MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test ( id INT, jsondata JSON );

INSERT INTO test VALUES 
(1, '{"category": ["mobiles"], "specs": ["4g", "2gb ram"]}'), 
(2, '{"category": ["computer"], "specs": ["2gb ram"]}');

SELECT CAST(jsondata AS CHAR) FROM test;

Records: 2  Duplicates: 0  Warnings: 0




CAST(jsondata AS CHAR)


{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}

{"specs": ["2gb ram"], "category": ["computer"]}


-- extract keys
SELECT test.id,
       CAST(test.jsondata AS CHAR) jsondata,
       jsonkeystable.id,
       jsonkeystable.keyname
FROM test
CROSS JOIN JSON_TABLE (
  JSON_KEYS(test.jsondata),
  '$[*]' COLUMNS (
    id FOR ORDINALITY,
    keyname VARCHAR(64) PATH '$'
    )
  ) jsonkeystable


id
jsondata
id
keyname


1
{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}
1
specs

1
{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}
2
category

2
{"specs": ["2gb ram"], "category": ["computer"]}
1
specs

2
{"specs": ["2gb ram"], "category": ["computer"]}
2
category


-- extract keys then according data
SELECT test.id,
       CAST(test.jsondata AS CHAR) jsondata,
       jsonkeystable.keyid,
       jsonkeystable.keyname,
       jsonvaluestable.valueid,
       jsonvaluestable.value
FROM test
CROSS JOIN JSON_TABLE(
  JSON_KEYS(test.jsondata),
  '$[*]' COLUMNS (
    keyid FOR ORDINALITY,
    keyname VARCHAR(64) PATH '$'
    )
  ) jsonkeystable
CROSS JOIN JSON_TABLE(
  JSON_EXTRACT(test.jsondata, CONCAT('$.', jsonkeystable.keyname)),
  '$[*]' COLUMNS (
    valueid FOR ORDINALITY,
    value VARCHAR(64) PATH '$'
    )
  ) jsonvaluestable
ORDER BY test.id, jsonkeystable.keyid, jsonvaluestable.valueid


id
jsondata
keyid
keyname
valueid
value


1
{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}
1
specs
1
4g

1
{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}
1
specs
2
2gb ram

1
{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}
2
category
1
mobiles

2
{"specs": ["2gb ram"], "category": ["computer"]}
1
specs
1
2gb ram

2
{"specs": ["2gb ram"], "category": ["computer"]}
2
category
1
computer


-- extract keys then according data, select only needed columns
SELECT jsonkeystable.keyname,
       jsonvaluestable.value
FROM test
CROSS JOIN JSON_TABLE(
  JSON_KEYS(test.jsondata),
  '$[*]' COLUMNS (
    keyid FOR ORDINALITY,
    keyname VARCHAR(64) PATH '$'
    )
  ) jsonkeystable
CROSS JOIN JSON_TABLE(
  JSON_EXTRACT(test.jsondata, CONCAT('$.', jsonkeystable.keyname)),
  '$[*]' COLUMNS (
    valueid FOR ORDINALITY,
    value VARCHAR(64) PATH '$'
    )
  ) jsonvaluestable
ORDER BY test.id, jsonkeystable.keyid, jsonvaluestable.valueid


keyname
value


specs
4g

specs
2gb ram

category
mobiles

specs
2gb ram

category
computer

id	jsondata	id	keyname
1	{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}	1	specs
1	{"specs": ["4g", "2gb ram"], "category": ["mobiles"]}	2	category
2	{"specs": ["2gb ram"], "category": ["computer"]}	1	specs
2	{"specs": ["2gb ram"], "category": ["computer"]}	2	category

keyname	value
specs	4g
specs	2gb ram
category	mobiles
specs	2gb ram
category	computer