mysql - 如何从 json 数组列中获取具有特定键值的 json 对象？

Question

在我的 MySQL 8.0 表中，我有一个 JSON ARRAY 列。它是一个 JSON 对象数组。我想根据对象中的键值对从每一行的数组中选择一个对象。

示例行:

[{bool:false, number:0, value:'hello'}, 
{bool:true, number:1, value:'world'},
{bool:true, number:2, value:'foo'},
{bool:false, number:1, value:'bar'}]

我想做的是获取“值”，其中 bool=true，AND number=1。所以我想要一个在此示例中返回“world”的查询。

如果我可以获得 bool=true 和 number=1 的对象的索引，那么在本例中它会返回“$[1]”。

我正在尝试对整个列运行查询，将新列设置为查询返回的值。 MySQL JSON 函数可以实现吗？我查看了引用资料，但没有像我的示例那样在数组中包含对象。

编辑:如果我这样做

SELECT JSON_SEARCH(column->"$[*]", 'all', '1');
SELECT JSON_SEARCH(names->"$[*]", 'all', 'true');

我分别得到 number=1 和 bool=true 的对象的路径/索引。我想要这两个结果的重叠。

Answer 1

您可以使用 JSON_TABLE将 JSON 转换为派生表，然后您可以从中提取值:

SELECT j.value
FROM test t
JOIN JSON_TABLE(t.jsonStr, 
        '$[*]'
        COLUMNS(bool BOOLEAN PATH '$.bool',
                number INT PATH '$.number',
                value VARCHAR(20) PATH '$.value')) j                    
WHERE j.bool = true AND j.number = 1

输出:

value
world

如果您还想要获取匹配的 value 的每个 JSON 值中的索引，您可以添加一个 FOR ORDINALITY 子句你的 JSON_TABLE 例如:

SELECT j.idx, j.value
FROM test t
JOIN JSON_TABLE(t.jsonStr, 
        '$[*]'
        COLUMNS(idx FOR ORDINALITY,
                bool BOOLEAN PATH '$.bool',
                number INT PATH '$.number',
                value VARCHAR(20) PATH '$.value')) j
WHERE j.bool = true AND j.number = 1

输出:

idx     value
2       world

Demo on dbfiddle