Extract JSON attributes as columns in MySQL

So you have been lured into storing attribute data as JSON in your MySQL to avoid defining a proper schema from the beginning. Well, now for some report you need a certain attribute as column. Here is how to solve this task.

We have following simplified schema:

CREATE TABLE `store` (
  `storeId` int(11) NOT NULL,
  `attributes` json DEFAULT NULL
);

With data as in following example:

   storeId: 4644
attributes: [
  {
    "value": "",
    "attributeId": "representedBy"
  },
  {
    "value": "abc_de",
    "locale": "de_DE",
    "attributeId": "terms"
  },
  {
    "value": false,
    "attributeId": "hidden"
  }
]

We may want to extract the value of attribute representedBy as column in our report. So to extract we use following trick

select storeId,
cast(
  json_unquote(
    json_extract(attributes, 
      json_unquote(
        replace(
          json_search(attributes, 'one', 'representedBy'),
          'attributeId', 'value'
        )
      )
    )
) as char) as representedBy
from store

How does it work:

• json_search(attributes, 'one', 'representedBy') searches the first occurrence of „representedBy“ and returns the path to the key where it is stored
• using replace we modify the path to point to the value instead of the attributeId
• after json_unquote we can use that path in json_extract. The return value is again unquoted and casted to char to have a proper string in the report

Why do we have to use that trick?

We can’t extract the attribute value by using the function json_extract directly because

• JSON paths which are used in json_extract for selection don’t provide selection based on keyValues – you can only select by keyName
• json_extract doesn’t separate selection from evaluation – what is selected is evaluated.
• Feature request: with both features added we could extract using json_extract(attributes, '$[*].attributeId="representedBy"', '$.value')