mysql - MySQL 8：使用 JSON_TABLE 解压缩任意 JSON 键值对

Question

我有一个 json 有效负载，其中包含我想要提取并插入到表中的任意键-> 值对。

我不知道高级键名，因此需要使用 MySQL 查询从 JSON 中动态提取它们。

任何帮助将不胜感激，但请：-

• 不要建议通过将属性重构为具有一致键的对象来重新设计架构
  • EG{"myatt1": "value1"}成[{ name: "myatt1", "value": "value1 }]
• 不建议稍后在演示文稿中这样做；javascript，python，java等。
  • 我想知道如何使用 MySQL 8 的 JSON 函数来做到这一点。

架构 (MySQL v8.0)

DROP TABLE IF EXISTS `objects`;

CREATE TABLE IF NOT EXISTS `objects` (
  `id` SERIAL,
  `payload` JSON NOT NULL
);

INSERT INTO `objects`
  (`payload`)
VALUES
  (
    '[
      {
          "name": "object #1",
          "attributes": {
              "myatt1": "value1",
              "myatt2": "value2"
          }
      },{
          "name": "object #2",
          "attributes": {
              "another thing": "another value",
              "random tag": "random value"
          }
      }
    ]'
  );

---

查询 #1

SELECT `j`.*
FROM
  `objects`,
  JSON_TABLE(
    `payload`,
    '$[*]' 
    COLUMNS(
      `objectid` FOR ORDINALITY,
      `objectname` VARCHAR(50) PATH '$.name',
      NESTED PATH '$."attributes".*' COLUMNS (
        `attribute_id` FOR ORDINALITY,
        `attribute_name` VARCHAR(50) PATH '$', /* Dont know how to do this */           
        `attribute_value` VARCHAR(50) PATH '$'
      )
    )
  ) `j`;

到目前为止我得到了什么（第 4 列是错误的）

| objectid | objectname | attribute_id | attribute_name | attribute_value |
| -------- | ---------- | ------------ | -------------- | --------------- |
| 1        | object #1  | 1            | value1         | value1          |
| 1        | object #1  | 2            | value2         | value2          |
| 2        | object #2  | 1            | random value   | random value    |
| 2        | object #2  | 2            | another value  | another value   |

要求的结果

| objectid | objectname | attribute_id | attribute_name | attribute_value |
| -------- | ---------- | ------------ | -------------- | --------------- |
| 1        | object #1  | 1            | myatt1         | value1          |
| 1        | object #1  | 2            | myatt2         | value2          |
| 2        | object #2  | 1            | random value   | random value    |
| 2        | object #2  | 2            | another thing  | another value   |

---

在 DB Fiddle 上查看

标签： mysqljson

Answer 1

一种选择是使用JSON_KEYS和JSON_EXTRACT：

SELECT
  `der`.`objectid`,
  `der`.`objectname`,
  `der`.`attribute_id`,
  `der`.`attribute_name`,
  `der`.`attribute_value`
FROM (
  SELECT
    `j`.`objectid`,
    `j`.`objectname`,
    `j`.`attribute_id`,
    `j`.`attribute_value`,
    JSON_UNQUOTE(
      JSON_EXTRACT(
        JSON_KEYS(
          `payload`,
          CONCAT(
            '$[', `j`.`objectid` - 1, '].attributes'
          )
        ),
        CONCAT(
          '$[', `j`.`attribute_id` - 1, ']'
        )
      )
    ) `attribute_name`
    FROM
      `objects`,
      JSON_TABLE(
        `payload`,
        '$[*]' 
        COLUMNS (
          `objectid` FOR ORDINALITY,
          `objectname` VARCHAR(50) PATH '$.name',
          NESTED PATH '$.attributes.*' COLUMNS (
            `attribute_id` FOR ORDINALITY,
            `attribute_value` VARCHAR(50) PATH '$'
          )
        )
      ) `j`
) `der`;

请参阅db-fiddle。