14.17.3 Functions That Search JSON Values

14.17.3 Functions That Search JSON Values

이 절의 함수들은 JSON 값에 대해 검색 또는 비교 연산을 수행하여 그 안에서 데이터를 추출하거나, 특정 위치에 데이터가 존재하는지 보고하거나, 그 데이터에 대한 경로를 보고합니다. MEMBER OF() 연산자도 이 절에서 설명합니다.

• JSON_CONTAINS(target, candidate[, path])

주어진 candidate JSON 도큐먼트가 target JSON 도큐먼트 안에 포함되는지, 또는 path 인자가 제공되었다면 candidate가 target 내의 특정 path에 존재하는지를 1 또는 0으로 반환하여 나타냅니다. 어떤 인자라도 NULL이면, 또는 path 인자가 target 도큐먼트의 일부를 식별하지 못하면 NULL을 반환합니다. target 또는 _candidate_가 유효한 JSON 도큐먼트가 아니거나, path 인자가 유효한 path 표현식이 아니거나 * 또는 ** 와일드카드를 포함하는 경우 에러가 발생합니다.

해당 path에 어떤 데이터든 존재하는지만 확인하려면, 대신 JSON_CONTAINS_PATH()를 사용하십시오.

포함 관계는 다음 규칙으로 정의됩니다:

• candidate 스칼라는 target 스칼라와 비교 가능하고 값이 동일할 때이면서 그 경우에만 target 스칼라 안에 포함됩니다. 두 스칼라 값은 동일한 JSON_TYPE() 타입을 가질 때 비교 가능합니다. 단, INTEGER 타입과 DECIMAL 타입 값은 서로 비교 가능합니다.

• candidate 배열은 candidate의 모든 요소가 target의 어떤 요소 안에 포함될 때이면서 그 경우에만 target 배열 안에 포함됩니다.

• candidate 비배열은 candidate가 target의 어떤 요소 안에 포함될 때이면서 그 경우에만 target 배열 안에 포함됩니다.

• candidate 오브젝트는 candidate의 각 key에 대해, target에 동일한 이름의 key가 존재하고, candidate key와 연관된 값이 target key와 연관된 값 안에 포함될 때이면서 그 경우에만 target 오브젝트 안에 포함됩니다.

그 외의 경우에는 candidate 값은 target 도큐먼트 안에 포함되지 않습니다.

InnoDB 테이블에서 JSON_CONTAINS()를 사용하는 쿼리는 멀티 값 인덱스를 사용하여 최적화될 수 있습니다. 자세한 내용은 Multi-Valued Indexes를 참조하십시오.

1mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
2mysql> SET @j2 = '1';
3mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
4+-------------------------------+
5| JSON_CONTAINS(@j, @j2, '$.a') |
6+-------------------------------+
7|                             1 |
8+-------------------------------+
9mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
10+-------------------------------+
11| JSON_CONTAINS(@j, @j2, '$.b') |
12+-------------------------------+
13|                             0 |
14+-------------------------------+
15
16mysql> SET @j2 = '{"d": 4}';
17mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
18+-------------------------------+
19| JSON_CONTAINS(@j, @j2, '$.a') |
20+-------------------------------+
21|                             0 |
22+-------------------------------+
23mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
24+-------------------------------+
25| JSON_CONTAINS(@j, @j2, '$.c') |
26+-------------------------------+
27|                             1 |
28+-------------------------------+

• JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

JSON 도큐먼트가 주어진 path 또는 여러 path에서 데이터를 포함하는지 0 또는 1로 반환합니다. 어떤 인자라도 NULL이면 NULL을 반환합니다. json_doc 인자가 유효한 JSON 도큐먼트가 아니거나, 어떤 path 인자라도 유효한 path 표현식이 아니거나, _one_or_all_이 'one' 또는 'all'이 아니면 에러가 발생합니다.

특정 path에서 특정 값을 확인하려면 대신 JSON_CONTAINS()를 사용하십시오.

지정된 path들 중 어느 것도 도큐먼트 안에 존재하지 않으면 반환값은 0입니다. 그렇지 않으면 반환값은 one_or_all 인자에 따라 달라집니다:

• 'one': 도큐먼트 안에 적어도 하나의 path가 존재하면 1, 그렇지 않으면 0.

• 'all': 도큐먼트 안에 모든 path가 존재하면 1, 그렇지 않으면 0.

1mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
2mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
3+---------------------------------------------+
4| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
5+---------------------------------------------+
6|                                           1 |
7+---------------------------------------------+
8mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
9+---------------------------------------------+
10| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
11+---------------------------------------------+
12|                                           0 |
13+---------------------------------------------+
14mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
15+----------------------------------------+
16| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
17+----------------------------------------+
18|                                      1 |
19+----------------------------------------+
20mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
21+----------------------------------------+
22| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
23+----------------------------------------+
24|                                      0 |
25+----------------------------------------+

• JSON_EXTRACT(json_doc, path[, path] ...)

path 인자와 일치하는 도큐먼트의 부분에서 선택된 JSON 도큐먼트의 데이터를 반환합니다. 어떤 인자라도 NULL이거나, 어떤 path도 도큐먼트에서 값을 찾지 못하면 NULL을 반환합니다. json_doc 인자가 유효한 JSON 도큐먼트가 아니거나, 어떤 path 인자라도 유효한 path 표현식이 아니면 에러가 발생합니다.

반환값은 path 인자들에 의해 매치된 모든 값들로 구성됩니다. 이들 인자가 여러 값을 반환할 수 있는 경우, 매치된 값들은 그것들을 생성한 path에 대응하는 순서로 배열로 자동 래핑됩니다. 그렇지 않으면 반환값은 단일 매치 값입니다.

1mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
2+--------------------------------------------+
3| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
4+--------------------------------------------+
5| 20                                         |
6+--------------------------------------------+
7mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
8+----------------------------------------------------+
9| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
10+----------------------------------------------------+
11| [20, 10]                                           |
12+----------------------------------------------------+
13mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
14+-----------------------------------------------+
15| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
16+-----------------------------------------------+
17| [30, 40]                                      |
18+-----------------------------------------------+

MySQL은 왼쪽이 JSON 컬럼 식별자(식이 아님)이고, 오른쪽이 컬럼 내에서 매치될 JSON path인 두 개의 인자와 함께 사용될 때, 이 함수의 축약형으로 -> 연산자를 지원합니다.

• column->path

-> 연산자는 두 개의 인자와 함께 사용할 때 왼쪽에는 컬럼 식별자, 오른쪽에는 JSON 도큐먼트(컬럼 값)에 대해 평가되는 JSON path(문자열 리터럴)가 오는 JSON_EXTRACT() 함수의 별칭 역할을 합니다. 이러한 식은 SQL 문 어디에서든지 컬럼 참조 대신 사용할 수 있습니다.

다음의 두 SELECT 문은 동일한 출력을 생성합니다:

1mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
2       > FROM jemp
3       > WHERE JSON_EXTRACT(c, "$.id") > 1
4       > ORDER BY JSON_EXTRACT(c, "$.name");
5+-------------------------------+-----------+------+
6| c                             | c->"$.id" | g    |
7+-------------------------------+-----------+------+
8| {"id": "3", "name": "Barney"} | "3"       |    3 |
9| {"id": "4", "name": "Betty"}  | "4"       |    4 |
10| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
11+-------------------------------+-----------+------+
123 rows in set (0.00 sec)
13
14mysql> SELECT c, c->"$.id", g
15       > FROM jemp
16       > WHERE c->"$.id" > 1
17       > ORDER BY c->"$.name";
18+-------------------------------+-----------+------+
19| c                             | c->"$.id" | g    |
20+-------------------------------+-----------+------+
21| {"id": "3", "name": "Barney"} | "3"       |    3 |
22| {"id": "4", "name": "Betty"}  | "4"       |    4 |
23| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
24+-------------------------------+-----------+------+
253 rows in set (0.00 sec)

이 기능은 다음에 보이는 것처럼 SELECT에만 제한되지 않습니다:

1mysql> ALTER TABLE jemp ADD COLUMN n INT;
2Query OK, 0 rows affected (0.68 sec)
3Records: 0  Duplicates: 0  Warnings: 0
4
5mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
6Query OK, 1 row affected (0.04 sec)
7Rows matched: 1  Changed: 1  Warnings: 0
8
9mysql> SELECT c, c->"$.id", g, n
10       > FROM jemp
11       > WHERE JSON_EXTRACT(c, "$.id") > 1
12       > ORDER BY c->"$.name";
13+-------------------------------+-----------+------+------+
14| c                             | c->"$.id" | g    | n    |
15+-------------------------------+-----------+------+------+
16| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
17| {"id": "4", "name": "Betty"}  | "4"       |    4 |    1 |
18| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
19+-------------------------------+-----------+------+------+
203 rows in set (0.00 sec)
21
22mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
23Query OK, 1 row affected (0.04 sec)
24
25mysql> SELECT c, c->"$.id", g, n
26       > FROM jemp
27       > WHERE JSON_EXTRACT(c, "$.id") > 1
28       > ORDER BY c->"$.name";
29+-------------------------------+-----------+------+------+
30| c                             | c->"$.id" | g    | n    |
31+-------------------------------+-----------+------+------+
32| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
33| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
34+-------------------------------+-----------+------+------+
352 rows in set (0.00 sec)

(방금 보여 준 테이블을 생성하고 데이터를 채우는 데 사용된 문장은 Indexing a Generated Column to Provide a JSON Column Index를 참조하십시오.)

이 기능은 다음과 같이 JSON 배열 값에도 사용할 수 있습니다:

1mysql> CREATE TABLE tj10 (a JSON, b INT);
2Query OK, 0 rows affected (0.26 sec)
3
4mysql> INSERT INTO tj10
5       > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
6Query OK, 1 row affected (0.04 sec)
7
8mysql> SELECT a->"$[4]" FROM tj10;
9+--------------+
10| a->"$[4]"    |
11+--------------+
12| 44           |
13| [22, 44, 66] |
14+--------------+
152 rows in set (0.00 sec)
16
17mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
18+------------------------------+------+
19| a                            | b    |
20+------------------------------+------+
21| [3, 10, 5, 17, 44]           |   33 |
22| [3, 10, 5, 17, [22, 44, 66]] |    0 |
23+------------------------------+------+
242 rows in set (0.00 sec)

중첩 배열도 지원합니다. ->를 사용하는 식은, target JSON 도큐먼트에서 일치하는 key를 찾지 못한 경우, 다음과 같이 NULL로 평가됩니다:

1mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
2+------------------------------+------+
3| a                            | b    |
4+------------------------------+------+
5| [3, 10, 5, 17, [22, 44, 66]] |    0 |
6+------------------------------+------+
7
8mysql> SELECT a->"$[4][1]" FROM tj10;
9+--------------+
10| a->"$[4][1]" |
11+--------------+
12| NULL         |
13| 44           |
14+--------------+
152 rows in set (0.00 sec)

이는 다음과 같이 JSON_EXTRACT()를 사용할 때 이러한 경우에 나타나는 동작과 동일합니다:

1mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
2+----------------------------+
3| JSON_EXTRACT(a, "$[4][1]") |
4+----------------------------+
5| NULL                       |
6| 44                         |
7+----------------------------+
82 rows in set (0.00 sec)

• column->>path

이는 향상된 unquoting 추출 연산자입니다. -> 연산자가 단순히 값을 추출하는 반면, ->> 연산자는 추출된 결과의 따옴표를 제거(unquote)합니다. 즉, JSON 컬럼 값 _column_과 path 표현식 path(문자열 리터럴)가 주어졌을 때, 다음 세 식은 동일한 값을 반환합니다:

• JSON_UNQUOTE( JSON_EXTRACT(column, path) )

• JSON_UNQUOTE(column ->path)

• column->>path

->> 연산자는 JSON_UNQUOTE(JSON_EXTRACT())가 허용되는 곳 어디에서나 사용할 수 있습니다. 여기에는(이에 한정되지 않지만) SELECT 목록, WHERE 및 HAVING 절, ORDER BY 및 GROUP BY 절이 포함됩니다.

다음의 몇 개 문장은 mysql 클라이언트에서 ->> 연산자가 다른 식과 동등함을 보여 줍니다:

1mysql> SELECT * FROM jemp WHERE g > 2;
2+-------------------------------+------+
3| c                             | g    |
4+-------------------------------+------+
5| {"id": "3", "name": "Barney"} |    3 |
6| {"id": "4", "name": "Betty"}  |    4 |
7+-------------------------------+------+
82 rows in set (0.01 sec)
9
10mysql> SELECT c->'$.name' AS name
11    ->     FROM jemp WHERE g > 2;
12+----------+
13| name     |
14+----------+
15| "Barney" |
16| "Betty"  |
17+----------+
182 rows in set (0.00 sec)
19
20mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
21    ->     FROM jemp WHERE g > 2;
22+--------+
23| name   |
24+--------+
25| Barney |
26| Betty  |
27+--------+
282 rows in set (0.00 sec)
29
30mysql> SELECT c->>'$.name' AS name
31    ->     FROM jemp WHERE g > 2;
32+--------+
33| name   |
34+--------+
35| Barney |
36| Betty  |
37+--------+
382 rows in set (0.00 sec)

방금 보여 준 예제의 jemp 테이블을 생성하고 채우는 데 사용된 SQL 문은 Indexing a Generated Column to Provide a JSON Column Index를 참조하십시오.

이 연산자는 다음과 같이 JSON 배열에도 사용할 수 있습니다:

1mysql> CREATE TABLE tj10 (a JSON, b INT);
2Query OK, 0 rows affected (0.26 sec)
3
4mysql> INSERT INTO tj10 VALUES
5    ->     ('[3,10,5,"x",44]', 33),
6    ->     ('[3,10,5,17,[22,"y",66]]', 0);
7Query OK, 2 rows affected (0.04 sec)
8Records: 2  Duplicates: 0  Warnings: 0
9
10mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
11+-----------+--------------+
12| a->"$[3]" | a->"$[4][1]" |
13+-----------+--------------+
14| "x"       | NULL         |
15| 17        | "y"          |
16+-----------+--------------+
172 rows in set (0.00 sec)
18
19mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
20+------------+---------------+
21| a->>"$[3]" | a->>"$[4][1]" |
22+------------+---------------+
23| x          | NULL          |
24| 17         | y             |
25+------------+---------------+
262 rows in set (0.00 sec)

->와 마찬가지로, ->> 연산자는 EXPLAIN 출력에서 항상 전개(expand)되며, 이는 다음 예제에서 볼 수 있습니다:

1mysql> EXPLAIN SELECT c->>'$.name' AS name
2    ->     FROM jemp WHERE g > 2\G
3*************************** 1. row ***************************
4           id: 1
5  select_type: SIMPLE
6        table: jemp
7   partitions: NULL
8         type: range
9possible_keys: i
10          key: i
11      key_len: 5
12          ref: NULL
13         rows: 2
14     filtered: 100.00
15        Extra: Using where
161 row in set, 1 warning (0.00 sec)
17
18mysql> SHOW WARNINGS\G
19*************************** 1. row ***************************
20  Level: Note
21   Code: 1003
22Message: /* select#1 */ select
23json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
24`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
251 row in set (0.00 sec)

이는 동일한 상황에서 MySQL이 -> 연산자를 전개하는 방식과 유사합니다.

• JSON_KEYS(json_doc[, path])

JSON 오브젝트의 top-level 값에서 key들을 JSON 배열로 반환하거나, path 인자가 주어진 경우 선택된 path에서의 top-level key들을 JSON 배열로 반환합니다. 어떤 인자라도 NULL이면, 또는 json_doc 인자가 오브젝트가 아니거나, 주어진 경우 _path_가 오브젝트를 찾지 못하면 NULL을 반환합니다. json_doc 인자가 유효한 JSON 도큐먼트가 아니거나, path 인자가 유효한 path 표현식이 아니거나 * 또는 ** 와일드카드를 포함하면 에러가 발생합니다.

선택된 오브젝트가 비어 있으면 결과 배열은 비어 있습니다. top-level 값에 중첩된 서브오브젝트가 있는 경우, 반환값에는 이러한 서브오브젝트의 key는 포함되지 않습니다.

1mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
2+---------------------------------------+
3| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
4+---------------------------------------+
5| ["a", "b"]                            |
6+---------------------------------------+
7mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
8+----------------------------------------------+
9| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
10+----------------------------------------------+
11| ["c"]                                        |
12+----------------------------------------------+

• JSON_OVERLAPS(json_doc1, json_doc2)

두 JSON 도큐먼트를 비교합니다. 두 도큐먼트에 공통된 key-value 쌍 또는 배열 요소가 하나라도 있으면 true(1)를 반환합니다. 두 인자가 모두 스칼라이면 함수는 단순 동등성 테스트를 수행합니다. 어느 인자라도 NULL이면 함수는 NULL을 반환합니다.

이 함수는 검색 대상 배열의 모든 요소가 검색할 배열 안에 존재해야 하는 JSON_CONTAINS()의 대응 함수 역할을 합니다. 따라서 JSON_CONTAINS()는 검색 키에 대해 AND 연산을 수행하는 반면, JSON_OVERLAPS()는 OR 연산을 수행합니다.

InnoDB 테이블의 JSON 컬럼에서 WHERE 절에 JSON_OVERLAPS()를 사용하는 쿼리는 멀티 값 인덱스를 사용하여 최적화될 수 있습니다. 자세한 정보와 예제는 Multi-Valued Indexes를 참조하십시오.

두 배열을 비교할 때, JSON_OVERLAPS()는 두 배열에 공통 배열 요소가 하나 이상 있으면 true를 반환하고, 그렇지 않으면 false를 반환합니다:

1mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
2+---------------------------------------+
3| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
4+---------------------------------------+
5|                                     1 |
6+---------------------------------------+
71 row in set (0.00 sec)
8
9mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
10+---------------------------------------+
11| JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |
12+---------------------------------------+
13|                                     1 |
14+---------------------------------------+
151 row in set (0.00 sec)
16
17mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
18+---------------------------------------+
19| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
20+---------------------------------------+
21|                                     0 |
22+---------------------------------------+
231 row in set (0.00 sec)

부분 일치는 매치로 간주되지 않으며, 이는 다음과 같습니다:

1mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
2+-----------------------------------------------------+
3| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
4+-----------------------------------------------------+
5|                                                   0 |
6+-----------------------------------------------------+
71 row in set (0.00 sec)

오브젝트를 비교할 때는, 두 오브젝트에 공통 key-value 쌍이 하나 이상 있으면 결과는 true입니다.

1mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
2+-----------------------------------------------------------------------+
3| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
4+-----------------------------------------------------------------------+
5|                                                                     1 |
6+-----------------------------------------------------------------------+
71 row in set (0.00 sec)
8
9mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
10+-----------------------------------------------------------------------+
11| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
12+-----------------------------------------------------------------------+
13|                                                                     0 |
14+-----------------------------------------------------------------------+
151 row in set (0.00 sec)

두 스칼라가 함수의 인자로 사용되면, JSON_OVERLAPS()는 단순 동등성 테스트를 수행합니다:

1mysql> SELECT JSON_OVERLAPS('5', '5');
2+-------------------------+
3| JSON_OVERLAPS('5', '5') |
4+-------------------------+
5|                       1 |
6+-------------------------+
71 row in set (0.00 sec)
8
9mysql> SELECT JSON_OVERLAPS('5', '6');
10+-------------------------+
11| JSON_OVERLAPS('5', '6') |
12+-------------------------+
13|                       0 |
14+-------------------------+
151 row in set (0.00 sec)

스칼라와 배열을 비교할 때, JSON_OVERLAPS()는 스칼라를 배열 요소로 취급하려고 시도합니다. 이 예에서 두 번째 인자 6은 다음과 같이 [6]으로 해석됩니다:

1mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
2+---------------------------------+
3| JSON_OVERLAPS('[4,5,6,7]', '6') |
4+---------------------------------+
5|                               1 |
6+---------------------------------+
71 row in set (0.00 sec)

이 함수는 타입 변환을 수행하지 않습니다:

1mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
2+-----------------------------------+
3| JSON_OVERLAPS('[4,5,"6",7]', '6') |
4+-----------------------------------+
5|                                 0 |
6+-----------------------------------+
71 row in set (0.00 sec)
8
9mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
10+-----------------------------------+
11| JSON_OVERLAPS('[4,5,6,7]', '"6"') |
12+-----------------------------------+
13|                                 0 |
14+-----------------------------------+
151 row in set (0.00 sec)

• JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

JSON 도큐먼트 안에서 주어진 문자열에 대한 path를 반환합니다. json_doc, search_str, path 인자 중 어느 하나라도 NULL이면, 또는 도큐먼트 안에 path 가 존재하지 않거나, search_str 을 찾지 못하면 NULL을 반환합니다. json_doc 인자가 유효한 JSON 도큐먼트가 아니거나, 어떤 path 인자라도 유효한 path 표현식이 아니거나, one_or_all 이 'one' 또는 'all'이 아니거나, escape_char 가 상수식이 아니면 에러가 발생합니다.

one_or_all 인자는 검색에 다음과 같이 영향을 줍니다:

• 'one': 첫 번째 매치 후 검색을 종료하고 하나의 path 문자열을 반환합니다. 어떤 매치가 먼저로 간주되는지는 정의되어 있지 않습니다.

• 'all': 중복 path가 포함되지 않도록 모든 매치되는 path 문자열을 반환합니다. 여러 문자열이 있는 경우 배열로 자동 래핑됩니다. 배열 요소의 순서는 정의되어 있지 않습니다.

search_str 검색 문자열 인자 안에서, %와 _ 문자는 LIKE 연산자에서와 같이 동작합니다: %는 임의의 개수(0개 포함)의 문자를 매치하고, _는 정확히 한 문자를 매치합니다.

검색 문자열 안에 리터럴 % 또는 _ 문자를 지정하려면 그 앞에 이스케이프 문자를 붙입니다. escape_char 인자가 없거나 NULL이면 기본값은 \입니다. 그 외의 경우 escape_char 는 비어 있거나 한 글자인 상수여야 합니다.

매칭 및 이스케이프 문자 동작에 대한 더 많은 정보는 Section 14.8.1, “String Comparison Functions and Operators”의 LIKE 설명을 참조하십시오. 이스케이프 문자 처리와 관련된, LIKE 동작과의 차이점은 JSON_SEARCH()의 이스케이프 문자는 실행 시간뿐 아니라 컴파일 시간에도 상수로 평가되어야 한다는 점입니다. 예를 들어, JSON_SEARCH()가 prepared statement에서 사용되고 escape_char 인자가 ? 파라미터로 제공되는 경우, 파라미터 값은 실행 시간에는 상수일 수 있지만 컴파일 시간에는 그렇지 않습니다.

1mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
2
3mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
4+-------------------------------+
5| JSON_SEARCH(@j, 'one', 'abc') |
6+-------------------------------+
7| "$[0]"                        |
8+-------------------------------+
9
10mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
11+-------------------------------+
12| JSON_SEARCH(@j, 'all', 'abc') |
13+-------------------------------+
14| ["$[0]", "$[2].x"]            |
15+-------------------------------+
16
17mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
18+-------------------------------+
19| JSON_SEARCH(@j, 'all', 'ghi') |
20+-------------------------------+
21| NULL                          |
22+-------------------------------+
23
24mysql> SELECT JSON_SEARCH(@j, 'all', '10');
25+------------------------------+
26| JSON_SEARCH(@j, 'all', '10') |
27+------------------------------+
28| "$[1][0].k"                  |
29+------------------------------+
30
31mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
32+-----------------------------------------+
33| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
34+-----------------------------------------+
35| "$[1][0].k"                             |
36+-----------------------------------------+
37
38mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
39+--------------------------------------------+
40| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
41+--------------------------------------------+
42| "$[1][0].k"                                |
43+--------------------------------------------+
44
45mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
46+---------------------------------------------+
47| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
48+---------------------------------------------+
49| "$[1][0].k"                                 |
50+---------------------------------------------+
51
52mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
53+-------------------------------------------------+
54| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
55+-------------------------------------------------+
56| "$[1][0].k"                                     |
57+-------------------------------------------------+
58
59mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
60+--------------------------------------------+
61| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
62+--------------------------------------------+
63| "$[1][0].k"                                |
64+--------------------------------------------+
65
66mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
67+-----------------------------------------------+
68| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
69+-----------------------------------------------+
70| "$[1][0].k"                                   |
71+-----------------------------------------------+
72
73mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
74+---------------------------------------------+
75| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
76+---------------------------------------------+
77| "$[2].x"                                    |
78+---------------------------------------------+
79
80mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
81+-------------------------------+
82| JSON_SEARCH(@j, 'all', '%a%') |
83+-------------------------------+
84| ["$[0]", "$[2].x"]            |
85+-------------------------------+
86
87mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
88+-------------------------------+
89| JSON_SEARCH(@j, 'all', '%b%') |
90+-------------------------------+
91| ["$[0]", "$[2].x", "$[3].y"]  |
92+-------------------------------+
93
94mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
95+---------------------------------------------+
96| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
97+---------------------------------------------+
98| "$[0]"                                      |
99+---------------------------------------------+
100
101mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
102+---------------------------------------------+
103| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
104+---------------------------------------------+
105| "$[2].x"                                    |
106+---------------------------------------------+
107
108mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
109+---------------------------------------------+
110| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
111+---------------------------------------------+
112| NULL                                        |
113+---------------------------------------------+
114
115mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
116+-------------------------------------------+
117| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
118+-------------------------------------------+
119| NULL                                      |
120+-------------------------------------------+
121
122mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
123+-------------------------------------------+
124| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
125+-------------------------------------------+
126| "$[3].y"                                  |
127+-------------------------------------------+

MySQL에서 지원하는 JSON path 문법 및 *, ** 와일드카드 연산자에 대한 규칙에 관한 자세한 정보는 JSON Path Syntax를 참조하십시오.

• JSON_VALUE(json_doc, path)

지정된 JSON 도큐먼트에서 주어진 path의 위치에서 값을 추출하고, 필요에 따라 원하는 타입으로 변환하여 추출된 값을 반환합니다. 전체 문법은 다음과 같습니다:

1JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
2
3on_empty:
4      {NULL | ERROR | DEFAULT value} ON EMPTY
5
6on_error:
7      {NULL | ERROR | DEFAULT value} ON ERROR

json_doc 은 유효한 JSON 도큐먼트입니다. 이 값이 NULL이면 함수는 NULL을 반환합니다.

path 는 도큐먼트 내 위치를 가리키는 JSON path입니다. 이는 문자열 리터럴 값이어야 합니다.

type 은 다음 데이터 타입 중 하나입니다:

• FLOAT

• DOUBLE

• DECIMAL

• SIGNED

• UNSIGNED

• DATE

• TIME

• DATETIME

• YEAR

  한 자리 또는 두 자리의 YEAR 값은 지원되지 않습니다.

• CHAR

• JSON

위에 나열된 타입은 CAST() 함수가 지원하는 (비배열) 타입과 동일합니다.

RETURNING 절로 지정되지 않은 경우, JSON_VALUE() 함수의 반환 타입은 VARCHAR(512)입니다. 반환 타입에 대해 문자 집합이 지정되지 않으면, JSON_VALUE()는 대소문자를 구분하는 바이너리 콜레이션을 사용하는 utf8mb4를 사용합니다; 반환 결과에 대한 문자 집합으로 utf8mb4를 지정한 경우, 서버는 대소문자를 구분하지 않는 이 문자 집합의 기본 콜레이션을 사용합니다.

지정된 path의 데이터가 JSON null 리터럴이거나 그로 해석되는 경우, 함수는 SQL NULL을 반환합니다.

on_empty 가 지정된 경우, JSON_VALUE()가 주어진 path에서 데이터를 찾지 못할 때의 동작을 결정하며, 이 절은 다음 값 중 하나를 가집니다:

• NULL ON EMPTY: 함수는 NULL을 반환합니다. 이것이 ON EMPTY의 기본 동작입니다.

• DEFAULT value ON EMPTY: 제공된 value 를 반환합니다. 이 값의 타입은 반환 타입과 일치해야 합니다.

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

사용되는 경우 on_error 는 에러가 발생했을 때 다음 값 중 하나를 가지며, 각각 다음과 같은 결과를 가집니다:

• NULL ON ERROR: JSON_VALUE()는 NULL을 반환합니다. ON ERROR 절이 사용되지 않으면 이것이 기본 동작입니다.

• DEFAULT value ON ERROR: 이 값이 반환되며, 이 값의 타입은 반환 타입과 일치해야 합니다.

• ERROR ON ERROR: 에러가 발생합니다.

ON EMPTY를 사용하는 경우, 어떠한 ON ERROR 절보다 먼저 와야 합니다. 이를 잘못된 순서로 지정하면 문법 에러가 발생합니다.

Error handling.

일반적으로 에러는 JSON_VALUE()에 의해 다음과 같이 처리됩니다:

• 모든 JSON 입력(도큐먼트 및 path)은 유효성 검사를 받습니다. 이들 중 하나라도 유효하지 않으면, ON ERROR 절을 트리거하지 않고 SQL 에러가 발생합니다.

• 다음 이벤트 중 하나라도 발생하면 ON ERROR가 트리거됩니다:

  • JSON 도큐먼트 안에서 여러 위치로 해석되는 path 등, 오브젝트 또는 배열을 추출하려는 시도

  • 'asdf'를 UNSIGNED 값으로 변환하려는 시도와 같은 변환 에러

  • 값의 잘림(truncation)

• 변환 에러는 NULL ON ERROR 또는 DEFAULT ... ON ERROR가 지정된 경우에도 항상 경고를 트리거합니다.

• 소스 JSON 도큐먼트( expr)가 지정된 위치( path)에 어떠한 데이터도 포함하지 않는 경우, ON EMPTY 절이 트리거됩니다.

예제.

두 개의 간단한 예는 다음과 같습니다:

1mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
2+--------------------------------------------------------------+
3| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
4+--------------------------------------------------------------+
5| Joe                                                          |
6+--------------------------------------------------------------+
7
8mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
9    -> RETURNING DECIMAL(4,2)) AS price;
10+-------+
11| price |
12+-------+
13| 49.95 |
14+-------+

JSON_VALUE()가 NULL을 반환하는 경우를 제외하면, 문 SELECT JSON_VALUE(json_doc, path RETURNING type)은 다음 문과 동등합니다:

1SELECT CAST(
2    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
3    AS type
4);

JSON_VALUE()는 많은 경우 생성 칼럼을 만들고 그 생성 칼럼에 인덱스를 생성할 필요를 제거함으로써 JSON 컬럼에 대한 인덱스 생성을 단순화합니다. 다음과 같이 JSON 컬럼을 가진 테이블 t1을 생성할 때, 해당 컬럼의 값과 매치되는 path를 사용하는 JSON_VALUE() 식에 인덱스를 생성하여 그 컬럼에 대해 작동하는 식에 인덱스를 생성할 수 있습니다:

1CREATE TABLE t1(
2    j JSON,
3    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
4);

다음 EXPLAIN 출력은 WHERE 절에서 인덱스 식을 사용하는 t1에 대한 쿼리가 이렇게 생성된 인덱스를 사용함을 보여 줍니다:

1mysql> EXPLAIN SELECT * FROM t1
2    ->     WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G
3*************************** 1. row ***************************
4           id: 1
5  select_type: SIMPLE
6        table: t1
7   partitions: NULL
8         type: ref
9possible_keys: i1
10          key: i1
11      key_len: 9
12          ref: const
13         rows: 1
14     filtered: 100.00
15        Extra: NULL

이는 생성 칼럼에 인덱스를 둔 테이블 t2를 생성하는 것과 거의 동일한 효과를 냅니다(자세한 내용은 Indexing a Generated Column to Provide a JSON Column Index 참조):

1CREATE TABLE t2 (
2    j JSON,
3    g INT GENERATED ALWAYS AS (j->"$.id"),
4    INDEX i1 (g)
5);

이 테이블에 대해 생성 칼럼을 참조하는 쿼리의 EXPLAIN 출력은 이전에 테이블 t1에 대한 쿼리에서와 동일한 방식으로 인덱스가 사용되고 있음을 보여 줍니다:

1mysql> EXPLAIN SELECT * FROM t2 WHERE g  = 123\G
2*************************** 1. row ***************************
3           id: 1
4  select_type: SIMPLE
5        table: t2
6   partitions: NULL
7         type: ref
8possible_keys: i1
9          key: i1
10      key_len: 5
11          ref: const
12         rows: 1
13     filtered: 100.00
14        Extra: NULL

JSON 컬럼에 대한 간접 인덱스를 제공하기 위해 생성 칼럼의 인덱스를 사용하는 방법에 대한 정보는 Indexing a Generated Column to Provide a JSON Column Index를 참조하십시오.

• value MEMBER OF(json_array)

value 가 json_array 의 요소이면 true(1)를 반환하고, 그렇지 않으면 false(0)를 반환합니다. value 는 스칼라 또는 JSON 도큐먼트여야 합니다. 스칼라인 경우 연산자는 이를 JSON 배열 요소로 취급하려고 시도합니다. value 또는 json_array 가 NULL 이면 함수는 NULL 을 반환합니다.

InnoDB 테이블의 JSON 컬럼에서 WHERE 절에 MEMBER OF()를 사용하는 쿼리는 멀티 값 인덱스를 사용하여 최적화될 수 있습니다. 자세한 정보와 예제는 Multi-Valued Indexes를 참조하십시오.

단순 스칼라는 다음과 같이 배열 값으로 처리됩니다:

1mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
2+-------------------------------------------+
3| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
4+-------------------------------------------+
5|                                         1 |
6+-------------------------------------------+
71 row in set (0.00 sec)
8
9mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
10+---------------------------------------------+
11| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
12+---------------------------------------------+
13|                                           1 |
14+---------------------------------------------+
151 row in set (0.00 sec)

배열 요소 값의 부분 일치는 매치로 처리되지 않습니다:

1mysql> SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
2+------------------------------------------+
3| 7 MEMBER OF('[23, "abc", 17, "ab", 10]') |
4+------------------------------------------+
5|                                        0 |
6+------------------------------------------+
71 row in set (0.00 sec)

1mysql> SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
2+--------------------------------------------+
3| 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |
4+--------------------------------------------+
5|                                          0 |
6+--------------------------------------------+
71 row in set (0.00 sec)

문자열 타입으로의 변환 및 문자열 타입으로부터의 변환은 수행되지 않습니다:

1mysql> SELECT
2      -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),
3      -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G
4*************************** 1. row ***************************
517 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0
6"17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0
71 row in set (0.00 sec)

값 자체가 배열인 경우 이 연산자를 사용하려면 이를 명시적으로 JSON 배열로 캐스트해야 합니다. 이는 CAST(... AS JSON)으로 수행할 수 있습니다:

1mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
2+--------------------------------------------------+
3| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
4+--------------------------------------------------+
5|                                                1 |
6+--------------------------------------------------+
71 row in set (0.00 sec)

다음과 같이 JSON_ARRAY() 함수를 사용하여 필요한 캐스트를 수행하는 것도 가능합니다:

1mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
2+--------------------------------------------+
3| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
4+--------------------------------------------+
5|                                          1 |
6+--------------------------------------------+
71 row in set (0.00 sec)

테스트할 값으로 사용되거나 target 배열에 나타나는 모든 JSON 오브젝트는 CAST(... AS JSON) 또는 JSON_OBJECT()를 사용하여 올바른 타입으로 강제(coerce)해야 합니다. 또한 JSON 오브젝트를 포함하는 target 배열 자체도 JSON_ARRAY를 사용하여 캐스트해야 합니다. 이는 다음의 문장 시퀀스에서 보여 줍니다:

1mysql> SET @a = CAST('{"a":1}' AS JSON);
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SET @b = JSON_OBJECT("b", 2);
5Query OK, 0 rows affected (0.00 sec)
6
7mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
8Query OK, 0 rows affected (0.00 sec)
9
10mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
11+------------------+------------------+
12| @a MEMBER OF(@c) | @b MEMBER OF(@c) |
13+------------------+------------------+
14|                1 |                1 |
15+------------------+------------------+
161 row in set (0.00 sec)