programing

SQL을 사용하여 json 데이터 목록 필드를 열로 변환

bestcode 2023. 1. 1. 11:13
반응형

SQL을 사용하여 json 데이터 목록 필드를 열로 변환

현재 DB(mariaDB)에 데이터라는 json 목록이 포함된 열이 있습니다.

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

나는 그것을 다음과 같은 것으로 바꾸고 싶다.

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

즉, 모든 필드에서 가능한 모든 값이 열이 되어 값을 가져옵니다.1해당 행에 해당 목록 항목이 존재하는지 여부 및0그렇지 않으면.

쿼리를 사용하여 이를 변환하는 방법이 있습니까?

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;

NULL 대신 0이 필요한 경우 식을 COALESCE()로 랩합니다.

분리된 경우data값은 중복된 값을 포함할 수 없습니다(그리고 이는 제약조건에 따라 제공됨). 또는 값이 필요하지 않고 사용자만 사용할 수 있습니다.

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

만지작거리다

PS. 가능한 경우sectionX값이 무한 또는 동적인 경우 동적 SQL과 함께 저장 프로시저를 사용해야 합니다.

고려사항:

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

또는 사용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

언급URL : https://stackoverflow.com/questions/63897414/transforming-json-data-list-field-to-columns-with-sql

반응형