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

我有一个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=4to find out if it被检查。
通过阅读MySQL引用，我了解到需要使用JSON_EXTRACT函数来获取数据。
在下面的一些示例中，我按如下方式测试了查询，以查看是否可以返回trackStatus：

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

这样可以工作并返回trackStatus。但是，当我尝试在trackStatus中展开此项以查询特定于isChecked的值时，它不会返回值（不是空值，而是空值）。

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

我试过无数种不同的问题，直到我绕圈子的时候。
我意识到问题出在trackStatus上，因为如果删除该数组，我可以查询nextStatusIndex并且它可以工作。但是如果数组在那里，它就不会。
因此，我希望有人能告诉我如何/如果我可以使用MySQL查询（在trackStatus和nextStatusIndex中）来查询这个JSON，给出数据的格式。

最佳答案

在MySQL 8.0中，可以将内部JSON数组转换为function JSON_TABLE()的记录集，然后对其进行检查。
下面是5.7版的解决方案，它依赖于JSON_SEARCH() and JSON_EXTRACT()。
此查询将为isChecked数组中属性trackStatus设置为value的（第一个）元素提供attribute4的值：

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

如果样本数据位于this DB fiddle中，则返回：

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

细节
您可以使用JSON_SEARCH查找数组trackStatus中属性设置为value的（第一个）元素的路径：

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

对于测试数据，这通常会返回：4。
我们可以操纵路径字符串，将其指向同一数组元素的属性"$.trackStatus[1].value"。周围的双引号也需要删除：

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

返回：isChecked。
最后，这个表达式可以作为$.trackStatus[1].isChecked的参数。