[SQL] 복수개의 json 정보들을 풀어헤치기

0. 목표

 - 복수개의 json 정보들을 풀어헤치기

1. 실습

 1) 테이블 생성

CREATE TABLE sql_study.json_test
(
	 id int,
     json_field longtext 
);

 2) 데이터 삽입

  - id 가 1 인 것은 json 두개가 결합되어 있고, 3 은 세개가 결합되어 있음

INSERT INTO sql_study.json_test(id, json_field) VALUES(1, '[{"a" : "abc", "b" : "bbc"}, {"a" : "abc2", "b" : "bbc2"}]');
INSERT INTO sql_study.json_test(id, json_field) VALUES(2, NULL);
INSERT INTO sql_study.json_test(id, json_field) VALUES(3, '[{"a" : "abc", "b" : "bbc"}, {"a" : "abc2", "b" : "bbc2"}, {"a" : "abc3", "b" : "bbc3"}]');

 3) 풀기

  - 처음 cte 구문 결과를 통해 1 ~ 10 까지의 숫자를 얻을 수 있음

  - 두번째 구문 결과를 통해 json 의 길이에 맞게 행이 곱해지는 결과를 얻을 수 있음

  - 세 번째 구문 결과를 통해 각 행의 숫자에 맞는 데이터를 추출

# 1부터 10까지 배열 만들기
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10
),

# 모든 데이터에 배열 곱해주기
a AS (
	SELECT 
		j.id,
		j.json_field,
        JSON_LENGTH(j.json_field) AS json_length,
		c.n

	FROM sql_study.json_test AS j, cte AS c
    
    WHERE 1 = 1
		# json 이 NULL 제외	
        AND j.json_field IS NOT NULL
        # 배열숫자 <= json 길이 
        AND JSON_LENGTH(j.json_field) >= c.n
), 

final AS (
	SELECT 
		id,
        n,
        JSON_EXTRACT(json_field, CONCAT('$[',n-1,'].a')) AS result
    
    FROM a
    
    ORDER BY 1, 2 ASC
)

SELECT * FROM final;

2. 참고

- what is mysql query to print 1 to 10?

what is mysql query to print 1 to 10?