Transforming json data list field to columns with SQL

user_3216366 0
Viewed 71

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?

mariadb mysql arrays sql json
2 Answers
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.

user_101387340

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
user_106767160
Related