如何在MariaDB 10.6.12中提取嵌套JSON的动态键？

输入JSON #

{
  "data": {
    "Header": {
      "num": 1000095371,
      "name": "1000095371 LE"
    },
    "character": {
        "b1234": {
          "ID": 1
        },
        "b1256": {
          "ID": 2
        },
        "b12389": {
          "ID": 3
        }
      }
  },
  "id": 123456
}

期望输出 #

+--------+------------+---------------+--------------+-------------+
| id     | num        | name          | characterkey | characterid |
+--------+------------+---------------+--------------+-------------+
| 123456 | 1000095371 | 1000095371 LE | b1234        | 1           |
+--------+------------+---------------+--------------+-------------+
| 123456 | 1000095371 | 1000095371 LE | b1256        | 2           |
+--------+------------+---------------+--------------+-------------+
| 123456 | 1000095371 | 1000095371 LE | b12389       | 3           |
+--------+------------+---------------+--------------+-------------+

需求说明：character节点下的键（characterkey）和数量不固定，需提取所有characterkey及其对应ID，同时关联其他固定字段，使用MariaDB 10.6.12版本。

解决方案 #

假设存储JSON的表名为your_table，JSON字段名为json_data，可通过以下SQL语句实现需求：

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.id')) AS id,
  JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.data.Header.num')) AS num,
  JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.data.Header.name')) AS name,
  jt.characterkey,
  JSON_UNQUOTE(JSON_EXTRACT(json_data, CONCAT('$.data.character.', jt.characterkey, '.ID'))) AS characterid
FROM
  your_table,
  JSON_TABLE(
    JSON_KEYS(json_data, '$.data.character'),
    '$[*]' COLUMNS(characterkey VARCHAR(255) PATH '$')
  ) AS jt;

关键逻辑说明 #

1. JSON_KEYS(json_data, '$.data.character')：获取character节点下的所有键，返回JSON数组格式的键列表。
2. JSON_TABLE(...)：将JSON数组转换为行集，每个键对应一行，生成characterkey字段。
3. CONCAT('$.data.character.', jt.characterkey, '.ID')：动态拼接JSON路径，根据当前行的characterkey提取对应的ID值。
4. JSON_UNQUOTE：去除JSON字符串的引号，得到纯文本格式的字段值。

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