[MySQL] JSON_TABLE - JSON 데이터를 테이블로 변환

개요

2024.09.23-[MySQL] JSON 다루기에서 MySQL에서 JSON 형식의 데이터를 추가하고 조회하는 방법을 알아보았다.

이번 글에서는 JSON 데이터를 테이블 형태로 변환하는 함수인 JSON_TABLE 함수의 사용 방법을 적어둔다.

 

 

JSON_TABLE

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

JSON_TABLE은 JSON 데이터를 표형식 데이터로 변환하는 함수로 MySQL 8.0에서 도입되었다. 매개변수는 다음과 같다.

- expr : 테이블로 변환할 JSON 데이터

- path : 테이블 행을 생성하는데 사용하는 JSON 경로

- column_list : 테이블 열을 생성하는데 사용하는 JSON 경로

 

예시와 함께 사용 방법을 적어둔다.

 

 

사용 예시

1. 기본적인 JSON 형식 데이터

SET @json_document = '
[
    { "name": "Wag", "type": "Dog", "weight": 20 },
    { "name": "Bark", "type": "Dog", "weight": 10 },
    { "name": "Meow", "type": "Cat", "weight": 7 }
]
';

SELECT * FROM JSON_TABLE(@json_document, '$[*]'
    COLUMNS (
    name VARCHAR(255) PATH '$.name', 
    type VARCHAR(50) PATH '$.type',
    weight INT PATH '$.weight' 
    )
) AS jt;

COLUMNS 뒤에는 컬럼의 이름과 데이터 타입, 그리고 JSON 경로를 지정한다.

 

2. Array 데이터

SET @json_document = '["Wag", "Bark", "Meow"]';

SELECT *
  FROM JSON_TABLE(@json_document, '$[*]'
  	COLUMNS (
  		name VARCHAR(255) PATH '$'
  	)
 ) AS at;

 

3. 빈 경로가 있는 경우 - ON EMPTY

SET @json_document = '
[
    { "name": "Punch", "type": "Kangaroo", "weight": 200 },
    { "name": "Snap", "type": "Cat", "weight": 12 },
    { "name": "Ruff" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
    COLUMNS (
    name  VARCHAR(255) PATH '$.name', 
    type VARCHAR(50) PATH '$.type' DEFAULT '"N/A"' ON EMPTY,
    weight INT PATH '$.weight'
    )
) AS jt;

JSON 데이터 상에 키가 통일되어 있지 않다면 ON EMPTY 절을 사용하여 값이 없을 때 사용할 기본값을 지정할 수 있다.

 

 

 

 

참고 문서

https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html

https://database.guide/how-json_table-works-in-mysql/