Create json table from nested json object

Question

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

I only want the output from first element of the array (that is itself an array) — mayank agrawal
– mayank agrawal, Commented Jul 26, 2021 at 7:16

Akina · Accepted Answer · 2021-07-26 07:21:02Z

2

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

answered Jul 26, 2021 at 7:21

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mayank agrawal Over a year ago

Is it also possible to obtain the table without using json_table ?

Akina Over a year ago

@mayankagrawal Recursive CTE and string and/or JSON functions may solve, of course...

Collectives™ on Stack Overflow

Create json table from nested json object

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related