JSON 包含空数组的 MySQL [英] MySQL where JSON contains empty array

问题描述

如何在 JSON 列中搜索空数组?

How can I search a JSON column for an empty array?

假设我有一个 config 列，有时它的 JSON 等于 {"tier": []}.

Let's say I have a config column that sometimes has JSON equal to {"tier": []}.

WHERE JSON_CONTAINS(config, '', '$.tier') = 1

或

WHERE JSON_CONTAINS_PATH(config, 'one', '$.tier') = NULL

没有返回任何结果.有什么我遗漏的吗?

Aren't returning any results. Is there something I'm missing?

我专门四处寻找答案，但在 SO 或 MySQL 文档上找不到任何内容.

I've looked around for answers to this specifically, but I couldn't find anything on SO or the MySQL docs.

推荐答案

这里有两种方法，在 MySQL 5.7.24 上测试:

Here are two ways to do it, testing on MySQL 5.7.24:

mysql 5.7.24> select config from mytable 
  where json_contains(config, cast('[]' as json), '$.tier');
+--------------+
| config       |
+--------------+
| {"tier": []} |
+--------------+

mysql 5.7.24> select config from mytable 
  where json_contains_path(config, 'one', '$.tier');
+--------------+
| config       |
+--------------+
| {"tier": []} |
+--------------+

<小时>

我找到了另一个解决方案，它有助于严格检查空数组:

---

I found another solution, which helps to check strictly for an empty array:

首先，看到我有两行，其中一行有一个非空数组:

First, see that I have two rows, and one has a non-empty array:

mysql 5.7.24> select config from mytable 
  where json_contains(config, json_array(), '$.tier');
+----------------------------------------+
| config                                 |
+----------------------------------------+
| {"tier": []}                           |
| {"tier": [{"name": "BK", "value": 8}]} |
+----------------------------------------+
2 rows in set (0.00 sec)

现在我确保数组的长度为 0 作为确认它为空的一种方式:

Now I make sure that the length of the array is 0 as a way of confirming that it is empty:

mysql 5.7.24> select config from mytable 
  where json_contains(config, json_array(), '$.tier') 
  and json_length(config, '$.tier') = 0; 
+--------------+
| config       |
+--------------+
| {"tier": []} |
+--------------+
1 row in set (0.00 sec)

这篇关于JSON 包含空数组的 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！