如何查询包含对象数组的MySQL JSON列

Question

如何查询包含对象数组的MySQL JSON列

3

我有一个MySQL（v5.7）数据库，其中有一张名为deals的表格，它包含一个名为status的JSON列。

Status以以下格式（对格式不正确的部分表示歉意）包含数据（在deals id = 29中）：

{
    "trackStatus":
    [
        {
            "date":[],
            "notes":[],
            "value":"0",
            "isChecked":false
        },
        {
            "date":["2019-03-25T17:42:45-04:00"],
            "notes":[],
            "value":4,
            "isChecked":true
         }
    ],
    "nextStatusIndex":0, 
    "currentDealStatus":4
}

我试图查询trackStatus数组中value= 4的数据来判断是否被选中。

通过阅读MySQL参考文档，我理解需要使用JSON_EXTRACT函数来获取数据。

根据一些示例，我尝试了以下查询以查看是否可以返回trackStatus：

SELECT JSON_EXTRACT(status, '$.trackStatus') 
FROM deals 
WHERE deals.id = 29

这个功能是有效的并返回 trackStatus。但是，当我试图在此基础上查询 trackStatus 中特定的 isChecked 时，它不会返回值（不是 null 而是空白）。

SELECT JSON_EXTRACT(status, '$.trackStatus', '$.isChecked') 
FROM deals 
WHERE deals.id = 29 AND JSON_EXTRACT(status, '$.trackStatus', '$.value') = 4

我已经尝试了无数种不同的查询方法，结果却转了个圈。

我意识到问题出在 trackStatus 上，因为如果我删除该数组，就可以查询 nextStatusIndex 且可以正常工作。但是加上这个数组后，却不能正常工作。

所以我希望有人能够向我展示如何/是否能够使用 MySQL 查询来查询此 JSON（包括 trackStatus 和 nextStatusIndex），并给定数据格式的方式。

- user558720

我知道你想做什么，但是为什么要这样做呢？使用脚本语言提取所需的JSON数据不是更容易吗？这就像只用电锯来搭建房屋一样，有更简单、更高效的工具可以使用。 - Zak

当然我可以那样做，而且我正在这么做。但是我认为，既然有了新的JSON列，肯定有一些查询这些数据的方法，因为有了所有新的JSON函数。 - user558720

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GMB · Accepted Answer

在MySQL 8.0中，可以使用function JSON_TABLE()将内部JSON数组转换为记录集，然后进行检查。

以下是适用于版本5.7的解决方案，它依赖于JSON_SEARCH()和JSON_EXTRACT()。

此查询将为您提供属性isChecked的值，该值设置为4的trackStatus数组中具有属性value的（第一个）元素：

SELECT JSON_EXTRACT(
    status, 
    REPLACE(
        REPLACE(
            JSON_SEARCH(status, 'all', '4', '', '$.trackStatus[*].value'), 
        '"', ''), 
    '.value', '.isChecked')
) isCheckedAtValue4
FROM deals WHERE deals.id = 29;

使用您在此DB fiddle中提供的示例数据，将返回以下结果：

| isCheckedAtValue4 |
| ----------------- |
| true              |

详情

您可以使用JSON_SEARCH来查找数组trackStatus中第一个具有属性value设置为4的元素的路径：

SELECT JSON_SEARCH(status, 'one', '4', '', '$.trackStatus[*].value')
FROM deals 
WHERE deals.id = 29;

使用您的测试数据，这通常会返回："$.trackStatus[1].value"。

我们可以操作路径字符串，将其指向同一数组元素的属性isChecked。还需要删除周围的双引号：

SELECT REPLACE(
    REPLACE(
        JSON_SEARCH(status, 'one', '4', '', '$.trackStatus[*].value'), 
    '"', ''), 
'.value', '.isChecked'),
FROM deals WHERE deals.id = 29;

这将返回：$.trackStatus[1].isChecked。

最后，此表达式可以作为参数传递给JSON_EXTRACT()。