Transforming json data list field to columns with SQL

Question

Currently I have in my DB(mariaDB) a column that is called data and contains a json list:

ID   | data
1    | '["section1","section2","section3"]'
2    | '["section2","section4"]'

I would like to turn it into something like that:

id   | section1     | section2     | section3         | section4
1    | 1            | 1            | 1                | 0
2    | 0            | 1            | 0                | 1

Meaning that all possible values from all fields become columns and get the value 1 if that list item exists for the row accordingly and 0 if it does not.

Is there any way to transform this with a query?

Akina · Accepted Answer · 2020-09-15 08:25:30Z

3

SELECT 
    id, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section1')) section1, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section2')) section2, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section3')) section3, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section4')) section4 
from test;

If you need zeros instead of NULLs then wrap the expressions with COALESCE().

If separate data value cannot contain duplicated values (and this is provided by according constraint) or if you need not values amount but their presence only you may use

select 
    id, 
    JSON_CONTAINS(data, '"section1"') section1, 
    JSON_CONTAINS(data, '"section2"') section2, 
    JSON_CONTAINS(data, '"section3"') section3, 
    JSON_CONTAINS(data, '"section4"') section4 
from test;

fiddle

PS. If the list of possible sectionX values is indefinite or dynamic then you must use stored procedure with dynamic SQL.

edited Sep 15, 2020 at 8:25

answered Sep 15, 2020 at 8:18

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Comments

GMB · Accepted Answer · 2020-09-15 08:00:44Z

1

Consider:

select 
    id, 
    data ->> '$[0]' section1, 
    data ->> '$[1]' section2, 
    data ->> '$[2]' section3, 
    data ->> '$[3]' section4 
from mytable

Or using json_extract():

select 
    id, 
    json_unqote(json_extract(data, '$[0]')) section1, 
    json_unqote(json_extract(data, '$[1]')) section2, 
    json_unqote(json_extract(data, '$[2]')) section3, 
    json_unqote(json_extract(data, '$[3]')) section4 
from mytable

edited Sep 15, 2020 at 8:00

answered Sep 15, 2020 at 7:58

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Akina Over a year ago

Query 1 - MariaDB does not support -> and ->> operators; Query 2 - you pivot posessionally, not by the value. fiddle

Collectives™ on Stack Overflow

Transforming json data list field to columns with SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related