12.17.3 Functions That Search JSON Values — MySQL 5.7

12.17.3 Функции поиска значений JSON

Функции в этом разделе выполняют операции поиска по значениям JSON для извлечения данных из них, сообщают о наличии данных в определенном месте или сообщают путь к данным внутри них.

JSON_CONTAINS(target, candidate[, path])

Возвращает 1 или 0, указывая, содержится ли заданный candidate документ JSON в target документе JSON, или — если был предоставлен path аргумент — найден ли кандидат по конкретному пути в целевом документе. Возвращает NULL, если любой аргумент является NULL, или если аргумент пути не определяет раздел целевого документа. Возникает ошибка, если target или candidate не является допустимым документом JSON, или если path аргумент не является допустимым выражением пути или содержит подстановочный знак * или **.

Чтобы проверить только наличие данных по пути, используйте JSON_CONTAINS_PATH() вместо этого.

Следующие правила определяют включение:
- Кандидат-скаляр содержится в целевом скаляре тогда и только тогда, когда они сравнимы и равны. Два скалярных значения сравнимы, если у них одинаковые типы JSON_TYPE(), за исключением того, что значения типов INTEGER и DECIMAL также сравнимы друг с другом.
- Кандидат-массив содержится в целевом массиве тогда и только тогда, когда каждый элемент кандидата содержится в каком-то элементе цели.
- Кандидат-немассив содержится в целевом массиве тогда и только тогда, когда кандидат содержится в каком-то элементе цели.
- Кандидат-объект содержится в целевом объекте тогда и только тогда, когда для каждого ключа в кандидате существует ключ с тем же именем в цели, и значение, связанное с ключом кандидата, содержится в значении, связанном с ключом цели.
В противном случае, значение кандидата не содержится в целевом документе.
```
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
|                             0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+
```
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

Возвращает 0 или 1, указывая, содержит ли документ JSON данные по заданному пути или путям. Возвращает NULL, если любой аргумент является NULL. Возникает ошибка, если json_doc аргумент не является допустимым документом JSON, любой path аргумент не является допустимым выражением пути, или one_or_all не является 'one' или 'all'.

Чтобы проверить конкретное значение по пути, используйте JSON_CONTAINS() вместо этого.

Возвращаемое значение равно 0, если указанный путь не существует в документе. В противном случае, возвращаемое значение зависит от one_or_all аргумента:
- 'one': 1, если хотя бы один путь существует в документе, 0 в противном случае.
- 'all': 1, если все пути существуют в документе, 0 в противном случае.
```
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
```
JSON_EXTRACT(json_doc, path[, path] ...)

Возвращает данные из документа JSON, выбранные из частей документа, соответствующих path аргументам. Возвращает NULL, если любой аргумент является NULL или ни один путь не находит значения в документе. Возникает ошибка, если json_doc аргумент не является допустимым документом JSON или любой path аргумент не является допустимым выражением пути.

Возвращаемое значение состоит из всех значений, соответствующих path аргументам. Если возможно, что эти аргументы могут возвращать несколько значений, соответствующие значения автоматически оборачиваются в массив в порядке, соответствующем путям, которые их создали. В противном случае возвращаемым значением является единственное соответствующее значение.
```
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10]                                           |
+----------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+
```
MySQL 5.7.9 и более поздние версии поддерживают оператор -> как сокращение для этой функции, используемой с 2 аргументами, где левая часть является идентификатором столбца JSON (не выражение), а правая часть — путь JSON, который должен соответствовать столбцу.

column->path

В MySQL 5.7.9 и более поздних версиях оператор -> служит псевдонимом для функции JSON_EXTRACT(), когда используется с двумя аргументами: идентификатором столбца слева и путем JSON (строковым литералом) справа, который оценивается относительно документа JSON (значения столбца). Вы можете использовать такие выражения вместо ссылок на столбцы везде, где они встречаются в операторах SQL.

Два оператора SELECT, показанные здесь, дают одинаковый результат:

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

mysql> SELECT c, c->"$.id", g
     > FROM jemp
     > WHERE c->"$.id" > 1
     > ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

Эта функциональность не ограничивается SELECT, как показано здесь:

mysql> ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT c, c->"$.id", g, n
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c                             | c->"$.id" | g    | n    |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |    1 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
+-------------------------------+-----------+------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)

mysql> SELECT c, c->"$.id", g, n
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c                             | c->"$.id" | g    | n    |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
+-------------------------------+-----------+------+------+
2 rows in set (0.00 sec)

(См. Индексирование сгенерированного столбца для предоставления индекса столбца JSON, для операторов, используемых для создания и заполнения только что показанной таблицы.)

Это также работает со значениями массива JSON, как показано здесь:

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10
     > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT a->"$[4]" FROM tj10;
+--------------+
| a->"$[4]"    |
+--------------+
| 44           |
| [22, 44, 66] |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, 44]           |   33 |
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+
2 rows in set (0.00 sec)

Вложенные массивы поддерживаются. Выражение, использующее ->, оценивается как NULL, если в целевом документе JSON не найден соответствующий ключ, как показано здесь:

mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+

mysql> SELECT a->"$[4][1]" FROM tj10;
+--------------+
| a->"$[4][1]" |
+--------------+
| NULL         |
| 44           |
+--------------+
2 rows in set (0.00 sec)

Это то же самое поведение, что и в таких случаях при использовании JSON_EXTRACT():

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

column->>path

Это улучшенный оператор извлечения без кавычек, доступный в MySQL 5.7.13 и более поздних версиях. В то время как оператор -> просто извлекает значение, оператор ->> дополнительно снимает кавычки с извлеченного результата. Другими словами, учитывая значение столбца JSON column и выражение пути 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:

mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+
2 rows in set (0.01 sec)

mysql> SELECT c->'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+----------+
| name     |
+----------+
| "Barney" |
| "Betty"  |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

См. Индексирование сгенерированного столбца для предоставления индекса столбца JSON, для операторов SQL, используемых для создания и заполнения таблицы jemp в только что показанном наборе примеров.

Этот оператор также может использоваться с массивами JSON, как показано здесь:

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10 VALUES
    ->     ('[3,10,5,"x",44]', 33),
    ->     ('[3,10,5,17,[22,"y",66]]', 0);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
+-----------+--------------+
| a->"$[3]" | a->"$[4][1]" |
+-----------+--------------+
| "x"       | NULL         |
| 17        | "y"          |
+-----------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
+------------+---------------+
| a->>"$[3]" | a->>"$[4][1]" |
+------------+---------------+
| x          | NULL          |
| 17         | y             |
+------------+---------------+
2 rows in set (0.00 sec)

Как и ->, оператор ->> всегда расширяется в выводе EXPLAIN, как демонстрирует следующий пример:

mysql> EXPLAIN SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

Это аналогично тому, как MySQL расширяет оператор -> в тех же обстоятельствах.

Оператор ->> был добавлен в MySQL 5.7.13.

JSON_KEYS(json_doc[, path])

Возвращает ключи из значения верхнего уровня объекта JSON в виде массива JSON или, если задан path аргумент, ключи верхнего уровня из выбранного пути. Возвращает NULL, если любой аргумент является NULL, json_doc аргумент не является объектом, или path, если задан, не находит объект. Возникает ошибка, если json_doc аргумент не является допустимым документом JSON или path аргумент не является допустимым выражением пути или содержит подстановочный знак * или **.

Результирующий массив пуст, если выбранный объект пуст. Если значение верхнего уровня имеет вложенные подобъекты, возвращаемое значение не включает ключи из этих подобъектов.
```
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+
```

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

Возвращает путь к заданной строке в документе JSON. Возвращает NULL, если какой-либо из аргументов json_doc, search_str или path является NULL; в документе нет path; или search_str не найдено. Возникает ошибка, если аргумент json_doc не является корректным документом JSON, любой аргумент path не является корректным выражением пути, one_or_all не является 'one' или 'all', или escape_char не является константным выражением.

Аргумент one_or_all влияет на поиск следующим образом:

'one': Поиск завершается после первой совпавшей записи и возвращает одну строку пути. Не определено, какая запись считается первой.
'all': Поиск возвращает все совпадающие строки пути, при этом никакие пути не дублируются. Если таких строк несколько, они автоматически упаковываются в массив. Порядок элементов массива не определён.

В строке поиска search_str символы % и _ работают так же, как и для оператора LIKE: % соответствует любому количеству символов (включая ноль), а _ соответствует ровно одному символу.

Чтобы указать буквальный % или _ символ в строке поиска, необходимо поместить перед ним символ экранирования. По умолчанию используется \, если аргумент escape_char отсутствует или NULL. В противном случае, escape_char должен быть константой, которая пустая или состоит из одного символа.

Более подробную информацию о поиске и поведении символа экранирования см. в описании LIKE в Разделе 12.8.1, “Функции и операторы сравнения строк”. В отношении обработки символа экранирования есть отличие от поведения LIKE: символ экранирования для JSON_SEARCH() должен быть вычислен как константа во время компиляции, а не только во время выполнения. Например, если JSON_SEARCH() используется в подготовленном запросе, и аргумент escape_char предоставляется с помощью параметра ?, значение параметра может быть постоянным во время выполнения, но не во время компиляции.

search_str и path всегда интерпретируются как utf8mb4 строки, независимо от их фактической кодировки. Это известная проблема, которая исправлена в MySQL 8.0 (Ошибка #32449181).

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

Дополнительную информацию о синтаксисе путей JSON, поддерживаемом MySQL, включая правила, определяющие использование символов-подстановок * и **, см. в Синтаксисе путей JSON.