英文:
How to query MySQL JSON column data for null values?
问题
我在MySQL数据库的JSON类型列中存储JSON数据。尝试过滤extra_data列，其中payment_address不为null。不幸的是找不到正确的方法。
我尝试过的查询如下：
SELECT * FROM orders WHERE extra_data->"$.payment_address" != NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" IS NOT NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" != "null";
对于extra_data列，JSON数据如下：
| extra_data |
|---|
| {"tracking_number": "", "payment_amount": null, "payment_address": null} |
| {"tracking_number": "", "payment_amount": null, "payment_address": "testaddress"} |
如何查询JSON列中的某些字段是否为null/非null值？
英文:
I'm storing JSON data in JSON type column in MySQL database. Tried to filter extra_data column for where payment_address is not null. Unfortunately couldn't find the right way.
These are the queries I tried:
SELECT * FROM orders WHERE extra_data->"$.payment_address" != NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" IS NOT NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" != "null";
For the extra_data column, JSON data is like this:
| extra_data |
|---|
| {"tracking_number": "", "payment_amount": null, "payment_address": null} |
| {"tracking_number": "", "payment_amount": null, "payment_address": "testaddress"} |
How can I query some field in JSON column for null/not null values?
答案1
得分: 2
这可能与 bug 85755 有关，与 JSON 的 null 和 SQL 的 NULL 之间的差异有关。无论如何，您可以通过使用 ->> 或 JSON_UNQUOTE 来解决这个问题：
SELECT *
FROM orders
WHERE extra_data->>"$.payment_address" != 'null';
SELECT *
FROM orders
WHERE JSON_UNQUOTE(extra_data->"$.payment_address") != 'null';
在这两种情况下的输出是：
| extra_data |
|---|
| {"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""} |
英文:
This is likely related to bug 85755 which relates to the difference between JSON null and SQL NULL. Anyway, you can workaround this by using ->> or JSON_UNQUOTE instead:
SELECT *
FROM orders
WHERE extra_data->>"$.payment_address" != 'null';
SELECT *
FROM orders
WHERE JSON_UNQUOTE(extra_data->"$.payment_address") != 'null';
In both cases the output is:
| extra_data |
|---|
| {"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""} |
答案2
得分: 1
You could use REPLACE to replace 'null' value into empty value, then get only not empty values:
使用 REPLACE 将 'null' 值替换为空值，然后仅获取非空值：
SELECT extra_data
FROM orders
WHERE REPLACE(JSON_EXTRACT(extra_data, '$.payment_address'), 'null', '') <> '';
Result:
结果：
{"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""}
英文:
You could use REPLACE to replace 'null' value into empty value, then get only not empty values :
SELECT extra_data
FROM orders
where REPLACE(JSON_EXTRACT(extra_data, '$.payment_address'), 'null', '') <> '' ;
Result :
{"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论