mysql的JSON数组字段中如何通过一定条件选择JSON对象

kcwpcxri 于 2023-05-05 发布在 Mysql

关注(0)|答案(3)|浏览(138)

我有一个JSON字段的表，其中包含一个JSON对象数组。我需要根据某些条件选择对象。
创建并填充表格：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_list JSON
);

INSERT INTO test(json_list) VALUES
("{""list"": [{""type"": ""color"", ""value"": ""red""}, {""type"": ""shape"", ""value"": ""oval""}, {""type"": ""color"", ""value"": ""green""}]}"),
("{""list"": [{""type"": ""shape"", ""value"": ""rect""}, {""type"": ""color"", ""value"": ""olive""}]}"),
("{""list"": [{""type"": ""color"", ""value"": ""red""}]}")
;

现在我需要从所有行中选择所有type = color的对象。
我想看到这个输出：

id  extracted_value
1   {"type": "color", "value": "red"}
1   {"type": "color", "value": "green"}
2   {"type": "color", "value": "olive"}
3   {"type": "color", "value": "red"}

如果能得到这个也不错：

id  color
1   red
1   green
2   olive
3   red

我无法更改DB或JSON。
我用的是MySQL 5.7

当前解决方案

我的解决方案是用一些索引集交叉连接表，然后提取JSON数组的所有元素。
我不喜欢它，如果一个数组中可能的对象数很大，它需要有所有索引，直到最大一个。它使查询变慢，因为当到达数组末尾时，它不会停止计算JSON值。

SELECT 
    test.id,
    JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')),
    ind.ind
FROM
    test
CROSS JOIN
    (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind
WHERE
    JSON_LENGTH(json_list, "$.list") > ind.ind
    AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

通过改变JSON_EXTRACT路径很容易只得到值。但有更好的办法吗？

编辑

添加了json_list.list长度检查。在本例中，这将过滤掉67%的派生表行。

mysql

来源：https://stackoverflow.com/questions/62189175/how-to-select-json-object-from-json-array-field-of-mysql-by-some-condition

3条答案

按热度按时间

klr1opcd1#

SELECT JSON_EXTRACT(json_list, '$.list[*]')
FROM `test`
where JSON_CONTAINS(json_list, '{"type":"color"}', '$.list')

赞(0）回复(0）举报 2023-05-05

xzabzqsa2#

目前最好的解决方案是：

SELECT 
    test.id,
    JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')),
    ind.ind
FROM
    test
CROSS JOIN
    (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind
WHERE
    JSON_LENGTH(json_list, "$.list") > ind.ind
    AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

赞(0）回复(0）举报 2023-05-05

li9yvcax3#

它在这里为我工作多个过滤器使用

SELECT id, JSON_EXTRACT(floorplan, '$') FROM table_name WHERE JSON_CONTAINS(floorplan, '{"Bed":0}', '$') OR JSON_CONTAINS(floorplan, '{"Bed":3}', '$');

'[{"Bed": 2, "MinimumRent": 1895}, {"Bed": 3, "MinimumRent": 2325}]'),

赞(0）回复(0）举报 2023-05-05

我来回答

mysql的JSON数组字段中如何通过一定条件选择JSON对象

当前解决方案

编辑

3条答案

相关问题

热门标签

最新问答