Print key, value pairs from nested MYSQL json

Question

I have extracted a mysql json dictionary strucutre and I wish to get all the values associated with the keys alpha and beta; however I also wish to print the key too. The structure of the dictionary is:

results = 
{1:
    {"a": {"alpha": 1234,
         "beta": 2345},
     "b": {"alpha": 1234,
         "beta": 2345},
     "c": {"alpha": 1234,
         "beta": 2345},
    },
 2: 
    {"ab": {"alpha": 1234,
          "beta": 2345},
     "ac": {"alpha": 1234,
          "beta": 2345},
     "bc": {"alpha": 1234,
          "beta": 2345},
    },
 3:
    {"abc": {"alpha": 1234,
           "beta": 2345}
    }
 "random_key": "not_interested_in_this_value"
}

So far I have been had some succes extracting the data I wish using:

SELECT JSON_EXTRACT alpha, beta FROM results;

This gave me the alpha and beta columns; however, I ideally would like to assoicate each value with their key to get:

+-------+---------+---------+
| key   | alpha   | beta    |
+-------+---------+---------+
| a     | 1234.   | 2345.   |
| b     | 1234.   | 2345.   |
| c     | 1234.   | 2345.   |
| ab    | 1234.   | 2345.   |
| ac    | 1234.   | 2345.   |
| bc    | 1234.   | 2345.   |
| abc   | 1234.   | 2345.   |
+-------+---------+---------+

I am very new to mysql and any help is appreciated.

That's not the syntax for using `JSON_EXTRACT()`. How did that query actually work? — Barmar, Feb 19 '22 at 00:15
not sure what you mean by "I have extracted"; it is most helpful if you start with the data as it is actually stored in your database. it isn't clear if results is a table or a json string or what. — ysth, Feb 19 '22 at 00:15
I think what you're looking for is [`JSON_TABLE()`](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html). This will allow you to turn the keys into the values in a new column. — Barmar, Feb 19 '22 at 00:17

Bill Karwin · Answer 1 · 2022-02-19T17:03:06.670

First of all, what you posted is not valid JSON. You can use integers as values, but you can't use integers as keys in objects. Also you have a few spurious , symbols. I had to fix these mistakes before I could insert the data into a table to test.

I was able to solve this using MySQL 8.0's JSON_TABLE() function in the following way:

select
 j2.`key`,
 json_extract(results, concat('$."',j1.`key`,'"."',j2.`key`,'".alpha')) as alpha,
 json_extract(results, concat('$."',j1.`key`,'"."',j2.`key`,'".beta')) as beta
from mytable
cross join json_table(json_keys(results), '$[*]' columns (`key` int path '$')) as j1
cross join json_table(json_keys(json_extract(results, concat('$."',j1.`key`,'"'))), '$[*]' columns (`key` varchar(3) path '$')) as j2
where j2.`key` IS NOT NULL;

Output:

+------+-------+------+
| key  | alpha | beta |
+------+-------+------+
| a    | 1234  | 2345 |
| b    | 1234  | 2345 |
| c    | 1234  | 2345 |
| ab   | 1234  | 2345 |
| ac   | 1234  | 2345 |
| bc   | 1234  | 2345 |
| abc  | 1234  | 2345 |
+------+-------+------+

If you find this sort of query too difficult, I would encourage you to reconsider whether you want to store data in JSON.

If I were you, I'd store data in normal rows and columns, then the query would be a lot simpler and easier to write and maintain.

Print key, value pairs from nested MYSQL json

1 Answers1