如何将MySQL 5.7 JSON NULL转换为本地MySQL NULL?

dldeef67  于 2023-03-09  发布在  Mysql
关注(0)|答案(7)|浏览(158)

此代码:

SELECT JSON_EXTRACT('{"hello": null}', '$.hello')

返回null
但是,这不是MySQL原生的NULL

SELECT COALESCE(JSON_EXTRACT('{"hello": null}', '$.hello'), 'world')

也产生null
如何将此JSON null转换为MySQL原生NULL
我想我可以使用IF进行一些比较,但这似乎不是正确的方法...

fbcarpbf

fbcarpbf1#

不幸的是CAST('{}' AS JSON)在这种情况下不起作用,但NULLIF起作用:
1.完整方法:
SELECT NULLIF(JSON_UNQUOTE(JSON_EXTRACT('{"hello": null}', '$.hello')), 'null') IS NULL;
1.短路:
SELECT NULLIF(helloColumn ->> '$.hello', 'null') IS NULL IS NULL;

whlutmcx

whlutmcx2#

这个问题是关于MySQL 5.7的,但我想添加MySQL 8.0的解决方案。
在MySQL 8.0.21中,我们现在可以使用JSON_VALUE函数(SQL标准的一部分)来提取值,并可以选择将值转换为(MySQL)类型。

mysql> SET @doc = '{"a": null}';

mysql> SELECT JSON_VALUE(@doc, '$.a' RETURNING SIGNED);
+------------------------------------------+
| JSON_VALUE(@doc, '$.a' RETURNING SIGNED) |
+------------------------------------------+
|                                     NULL |
+------------------------------------------+

mysql> SET @doc = '{"a": 2}';

mysql> SELECT JSON_VALUE(@doc, '$.a' RETURNING SIGNED);
+------------------------------------------+
| JSON_VALUE(@doc, '$.a' RETURNING SIGNED) |
+------------------------------------------+
|                                        2 |
+------------------------------------------+
vd2z7a6w

vd2z7a6w3#

假设您有一个名为'mytable'的表,其中包含一个名为'data'的json列。

SELECT 
  CASE WHEN data->>'$.myfield' = 'null' THEN NULL ELSE data->>'$.myfield' END myfield
FROM mytable

或者创建一个虚拟字段,如下所示:

ALTER TABLE mytable ADD myfield VARCHAR(200) GENERATED ALWAYS AS (
  CASE WHEN data->>'$.myfield' = 'null' THEN NULL ELSE data->>'$.myfield' END
) VIRTUAL;
6jygbczu

6jygbczu4#

看起来official discussion正在进行中,但不太活跃。
还有一个操纵陪审团的例子

mysql> SELECT CAST('null' AS JSON), JSON_TYPE(CAST('null' AS JSON)) = 'NULL';
+----------------------+------------------------------------------+
| CAST('null' AS JSON) | JSON_TYPE(CAST('null' AS JSON)) = 'NULL' |
+----------------------+------------------------------------------+
| null                 |                                        1 |
+----------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.21-20-log |
+---------------+
1 row in set (0.00 sec)
c6ubokkw

c6ubokkw5#

SELECT COALESCE(IF(JSON_TYPE(JSON_EXTRACT('{"hello":null}', '$.hello')) = 'NULL', NULL, JSON_EXTRACT('{"hello":null}', '$.hello')), 'world');
mccptt67

mccptt676#

您可以通过运行以下命令创建自定义函数TO_NULL

-- To check if JSON value is null, if so return SQL null, and if not return original JSON value.
-- There are two nulls, SQL null and JSON null, they are not equal.
-- More information: https://stackoverflow.com/questions/37795654
DROP FUNCTION IF EXISTS TO_NULL;
CREATE FUNCTION TO_NULL(a JSON) RETURNS JSON
BEGIN
    RETURN IF(JSON_TYPE(a) = 'NULL', NULL, a);
END;

而不是像这样使用它:

SELECT TO_NULL(JSON_EXTRACT('null', '$')) IS NULL;
bksxznpy

bksxznpy7#

简介

假设您有一个表,其中包含JSON类型的列j,并且该列的嵌套属性可以为空。

{"hello": "world"}
{"hello": null}
...

在MySQL 5.7.13+中,选择hello值的最简单方法如下:

SELECT j->>'$.hello' FROM ...

但是,该查询的结果将具有nullstringLONGTEXT)值,而不是特殊的SQL NULL值:

world   -- that's a string
null    -- that's also a string
...

验证这一点的一种方法是运行如下代码:

SELECT COALESCE(j->>'$.hello', 'YouWillNeverSeeMe:(((')

看起来更奇怪的是,如果我们使用->而不是->>,我们会得到:

"world"   -- that's a string (note the quotation marks)
null      -- that's also a string (with no quotation marks)
...

溶液

那么,我们如何将JSON null值作为SQL NULL值呢?

MySQL 8.0.21及以上版本

在MySQL 8.0.21+中,我们可以使用JSON_VALUE函数并执行以下操作:

SELECT JSON_VALUE(j->'$.hello', '$') FROM ...

结果将是:

world   -- that's a string
[NULL]  -- that's NOT a string but the special SQL NULL value
...

注意,如果我们在这里使用->>而不是->,我们将获取:

[NULL]  -- that's NOT a string but the special SQL NULL value
[NULL]  -- that's NOT a string but the special SQL NULL value
...
较旧的MySQL(包括5.7)

我不知道有什么理想的解决方案。answer provided by sergolius看起来简单漂亮,但它有一个潜在的缺陷。假设Alice和Bob有了一个孩子,他们决定将其命名为Null(嘿,我不是在这里评判!)。假设Null最终出现在我们数据库的JSON列中。因此,不要获取:

Alice
[NULL]
Bob
Null
John
...

,我们最终可能会得到:

Alice
[NULL]
Bob
[NULL]   -- Oops! Where did Null go?
John
...

相关问题