Use MySQL 8 json_table to dynamically extract JSON keys and nested value-CodePudding

I've been attempting to use MySQL 8's JSON_TABLE to extract the root keys and then their nested values. The problem is the root keys are dynamic and the nested key/value pairs might not exist.

JSON:

{
  "Foo": {
    "A": 3
  },
  "Bar": {
    "A": 1,
    "B": 368
  },
  "Biz": {
    "C": 2,
    "D": 10
  }
}

In this JSON the root keys "Foo", "Bar", and "Biz" are dynamic and for each of their objects I want to extract the "A" key's value, which may or may not exist. For example, the above code would return this result set:

json_key	a_value
Foo	3
Bar	1
Biz	null

I've been something along these lines but no luck (just returns one row of nulls):

select * from json_table('{"Foo": {"A": 3}, "Bar": {"A": 1, "B": 368}, "Biz": {"C": 2}}', 
    '$' COLUMNS(
        json_key varchar(255) path '$.*',
        sub_value integer path '$.*.A'
    )
) as i;

In the worst case I can try to restructure the JSON but it's already in the database so I'm hoping to leverage MySQL's JSON capability. Any ideas?

CodePudding user response：

SELECT json_key,
       JSON_EXTRACT(@json_value, CONCAT('$.', json_key, '.A')) a_value
FROM JSON_TABLE(JSON_KEYS(@json_value),
                '$[*]' COLUMNS (json_key VARCHAR(255) PATH '$')) keystable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dd9c01e77d57206d587dd2d17340bc02