14.17.8 JSON Utility Functions

14.17.8 JSON Utility Functions

이 섹션에서는 JSON 값이나 JSON 값으로 파싱할 수 있는 문자열에 대해 동작하는 유틸리티 함수들을 문서화합니다. JSON_PRETTY()는 JSON 값을 읽기 쉬운 형식으로 출력합니다. JSON_STORAGE_SIZE()와 JSON_STORAGE_FREE()는 각각 지정된 JSON 값이 사용하는 저장 공간의 양과 부분 업데이트 이후 JSON 컬럼에 남아 있는 공간의 양을 보여 줍니다.

• JSON_PRETTY(json_val)

PHP 및 다른 언어와 데이터베이스 시스템에서 구현된 것과 유사한 JSON 값의 프리티 프린팅을 제공합니다. 제공되는 값은 JSON 값이거나 JSON 값의 유효한 문자열 표현이어야 합니다. 이 값에 포함된 불필요한 공백과 줄 바꿈은 출력에 영향을 주지 않습니다. NULL 값에 대해서는 함수가 NULL을 반환합니다. 값이 JSON 도큐먼트가 아니거나 JSON 도큐먼트로 파싱할 수 없는 경우, 함수는 에러와 함께 실패합니다.

이 함수의 출력 형식은 다음 규칙을 따릅니다:

• 각 배열 요소 또는 오브젝트 멤버는 상위 요소보다 한 단계 더 들여쓰기된 별도의 줄에 나타납니다.

• 들여쓰기의 각 단계는 선행 공백 두 개를 추가합니다.

• 개별 배열 요소 또는 오브젝트 멤버를 구분하는 콤마는 두 요소 또는 멤버를 구분하는 줄 바꿈 전에 출력됩니다.

• 오브젝트 멤버의 키와 값은 콜론 다음에 공백(':')으로 구분됩니다.

• 비어 있는 오브젝트 또는 배열은 한 줄로 출력됩니다. 여는 중괄호와 닫는 중괄호 사이에는 공백이 출력되지 않습니다.

• 문자열 스칼라와 키 이름에 있는 특수 문자는 JSON_QUOTE() 함수에서 사용하는 것과 동일한 규칙을 사용하여 이스케이프됩니다.

1mysql> SELECT JSON_PRETTY('123'); # scalar
2+--------------------+
3| JSON_PRETTY('123') |
4+--------------------+
5| 123                |
6+--------------------+
7
8mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
9+------------------------+
10| JSON_PRETTY("[1,3,5]") |
11+------------------------+
12| [\
13  1,\
14  3,\
15  5\
16]      |
17+------------------------+
18
19mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
20+---------------------------------------------+
21| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
22+---------------------------------------------+
23| {
24  "a": "10",
25  "b": "15",
26  "x": "25"
27}   |
28+---------------------------------------------+
29
30mysql> SELECT JSON_PRETTY('["a",1,{"key1":\
31    '>    "value1"},"5",     "77" ,\
32    '>       {"key2":["value3","valueX",\
33    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
34*************************** 1. row ***************************
35JSON_PRETTY('["a",1,{"key1":\
36             "value1"},"5",     "77" ,\
37                {"key2":["value3","valuex",\
38          "valuey"]},"j", "2"   ]'): [\
39  "a",\
40  1,\
41  {\
42    "key1": "value1"\
43  },\
44  "5",\
45  "77",\
46  {\
47    "key2": [\
48      "value3",\
49      "valuex",\
50      "valuey"\
51    ]\
52  },\
53  "j",\
54  "2"\
55]

• JSON_STORAGE_FREE(json_val)

JSON 컬럼 값에 대해 이 함수는 JSON_SET(), JSON_REPLACE(), JSON_REMOVE()을 사용하여 인플레이스 업데이트한 후, 그 바이너리 표현에서 얼마나 많은 저장 공간이 해제되었는지를 보여 줍니다.

인자는 리터럴 값이든 사용자 변수의 값이든, 유효한 JSON 도큐먼트이거나 JSON 도큐먼트로 파싱할 수 있는 문자열일 수도 있습니다. 이 경우 함수는 0을 반환합니다. 인자가 앞에서 설명한 대로 업데이트된 JSON 컬럼 값이고, 그 바이너리 표현이 업데이트 이전보다 더 적은 공간을 차지하게 되면 함수는 양의 0이 아닌 값을 반환합니다. 바이너리 표현이 업데이트 전과 같거나 더 커졌거나, 업데이트가 부분 업데이트를 활용하지 못한 JSON 컬럼의 경우에는 0을 반환합니다. 인자가 NULL이면 NULL을 반환합니다.

_json_val_이 NULL이 아니면서 유효한 JSON 도큐먼트도 아니고 JSON 도큐먼트로 성공적으로 파싱할 수도 없는 경우에는 에러가 발생합니다.

다음 예에서는 JSON 컬럼을 포함하는 테이블을 생성한 다음, JSON 오브젝트가 들어 있는 로우를 하나 인서트합니다:

1mysql> CREATE TABLE jtable (jcol JSON);
2Query OK, 0 rows affected (0.38 sec)
3
4mysql> INSERT INTO jtable VALUES
5      ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
6Query OK, 1 row affected (0.04 sec)
7
8mysql> SELECT * FROM jtable;
9+----------------------------------------------+
10| jcol                                         |
11+----------------------------------------------+
12| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
13+----------------------------------------------+
141 row in set (0.00 sec)

이제 JSON_SET()을 사용하여 컬럼 값을 업데이트하는데, 이렇게 하면 부분 업데이트를 수행할 수 있습니다. 이 경우 키 c가 가리키는 값(배열 [true,\ false])을 더 적은 공간을 차지하는 값(정수 1)으로 교체합니다:

1mysql> UPDATE jtable
2      ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
3Query OK, 1 row affected (0.03 sec)
4Rows matched: 1  Changed: 1  Warnings: 0
5
6mysql> SELECT * FROM jtable;
7+--------------------------------+
8| jcol                           |
9+--------------------------------+
10| {"a": 10, "b": "wxyz", "c": 1} |
11+--------------------------------+
121 row in set (0.00 sec)
13
14mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
15+-------------------------+
16| JSON_STORAGE_FREE(jcol) |
17+-------------------------+
18|                      14 |
19+-------------------------+
201 row in set (0.00 sec)

연속적인 부분 업데이트가 이 프리 스페이스에 미치는 효과는 누적됩니다. 다음 예에서는 JSON_SET()을 사용하여 키 b를 가지는 값이 차지하는 공간을 줄이고(그 외에는 어떤 변경도 하지 않음) 그 결과를 보여 줍니다:

1mysql> UPDATE jtable
2      ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
3Query OK, 1 row affected (0.03 sec)
4Rows matched: 1  Changed: 1  Warnings: 0
5
6mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
7+-------------------------+
8| JSON_STORAGE_FREE(jcol) |
9+-------------------------+
10|                      16 |
11+-------------------------+
121 row in set (0.00 sec)

JSON_SET(), JSON_REPLACE(), JSON_REMOVE()를 사용하지 않고 컬럼을 업데이트하면 옵티마이저는 인플레이스 업데이트를 수행할 수 없습니다. 이 경우 JSON_STORAGE_FREE()는 0을 반환하며, 다음과 같습니다:

1mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
2Query OK, 1 row affected (0.05 sec)
3Rows matched: 1  Changed: 1  Warnings: 0
4
5mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
6+-------------------------+
7| JSON_STORAGE_FREE(jcol) |
8+-------------------------+
9|                       0 |
10+-------------------------+
111 row in set (0.00 sec)

JSON 도큐먼트의 부분 업데이트는 컬럼 값에 대해서만 수행할 수 있습니다. JSON 값을 저장하는 사용자 변수의 경우, 업데이트가 JSON_SET()을 사용하여 수행되더라도 값은 항상 완전히 대체됩니다:

1mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
5Query OK, 0 rows affected (0.00 sec)
6
7mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
8+----------------------------------+------+
9| @j                               | Free |
10+----------------------------------+------+
11| {"a": 10, "b": "wxyz", "c": "1"} |    0 |
12+----------------------------------+------+
131 row in set (0.00 sec)

JSON 리터럴에 대해서는 이 함수가 항상 0을 반환합니다:

1mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
2+------+
3| Free |
4+------+
5|    0 |
6+------+
71 row in set (0.00 sec)

• JSON_STORAGE_SIZE(json_val)

이 함수는 JSON 도큐먼트의 바이너리 표현을 저장하는 데 사용되는 바이트 수를 반환합니다. 인자가 JSON 컬럼인 경우, 이는 컬럼에 JSON 도큐먼트가 인서트될 때 사용된 공간이며, 그 이후에 수행되었을 수 있는 어떤 부분 업데이트 이전의 공간입니다. _json_val_은 유효한 JSON 도큐먼트이거나 JSON 도큐먼트로 파싱할 수 있는 문자열이어야 합니다. 문자열인 경우, 함수는 문자열을 JSON으로 파싱하여 바이너리로 변환할 때 생성되는 JSON 바이너리 표현에 사용되는 저장 공간의 양을 반환합니다. 인자가 NULL이면 NULL을 반환합니다.

_json_val_이 NULL이 아니면서 JSON 도큐먼트가 아니거나 JSON 도큐먼트로 성공적으로 파싱할 수 없는 경우에는 에러가 발생합니다.

이 함수의 동작을 인자로 JSON 컬럼을 사용할 때 보여 주기 위해, JSON 컬럼 jcol을 포함하는 jtable이라는 테이블을 생성하고, 테이블에 JSON 값을 인서트한 다음, JSON_STORAGE_SIZE()를 사용하여 이 컬럼이 사용하는 저장 공간을 구해 보면 다음과 같습니다:

1mysql> CREATE TABLE jtable (jcol JSON);
2Query OK, 0 rows affected (0.42 sec)
3
4mysql> INSERT INTO jtable VALUES
5      ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
6Query OK, 1 row affected (0.04 sec)
7
8mysql> SELECT
9      ->     jcol,
10      ->     JSON_STORAGE_SIZE(jcol) AS Size,
11      ->     JSON_STORAGE_FREE(jcol) AS Free
12      -> FROM jtable;
13+-----------------------------------------------+------+------+
14| jcol                                          | Size | Free |
15+-----------------------------------------------+------+------+
16| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
17+-----------------------------------------------+------+------+
181 row in set (0.00 sec)

JSON_STORAGE_SIZE()의 출력에 따르면, 컬럼에 인서트된 JSON 도큐먼트는 47바이트를 차지합니다. 또한 JSON_STORAGE_FREE()를 사용하여 컬럼에 대한 이전 부분 업데이트에 의해 해제된 공간의 양을 확인했습니다. 아직 업데이트가 수행되지 않았으므로 예상대로 0입니다.

다음으로 jcol에 저장된 도큐먼트에 대해 부분 업데이트가 발생해야 하는 UPDATE를 테이블에 수행한 다음, 그 결과를 다음과 같이 확인합니다:

1mysql> UPDATE jtable SET jcol =
2      ->     JSON_SET(jcol, "$.b", "a");
3Query OK, 1 row affected (0.04 sec)
4Rows matched: 1  Changed: 1  Warnings: 0
5
6mysql> SELECT
7      ->     jcol,
8      ->     JSON_STORAGE_SIZE(jcol) AS Size,
9      ->     JSON_STORAGE_FREE(jcol) AS Free
10      -> FROM jtable;
11+--------------------------------------------+------+------+
12| jcol                                       | Size | Free |
13+--------------------------------------------+------+------+
14| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 |
15+--------------------------------------------+------+------+
161 row in set (0.00 sec)

이전 쿼리에서 JSON_STORAGE_FREE()가 반환한 값은 JSON 도큐먼트에 대해 부분 업데이트가 수행되었고, 이를 저장하는 데 사용된 공간 중 3바이트가 해제되었음을 나타냅니다. 부분 업데이트에 의해서도 JSON_STORAGE_SIZE()가 반환하는 결과는 변경되지 않습니다.

부분 업데이트는 JSON_SET(), JSON_REPLACE(), JSON_REMOVE()를 사용한 업데이트에 대해 지원됩니다. 값을 JSON 컬럼에 직접 대입하는 경우에는 부분적으로 업데이트할 수 없습니다. 이러한 업데이트 이후에는 JSON_STORAGE_SIZE()가 항상 새로 설정된 값에 사용되는 저장 공간을 보여 줍니다:

1mysql> UPDATE jtable
2mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
3Query OK, 1 row affected (0.04 sec)
4Rows matched: 1  Changed: 1  Warnings: 0
5
6mysql> SELECT
7      ->     jcol,
8      ->     JSON_STORAGE_SIZE(jcol) AS Size,
9      ->     JSON_STORAGE_FREE(jcol) AS Free
10      -> FROM jtable;
11+------------------------------------------------+------+------+
12| jcol                                           | Size | Free |
13+------------------------------------------------+------+------+
14| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
15+------------------------------------------------+------+------+
161 row in set (0.00 sec)

JSON 사용자 변수는 부분 업데이트할 수 없습니다. 이는 이 함수가 사용자 변수에 저장된 JSON 도큐먼트를 보관하는 데 현재 사용 중인 공간을 항상 보여 준다는 뜻입니다:

1mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
5+------------------------------------+------+
6| @j                                 | Size |
7+------------------------------------+------+
8| [100, "sakila", [1, 3, 5], 425.05] |   45 |
9+------------------------------------+------+
101 row in set (0.00 sec)
11
12mysql> SET @j = JSON_SET(@j, '$[1]', "json");
13Query OK, 0 rows affected (0.00 sec)
14
15mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
16+----------------------------------+------+
17| @j                               | Size |
18+----------------------------------+------+
19| [100, "json", [1, 3, 5], 425.05] |   43 |
20+----------------------------------+------+
211 row in set (0.00 sec)
22
23mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
24Query OK, 0 rows affected (0.00 sec)
25
26mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
27+---------------------------------------------+------+
28| @j                                          | Size |
29+---------------------------------------------+------+
30| [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
31+---------------------------------------------+------+
321 row in set (0.00 sec)

JSON 리터럴에 대해서는 이 함수가 항상 현재 사용 중인 저장 공간을 반환합니다:

1mysql> SELECT
2      ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
3      ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
4      ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
5      ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
6+----+----+----+----+
7| A  | B  | C  | D  |
8+----+----+----+----+
9| 45 | 44 | 47 | 56 |
10+----+----+----+----+
111 row in set (0.00 sec)