sql - MySQL - How can I list all key names of a JSON data from column

Question

I have a SQL table and that contains a JSON column. Now, I want to do that. How get the all key names from registered all rows (JSON column). An example;

ID  JSON
1   {"a": 1, "b": 2}
2   {"apple": 3, "banana": 5}
3   {"wood": 4, "food": 6}

I should have got a query that get all key names from this rows. So my result should be that;

JSON
--------
a
b
apple
banana
wood
food

Is that possible? Can I do this?

Answer 1

To answer your question, YES it is possible.

To answer your second question: How would I know if you can dot this? ??

Below is an example of how to do it:

First i created the sample table:

CREATE TABLE json2(id integer, js JSON);
INSERT INTO json2 VALUES
( 1,   '{"a": 1, "b": 2}'),
( 2,   '{"apple": 3, "banana": 5}'),
( 3,   '{"wood": 4, "food": 6}');

When applying some JSON functions:

SELECT 
   JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(js),CONCAT('$[',x.x,']'))) as `JSON`
FROM json2
CROSS JOIN (SELECT 0 as x
             UNION ALL
             SELECT 1
             ) x
;

The output is:

+ --------- +
| JSON      |
+ --------- +
| a         |
| b         |
| apple     |
| banana    |
| food      |
| wood      |
+ --------- +