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,
          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')