无法从JSON_EXTRACT检测到空值 [英] Can't detect null value from JSON_EXTRACT

问题描述

我有一个数据库，该数据库具有存储在JSON列中的数据数组.我需要找到在JSON数组中特定位置具有空值的所有值.使用 JSON_EXTRACT 似乎微不足道，我对null的比较都没有奏效，所有这些都声称该值为null.

I have a database that has an array of data stored in a JSON column. I need to find all values that have a null value at a particular position in the JSON array. While pulling out the data with JSON_EXTRACT seemed trivial, none of my comparisons to null have worked, all of them claiming the value is null.

以下是我应该告诉我的示例代码:

Here is the example code that should work as far as I can tell:

SELECT JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') , (JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') is null)
FROM ate.readings_columns_new;

结果表的前几行如下:

null                    |   0
"INTERNALTEMPERATURE"   |   0
"INPUT_VOLTAGE"         |   0
null                    |   0
null                    |   0
"AH1"                   |   0

我尝试了所有我能想到的比较，所有比较结果均为0:

I have tried every comparison I can think of, and they all result in a 0:

(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') is null)
(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') <=> null)
ISNULL(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'))
(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') <=> 'null')

是否有一些键可以比较从JSON_EXTRACT提取的空值?

Is there some key to comparing null values pulled from a JSON_EXTRACT?

推荐答案

SELECT 
  JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'), 
  (JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') = CAST('null' AS JSON))
FROM ate.readings_columns_new;

或

SELECT 
  JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'), 
  (JSON_TYPE(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]')) = 'NULL')
FROM ate.readings_columns_new;

请参见 JSON_TYPE .

这篇关于无法从JSON_EXTRACT检测到空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！