CODEWITHSUNDEEP
mysqlsqljson
mayank agrawal
mayank agrawal

Reputation: 2545

Create json table from nested json object

I have a json structure in a field json_s in table mytable as follows (this is example for one row, there will be many similar rows)

{
 "100": {
         "1": [[2,3],2] ,
         "4": [[10], 0]
        },
 "102": {
         "7":[[5,6],5]
        }
}

which I want to convert into a json_table for further use in different queries. The json table structure should be like this

field_a  |  field_b  | field_c  | 
 100     |    1      |   2      | 
 100     |    1      |   3      |
 100     |    4      |   10     |     
 102     |    7      |   5      | 
 102     |    7      |   6      |

I have tried many solutions from other questions like this but not successful so far. One major problem is that I have unknown keys whom I want to assign in json column and the structure is nested at two levels.

EDIT: The structure to field mapping will be like this

{ "field_a" : {
               "field_b" : [ ["field_c1", "field_c2",... ] , x]
               }}

meaning ignoring the x element

Upvotes: 0

Views: 884

Answers (1)

Akina
Akina

Reputation: 42844

SELECT jt1.field_a, 
       jt2.field_b,
       jt3.field_c
FROM t1
CROSS JOIN JSON_TABLE(JSON_KEYS(t1.data),
                      '$[*]' COLUMNS (field_a VARCHAR(255) PATH '$')) jt1
CROSS JOIN JSON_TABLE(JSON_KEYS(JSON_EXTRACT(t1.data, CONCAT('$."', jt1.field_a, '"'))),
                      '$[*]' COLUMNS (field_b VARCHAR(255) PATH '$')) jt2
CROSS JOIN JSON_TABLE(JSON_EXTRACT(t1.data, CONCAT('$."', jt1.field_a, '"."', jt2.field_b, '"')),
                      '$[0][*]' COLUMNS (field_c VARCHAR(255) PATH '$')) jt3

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

Upvotes: 2

Related Questions