MySQL 8: Unpack arbitrary JSON key-value pairings using JSON_TABLE

Question

I have a json payload that contains arbitrary key -> value pairings that I want to extract and insert into a table.

I don't know the key names in advanced, so they need to be extracted from the JSON dynamically with a MySQL query.

Any help would be appreciated but please:-

Don't suggest redesigning the schema by refactoring the attribute into object's with consistent keys
- E.G. {"myatt1": "value1"} into [{ name: "myatt1", "value": "value1 }]
Don't suggest doing this in presentation later; javascript, python, java etc.
- I want to know how to do this with MySQL 8's JSON functions.

Schema (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"
          }
      }
    ]'
  );

Query #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`;

What I've got so far (the 4th column is wrong)

| 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   |

Required Results

| 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   |

View on DB Fiddle

wchiquito · Accepted Answer · 2020-10-29 21:21:36Z

1

One option is to use JSON_KEYS and 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`;

See db-fiddle.

answered Oct 29, 2020 at 21:21

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soulrob Over a year ago

Thanks a lot @wchiquito, I've been banging my head against this wall all day. You mentioned that this is one option, can you think of other approaches?

Collectives™ on Stack Overflow

MySQL 8: Unpack arbitrary JSON key-value pairings using JSON_TABLE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related