如何在MySQL中从嵌套JSON字段提取指定字段值？

你之前的SQL依赖数组索引取值，一旦数组元素顺序变动，结果就会出错。正确的做法是根据key字段的具体值匹配对应的value，以下是不同数据库的实现方式：

MySQL/MariaDB #

方法1：用JSON_TABLE展开数组（推荐，可读性高） #

SELECT jt.value AS country
FROM test_table,
     JSON_TABLE(
         description,
         '$[*]' COLUMNS(
             key_name VARCHAR(50) PATH '$.key',
             value VARCHAR(100) PATH '$.value'
         )
     ) jt
WHERE jt.key_name = 'country_name';

方法2：用JSON_SEARCH+JSON_EXTRACT组合 #

SELECT JSON_UNQUOTE(
    JSON_EXTRACT(
        description,
        CONCAT('$[', JSON_SEARCH(description, 'one', 'country_name', NULL, '$[*].key'), '].value')
    )
) AS country
FROM test_table;

PostgreSQL #

如果字段是jsonb类型（推荐用jsonb）：

SELECT (elem->>'value') AS country
FROM test_table,
     jsonb_array_elements(description::jsonb) AS elem
WHERE elem->>'key' = 'country_name';

如果是json类型，把jsonb_array_elements换成json_array_elements即可。

SQLite #

SELECT json_extract(value, '$.value') AS country
FROM test_table,
     json_each(description)
WHERE json_extract(value, '$.key') = 'country_name';

内容的提问来源于stack exchange，提问作者Nilu Singh