时间戳

首页 > 解决方案 > 如何在 SQL 上的 JSON 数组中搜索

mysql - 如何在 SQL 上的 JSON 数组中搜索

问题描述

我正在使用 MySQL 5.6.43 并尝试在表中的 JSON 数组中进行搜索。我在 MySQL 数据库中的 JSON 是这样的：

[
    {
        "Name": "AAA",
        "CountryCode": "AFG",
        "District": "Kabol",
        "Population": 1780000
    },
    {
        "Name": "BBB",
        "CountryCode": "AFG",
        "District": "Qandahar",
        "Population": 237500
    },
    {
        "Name": "CCC",
        "CountryCode": "USD",
        "District": "Qandahar",
        "Population": 237500
    }
]

我想拿正义AFG CountryCode的。所以我的结果应该是这样的：

[
    {
        "Name": "AAA",
        "CountryCode": "AFG",
        "District": "Kabol",
        "Population": 1780000
    },
    {
        "Name": "BBB",
        "CountryCode": "AFG",
        "District": "Qandahar",
        "Population": 237500
    }
]

我怎样才能做到这一点？

标签： mysqlsqljsonmysql-5.6

解决方案

SELECT test.data, JSON_ARRAYAGG(JSON_OBJECT('Name',jsontable.Name,
                                            'CountryCode',jsontable.CountryCode,
                                            'District',jsontable.District,
                                            'Population',jsontable.Population)) filtered
FROM test,
     JSON_TABLE(test.data,
                '$[*]' COLUMNS (Name VARCHAR(255) PATH '$.Name',
                                CountryCode VARCHAR(255) PATH '$.CountryCode',
                                District VARCHAR(255) PATH '$.District',
                                Population VARCHAR(255) PATH '$.Population')) jsontable
WHERE jsontable.CountryCode = 'AFG'
GROUP BY test.data;

小提琴

在 MySQL 5.6 中，您必须使用字符串函数 - 此版本中尚未实现 JSON：

SELECT CONCAT('[', GROUP_CONCAT('{', SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(test.data, '}', nums.num), '}', -1), '{', -1), '}'), ']') filtered
FROM test
CROSS JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) nums
WHERE LOCATE('"CountryCode":"AFG"', SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(test.data, ' ', ''), '}', nums.num), '}', -1))
GROUP BY test.data

小提琴

如果源数组每个值可能包含超过 4 个对象，则展开nums子查询。

限制：要搜索的键和值都不能包含空格。

推荐阅读