Explode column containing list of json-strings in mysql

Question

I have a table in a mysql database which looks like this:

ID                                      CONFIG
0              276  {"pos":[{"type":"geo...
1              349  {"pos":[{"type":"geo...
2              378  {"pos":[{"type":"geo...
3              381  {"pos":[{"type":"geo...
4              385  {"pos":[{"type":"geo...

where the elements in the CONFIG column all have the form:

{"posit":[{"type":"geo_f","priority":1,"validity":0},{"type":"geo_m","priority":2,"validity":0},{"type":"geo_i","priority":3,"validity":0},{"type":"geo_c","priority":4,"validity":0}]}

Now, I know how to "explode" this table for the first level:

SELECT *,
CONFIG ->'$.posit' as Position
FROM mytable;

which return

ID                             CONFIG          Position
0              276  {"pos":[{"type":"geo...    [{"type":"geo...
1              349  {"pos":[{"type":"geo...    [{"type":"geo...
2              378  {"pos":[{"type":"geo...    [{"type":"geo...
3              381  {"pos":[{"type":"geo...    [{"type":"geo...
4              385  {"pos":[{"type":"geo...    [{"type":"geo...

where the elements of Position are lists of json-strings:

[{"type":"geo_f","priority":1,"validity":0},{"type":"geo_m","priority":2,"validity":0},{"type":"geo_i","priority":3,"validity":0},{"type":"geo_c","priority":4,"validity":0}]

But, I do not have a clue of how to take the next step to "explode" the elements of Position. The resulting table should have:

4 times the number of rows as the original table since there are four json-strings. I.e., 4 rows for each ID.
The addition of columns type, priority, validity.

I tried different way to extract this information:

SELECT *,
CONFIG ->'$.posit' as Position,
Position ->>'$.type' as Type
FROM mytable

but that evidently failed.

Any help would be greatly appreciated!

lemon · Accepted Answer · 2022-05-15 00:35:48Z

2

We have a JSON_TABLE function in mysql 8. Does it work for you?

SELECT jt.*
FROM z as mytable
CROSS JOIN JSON_TABLE(
    mytable.CONFIG -> '$.posit', 
                      '$[*]' COLUMNS (ID FOR ORDINALITY,
                                      type     VARCHAR(255) PATH '$.type',
                                      priority VARCHAR(255) PATH '$.priority',
                                      validity VARCHAR(255) PATH '$.validity')
) jt

Or in case you need only for row but as columns it is much more simple

SELECT mytable.CONFIG -> '$.posit[0].type' as type_0,
       mytable.CONFIG -> '$.posit[1].type' as type_1,
       mytable.CONFIG -> '$.posit[2].type' as type_2,
       mytable.CONFIG -> '$.posit[3].type' as type_3
FROM z as mytable

PS: As mentioned in comment you wanted ID from mytable:

SELECT mytable.ID, 
       jt.*
FROM       z AS mytable
CROSS JOIN JSON_TABLE(mytable.CONFIG -> '$.posit',
                                        '$[*]' COLUMNS (type     VARCHAR(255) PATH '$.type',
                                                        priority VARCHAR(255) PATH '$.priority',
                                                        validity VARCHAR(255) PATH '$.validity')
) jt;

edited May 15, 2022 at 0:35

lemon

15.6k6 gold badges23 silver badges42 bronze badges

answered May 15, 2021 at 18:37

Andrew

1311 silver badge8 bronze badges

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

2 Comments

Akina Over a year ago

Wrong - applicable to scalar or single-row value only. Select id from source table and rename parsed ORDINALITY column, this allows to parse a rowset.

Serge de Gosson de Varennes Over a year ago

Great! The last piece of code worked perfectly! Thank you!

Collectives™ on Stack Overflow

Explode column containing list of json-strings in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related