14.17.6 JSON Table Functions

14.17.6 JSON 테이블 함수

이 섹션에는 JSON 데이터를 테이블 형식 데이터로 변환하는 JSON 함수에 대한 정보가 포함되어 있습니다. MySQL 9.5는 이러한 함수 중 하나인 JSON_TABLE()을 지원합니다.

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

JSON 문서에서 데이터를 추출하여 지정된 컬럼을 가지는 관계형 테이블로 반환합니다. 이 함수의 전체 문법은 다음과 같습니다:

1JSON_TABLE(
2    expr,
3    path COLUMNS (column_list)
4)   [AS] alias
5
6column_list:
7    column[, column][, ...]
8
9column:
10    name FOR ORDINALITY
11    |  name type PATH string path [on_empty] [on_error]
12    |  name type EXISTS PATH string path
13    |  NESTED [PATH] path COLUMNS (column_list)
14
15on_empty:
16    {NULL | DEFAULT json_string | ERROR} ON EMPTY
17
18on_error:
19    {NULL | DEFAULT json_string | ERROR} ON ERROR

expr: JSON 데이터를 반환하는 표현식입니다. 이는 상수('{"a":1}')일 수도 있고, 컬럼(먼저 FROM 절에 지정된 테이블 t1이 있다고 할 때 t1.json_data)일 수도 있으며, 함수 호출(JSON_EXTRACT(t1.json_data,'$.post.comments'))일 수도 있습니다.

path: 데이터 소스에 적용되는 JSON 경로 표현식입니다. path와 일치하는 JSON 값을 _row source_라고 부르며, 이는 관계형 데이터의 행을 생성하는 데 사용됩니다. COLUMNS 절은 row source를 평가하고, 그 안에서 특정 JSON 값을 찾아서, 이 JSON 값들을 관계형 데이터의 한 행 내 개별 컬럼의 SQL 값으로 반환합니다.

_alias_는 필수입니다. 테이블 별칭에 대한 일반적인 규칙이 적용됩니다( Section 11.2, “Schema Object Names” 참조).

이 함수는 컬럼 이름을 대소문자를 구분하지 않고 비교합니다.

JSON_TABLE()은 다음 목록에 설명된 네 가지 유형의 컬럼을 지원합니다:

1. name FOR ORDINALITY: 이 유형은 COLUMNS 절에서 행을 번호 매깁니다. _name_이라는 컬럼은 타입이 UNSIGNED INT인 카운터이며, 초기값은 1입니다. 이는 CREATE TABLE 문장에서 컬럼을 AUTO_INCREMENT로 지정하는 것과 동일하며, NESTED [PATH] 절에 의해 생성된 여러 행에 대해 동일한 값을 가지는 부모 행을 구분하는 데 사용할 수 있습니다.

2. name type PATH string_path [on_empty] [on_error]: 이 유형의 컬럼은 _string_path_로 지정된 값을 추출하는 데 사용됩니다. _type_은 MySQL 스칼라 데이터 타입입니다(즉, 오브젝트나 배열이 될 수는 없습니다). JSON_TABLE()은 데이터를 JSON으로 추출한 다음, MySQL에서 JSON 데이터에 적용되는 일반적인 자동 타입 변환을 사용하여 이를 컬럼 타입으로 강제 변환합니다. 값이 없으면 on_empty 절이 트리거됩니다. 오브젝트나 배열을 저장하려고 하면 선택적인 on error 절이 트리거됩니다; 이는 JSON으로 저장된 값에서 테이블 컬럼으로 강제 변환하는 동안 오류가 발생하는 경우(예: 문자열 'asd'를 정수 컬럼에 저장하려는 경우)에도 발생합니다.

3. name type EXISTS PATH path: 이 컬럼은 _path_로 지정된 위치에 어떤 데이터라도 존재하면 1을, 그렇지 않으면 0을 반환합니다. _type_은 유효한 MySQL 데이터 타입이면 무엇이든 될 수 있으나, 일반적으로는 INT의 어떤 형태로 지정하는 것이 좋습니다.

4. NESTED [PATH] path COLUMNS (column_list): 이는 JSON 데이터에서 중첩된 오브젝트나 배열을 부모 오브젝트 또는 배열의 JSON 값과 함께 단일 행으로 평탄화합니다. 여러 개의 PATH 옵션을 사용하면 여러 중첩 수준의 JSON 값을 단일 행으로 프로젝션할 수 있습니다.

_path_는 부모의 path, 즉 JSON_TABLE()의 부모 path 행 path 또는 (중첩된 path인 경우) 부모 NESTED [PATH] 절의 path를 기준으로 한 상대 path입니다.

_on empty_가 지정된 경우, 데이터가 (타입에 따라) 없는 경우에 JSON_TABLE()이 어떻게 동작할지 결정합니다. 이 절은 또한 NESTED PATH 절이 일치하는 항목을 찾지 못해 그에 대해 NULL로 보완된 행을 생성하는 컬럼에서 트리거됩니다. _on empty_는 다음 값 중 하나를 가집니다:

• NULL ON EMPTY: 컬럼을 NULL로 설정합니다. 이것이 기본 동작입니다.

• DEFAULT json_string ON EMPTY: 제공된 _json_string_을 유효한 한 JSON으로 파싱하여, 누락된 값 대신 저장합니다. 컬럼 타입 규칙은 기본값에도 적용됩니다.

• ERROR ON EMPTY: 에러를 발생시킵니다.

사용하는 경우, _on_error_는 다음 값들 중 하나를 가지며, 각각의 결과는 다음과 같습니다:

• NULL ON ERROR: 컬럼을 NULL로 설정합니다. 이것이 기본 동작입니다.

• DEFAULT json string ON ERROR: _json_string_을 (유효한 경우) JSON으로 파싱하여, 오브젝트 또는 배열 대신 저장합니다.

• ERROR ON ERROR: 에러를 발생시킵니다.

ON EMPTY보다 먼저 ON ERROR를 지정하는 것은 비표준이며 MySQL에서 폐기 예정(deprecated)되었습니다; 이렇게 시도하면 서버가 경고를 발생시킵니다. 이 비표준 문법에 대한 지원은 향후 MySQL 버전에서 제거될 예정입니다.

예를 들어, DECIMAL(10,1) 컬럼에 3.14159를 저장하는 것처럼, 값이 컬럼에 저장되는 동안 잘리는 경우, 어떤 ON ERROR 옵션과는 관계없이 경고가 발생합니다. 하나의 문장(statement)에서 여러 값이 잘리는 경우, 경고는 한 번만 발생합니다.

이 함수에 전달된 표현식과 path가 JSON null로 평가되면, JSON_TABLE()은 SQL 표준에 따라 SQL NULL을 반환합니다. 예시는 다음과 같습니다:

1mysql> SELECT *
2    ->   FROM
3    ->     JSON_TABLE(
4    ->       '[ {"c1": null} ]',
5    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
6    ->     ) as jt;
7+------++
8| c1   |
9+------+
10| NULL |
11+------+
121 row in set (0.00 sec)

다음 쿼리는 ON EMPTY 및 ON ERROR 사용법을 보여줍니다. {"b":1}에 해당하는 행은 path "$.a"에 대해 비어 있으며, [1,2]를 스칼라로 저장하려는 시도는 에러를 발생시킵니다; 이 행들은 출력에서 강조되어 있습니다.

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
5    ->     "$[*]"
6    ->     COLUMNS(
7    ->       rowid FOR ORDINALITY,
8    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
9    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
10    ->       bx INT EXISTS PATH "$.b"
11    ->     )
12    ->   ) AS tt;
13
14+-------+------+------------+------+
15| rowid | ac   | aj         | bx   |
16+-------+------+------------+------+
17|     1 | 3    | "3"        |    0 |
18|     2 | 2    | 2          |    0 |
19|     3 | 111  | {"x": 333} |    1 |
20|     4 | 0    | 0          |    0 |
21|     5 | 999  | [1, 2]     |    0 |
22+-------+------+------------+------+
235 rows in set (0.00 sec)

컬럼 이름은 테이블 컬럼 이름을 규정하는 일반적인 규칙 및 제한의 적용을 받습니다. Section 11.2, “Schema Object Names”를 참조하십시오.

모든 JSON 및 JSON 경로 표현식은 유효성 검사를 거칩니다; 두 유형 중 어느 하나라도 잘못된 표현식이면 에러가 발생합니다.

COLUMNS 키워드 앞에 오는 _path_에 대한 각 match는 결과 테이블에서 개별 행에 대응됩니다. 예를 들어, 다음 쿼리는 여기 보이는 결과를 생성합니다:

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
5    ->     "$[*]" COLUMNS(
6    ->       xval VARCHAR(100) PATH "$.x",
7    ->       yval VARCHAR(100) PATH "$.y"
8    ->     )
9    ->   ) AS  jt1;
10
11+------+------+
12| xval | yval |
13+------+------+
14| 2    | 8    |
15| 3    | 7    |
16| 4    | 6    |
17+------+------+

표현식 "$[*]"은 배열의 각 요소와 match됩니다. path를 수정하여 결과의 행을 필터링할 수 있습니다. 예를 들어, "$[1]"을 사용하면 소스로 사용된 JSON 배열의 두 번째 요소로 추출이 제한되며, 다음과 같습니다:

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
5    ->     "$[1]" COLUMNS(
6    ->       xval VARCHAR(100) PATH "$.x",
7    ->       yval VARCHAR(100) PATH "$.y"
8    ->     )
9    ->   ) AS  jt1;
10
11+------+------+
12| xval | yval |
13+------+------+
14| 3    | 7    |
15+------+------+

컬럼 정의 내에서 "$"는 match 전체를 컬럼에 전달합니다; "$.x" 및 "$.y"는 각각 match 내에서 키 x 및 y에 해당하는 값만 전달합니다. 자세한 내용은 JSON Path Syntax를 참조하십시오.

NESTED PATH(또는 간단히 NESTED; PATH는 선택 사항)는 자신이 속한 COLUMNS 절에서의 각 match에 대해 레코드 집합을 생성합니다. match가 없으면, nested path의 모든 컬럼은 NULL로 설정됩니다. 이는 최상위 절과 NESTED [PATH] 간의 외부 조인을 구현합니다. 내부 조인은 WHERE 절에 적절한 조건을 적용하여 흉내 낼 수 있으며, 다음과 같습니다:

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
5    ->     '$[*]' COLUMNS(
6    ->             a INT PATH '$.a',
7    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
8    ->            )
9    ->    ) AS jt
10    -> WHERE b IS NOT NULL;
11
12+------+------+
13| a    | b    |
14+------+------+
15|    1 |   11 |
16|    1 |  111 |
17|    2 |   22 |
18|    2 |  222 |
19+------+------+

형제 nested path, 즉 같은 COLUMNS 절에 있는 둘 이상의 NESTED [PATH] 인스턴스는 한 번에 하나씩 차례대로 처리됩니다. 하나의 nested path가 레코드를 생성하는 동안, 형제 nested path 표현식의 컬럼은 NULL로 설정됩니다. 이는 단일 COLUMNS 절 내 단일 match에 대한 레코드의 총 개수가 NESTED [PATH] 한정자가 생성하는 모든 레코드의 곱이 아니라 합이라는 것을 의미하며, 다음과 같습니다:

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
5    ->     '$[*]' COLUMNS(
6    ->         a INT PATH '$.a',
7    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
8    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
9    ->     )
10    -> ) AS jt;
11
12+------+------+------+
13| a    | b1   | b2   |
14+------+------+------+
15|    1 |   11 | NULL |
16|    1 |  111 | NULL |
17|    1 | NULL |   11 |
18|    1 | NULL |  111 |
19|    2 |   22 | NULL |
20|    2 |  222 | NULL |
21|    2 | NULL |   22 |
22|    2 | NULL |  222 |
23+------+------+------+

FOR ORDINALITY 컬럼은 COLUMNS 절에 의해 생성된 레코드를 번호 매기며, 특히 부모 레코드의 값이 동일한 경우 nested path의 부모 레코드를 구분하는 데 사용할 수 있습니다. 예시는 다음과 같습니다:

1mysql> SELECT *
2    -> FROM
3    ->   JSON_TABLE(
4    ->     '[{"a": "a_val",\
5    '>       "b": [{"c": "c_val", "l": [1,2]}]},\
6    '>     {"a": "a_val",\
7    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
8    ->     '$[*]' COLUMNS(
9    ->       top_ord FOR ORDINALITY,
10    ->       apath VARCHAR(10) PATH '$.a',
11    ->       NESTED PATH '$.b[*]' COLUMNS (
12    ->         bpath VARCHAR(10) PATH '$.c',
13    ->         ord FOR ORDINALITY,
14    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
15    ->         )
16    ->     )
17    -> ) as jt;
18
19+---------+---------+---------+------+-------+
20| top_ord | apath   | bpath   | ord  | lpath |
21+---------+---------+---------+------+-------+
22|       1 |  a_val  |  c_val  |    1 | 1     |
23|       1 |  a_val  |  c_val  |    1 | 2     |
24|       2 |  a_val  |  c_val  |    1 | 11    |
25|       2 |  a_val  |  c_val  |    2 | 22    |
26+---------+---------+---------+------+-------+

소스 문서에는 두 개의 요소를 가진 배열이 포함되어 있으며, 각 요소는 두 개의 행을 생성합니다. apath와 bpath의 값은 전체 결과 집합에서 동일하므로, 이 값들만으로는 lpath 값이 동일한 부모에서 왔는지 또는 서로 다른 부모에서 왔는지를 판단할 수 없습니다. ord 컬럼의 값은 top_ord가 1인 레코드 집합에서는 동일하게 유지되므로, 이 두 값은 단일 오브젝트에서 온 것입니다. 나머지 두 값은 ord 컬럼의 값이 서로 다르므로 서로 다른 오브젝트에서 온 것입니다.

일반적으로, 같은 FROM 절에서 앞에 나오는 테이블의 컬럼에 의존하는 파생 테이블을 조인할 수는 없습니다. MySQL은 SQL 표준에 따라 테이블 함수에 대해 예외를 둡니다; 이들은 lateral 파생 테이블로 간주됩니다. 이는 묵시적이며, 이 이유로 표준에 따라 JSON_TABLE() 앞에서는 LATERAL 키워드가 허용되지 않습니다.

다음과 같이 문장을 사용하여 생성 및 데이터를 삽입한 테이블 t1이 있다고 가정합니다:

1CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);
2
3INSERT INTO t1 () VALUES
4	ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
5	ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
6	ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
7	ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
8	ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
9;

그런 다음 다음과 같이 조인을 수행할 수 있습니다. 이때 JSON_TABLE()은 파생 테이블로 동작하면서, 동시에 앞에서 참조한 테이블의 컬럼을 참조합니다:

1SELECT c1, c2, JSON_EXTRACT(c3, '$.*')
2FROM t1 AS m
3JOIN
4JSON_TABLE(
5  m.c3,
6  '$.*'
7  COLUMNS(
8    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
9    bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,
10    ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
11  )
12) AS tt
13ON m.c1 > tt.at;

이 쿼리에서 LATERAL 키워드를 사용하려고 시도하면 ER_PARSE_ERROR가 발생합니다.