时间戳

首页 > 解决方案 > 在 elasticsearch 中使用 /_SQL API 处理“null_value”属性

sql - 在 elasticsearch 中使用 /_SQL API 处理“null_value”属性

问题描述

我有一个使用 typescript 和 .net（服务器端）开发的 Web 应用程序，它类似于 BI 工具。我们使用 Elasticsearch 作为数据连接器，我可以在其中根据我在 elasticsearch 中的记录创建仪表板。

我有一个带有下面架构的示例表

"mappings": {
"_doc": {
"properties": {
"status_code": {
"type": "keyword",
"null_value": "NULL"
}
}

当我向下钻取值时，由于空行为，它显示为没有记录。当我的数据为空值时，请查找查询格式

视频：https ://easyupload.io/4ubcie

SELECT
"employeeName" AS "column4",
SUM( "id" ) AS "column5"
FROM "test_emp5" AS "33f25b56fc4d71a82831f4dce21a24"
WHERE ( ( (CAST("shift" AS VARCHAR) IN( 'AM' ))))
GROUP BY "employeeName"
ORDER BY "column4" ASC LIMIT 101



SELECT
"employeeName" AS "column4",
SUM( "id" ) AS "column5"
FROM "test_emp5" AS "33f25b56fc4d71a82831f4dce21a24"
WHERE ( ( (CAST("shift" AS VARCHAR) IN( '' ))))
GROUP BY "employeeName"
ORDER BY "column4" ASC LIMIT 101

如果您在数据为空时看到上面的查询，则它的形式类似于上面的第二个查询。我知道在添加“null_value”属性后，这不应该像 null 一样，我可以索引它。有任何想法吗？？

我们使用/_sql API 来形成这个查询。

样本数据：

{"took":2,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":4,"relation":"eq"},"max_score":1.0,"hits":[{"_index":"test_emp3","_type":"_doc","_id":"QrTxgnsBhHo46Lwj58A-","_score":1.0,"_source":{"employeeName":"John_doe","shift":"AM","id":2116}},{"_index":"test_emp3","_type":"_doc","_id":"Q7TxgnsBhHo46Lwj58A-","_score":1.0,"_source":{"employeeName":"James_lebron","shift":"AM","id":2117}},{"_index":"test_emp3","_type":"_doc","_id":"RLTxgnsBhHo46Lwj58A-","_score":1.0,"_source":{"employeeName":"Mick_tyson","shift":"PM","id":2118}},{"_index":"test_emp3","_type":"_doc","_id":"1\n","_score":1.0,"_source":{
    "id":2991,
    "employeeName":"Kane",
    "shift":"AM"
}}]}}

标签： sqlelasticsearch

解决方案

推荐阅读