14.17.3 Functions That Search JSON Values — MySQL 8.4

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

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

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 также сравнимы друг с другом.
- Массив-кандидат содержится в целевом массиве тогда и только тогда, когда каждый элемент кандидата содержится в каком-либо элементе цели.
- Немассив-кандидат содержится в целевом массиве тогда и только тогда, когда кандидат содержится в каком-либо элементе цели.
- Объект-кандидат содержится в целевом объекте тогда и только тогда, когда для каждого ключа в кандидате существует ключ с тем же именем в цели и значение, связанное с ключом кандидата, содержится в значении, связанном с целевым ключом.
В противном случае, значение кандидата не содержится в целевом документе.

Запросы с использованием JSON_CONTAINS() в таблицах InnoDB могут быть оптимизированы с помощью индексов с несколькими значениями; см. Индексы с несколькими значениями для получения дополнительной информации.
```
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 поддерживает оператор -> как сокращение для этой функции, используемой с 2 аргументами, где левая часть является идентификатором столбца JSON (а не выражением), а правая часть — это JSON-путь, который должен быть сопоставлен в столбце.

column->path

Оператор -> служит псевдонимом для функции 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

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

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_OVERLAPS(json_doc1, json_doc2)

Сравнивает два JSON-документа. Возвращает true (1), если два документа имеют общие пары ключ-значение или элементы массива. Если оба аргумента являются скалярными значениями, функция выполняет простое сравнение на равенство. Если любой из аргументов является NULL, функция возвращает NULL.

Эта функция является аналогом функции JSON_CONTAINS(), которая требует, чтобы все элементы искомого массива присутствовали в массиве, в котором выполняется поиск. Таким образом, JSON_CONTAINS() выполняет операцию AND над ключами поиска, а JSON_OVERLAPS() выполняет операцию OR.

Запросы к JSON-столбцам таблиц InnoDB с использованием JSON_OVERLAPS() в предложении WHERE могут быть оптимизированы с помощью индексов с несколькими значениями. Индексы с несколькими значениями предоставляют подробную информацию и примеры.

При сравнении двух массивов, JSON_OVERLAPS() возвращает true, если они имеют один или несколько общих элементов массива, и false, если нет:

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

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

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

Частичные совпадения рассматриваются как отсутствие совпадения, как показано здесь:

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

При сравнении объектов результат является true, если у них есть хотя бы одна общая пара ключ-значение.

mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

Если в качестве аргументов функции используются два скалярных значения, JSON_OVERLAPS() выполняет простое сравнение на равенство:

mysql> SELECT JSON_OVERLAPS('5', '5');
+-------------------------+
| JSON_OVERLAPS('5', '5') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS('5', '6');
+-------------------------+
| JSON_OVERLAPS('5', '6') |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

При сравнении скалярного значения с массивом, JSON_OVERLAPS() пытается рассматривать скалярное значение как элемент массива. В этом примере второй аргумент 6 интерпретируется как [6], как показано здесь:

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

Функция не выполняет преобразования типов:

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

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

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-символом. Значение по умолчанию — \, если аргумент escape_char отсутствует или NULL. В противном случае, escape_char должен быть константой, которая пуста или состоит из одного символа.

Более подробную информацию о поиске и поведении escape-символа см. в описании оператора LIKE в разделе 14.8.1, «Функции и операторы сравнения строк». Что касается обработки escape-символов, есть отличие от поведения оператора LIKE: escape-символ для JSON_SEARCH() должен быть константой на этапе компиляции, а не только на этапе выполнения. Например, если JSON_SEARCH() используется в подготовленном запросе, и аргумент escape_char задаётся с помощью параметра ?, значение параметра может быть постоянным на этапе выполнения, но не на этапе компиляции.

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-путей.

JSON_VALUE(json_doc, path)

Извлекает значение из JSON-документа по указанному пути в заданном документе и возвращает извлеченное значение, необязательно преобразуя его к нужному типу. Полный синтаксис показан здесь:
```
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR
```
json_doc — это допустимый JSON-документ. Если это NULL, функция возвращает NULL.

path — это путь JSON, указывающий на местоположение в документе. Это должно быть строковое значение.

type — это один из следующих типов данных:
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR
  
  YEAR значения из одного или двух цифр не поддерживаются.
- CHAR
- JSON
Указанные типы такие же, как и типы (не массивы), поддерживаемые функцией CAST().

Если не указано с помощью RETURNING, тип возвращаемого значения функции JSON_VALUE() — VARCHAR(512). Когда для типа возвращаемого значения не указан набор символов, JSON_VALUE() использует utf8mb4 с двоичным сравнением, которое чувствительно к регистру; если utf8mb4 указан как набор символов для результата, сервер использует по умолчанию сравнение для этого набора символов, которое не чувствительно к регистру.

Если данные по указанному пути состоят из или разрешаются в JSON-литерал null, функция возвращает SQL NULL.

on_empty, если указано, определяет, как ведёт себя JSON_VALUE(), когда данные по указанному пути не найдены; этот пункт принимает одно из следующих значений:
- 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. Указание их в неправильном порядке приводит к синтаксической ошибке.

Обработка ошибок. В общем случае ошибки обрабатываются JSON_VALUE() следующим образом:
- Все JSON-входы (документ и путь) проверяются на корректность. Если любой из них некорректен, SQL-ошибка генерируется без запуска пункта ON ERROR.
- ON ERROR срабатывает всякий раз, когда происходит одно из следующих событий:
  - Попытка извлечения объекта или массива, например, в результате пути, который разрешается на несколько местоположений в JSON-документе
  - Ошибки преобразования, такие как попытка преобразовать 'asdf' в значение UNSIGNED
  - Обрезка значений
- Ошибка преобразования всегда вызывает предупреждение, даже если указаны NULL ON ERROR или DEFAULT ... ON ERROR.
- Пункт ON EMPTY срабатывает, когда исходный JSON-документ (expr) не содержит данных в указанном месте (path).
Примеры. Два простых примера показаны здесь:
```
mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
+--------------------------------------------------------------+
| Joe                                                          |
+--------------------------------------------------------------+

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
    -> RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
```
За исключением случаев, когда JSON_VALUE() возвращает NULL, оператор SELECT JSON_VALUE(json_doc, path RETURNING type) эквивалентен следующему оператору:
```
SELECT CAST(
    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
    AS type
);
```
JSON_VALUE() упрощает создание индексов для столбцов JSON, делая его ненужным во многих случаях создавать генерируемый столбец и затем индекс для генерируемого столбца. Это можно сделать при создании таблицы t1, которая имеет столбец JSON, создав индекс для выражения, использующего JSON_VALUE(), действующего над этим столбцом (с путем, соответствующим значению в этом столбце), как показано здесь:
```
CREATE TABLE t1(
    j JSON,
    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);
```
Следующий вывод EXPLAIN показывает, что запрос к t1, использующий выражение индекса в пункте WHERE, использует созданный индекс:
```
mysql> EXPLAIN SELECT * FROM t1
    ->     WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
```
Это достигает почти того же эффекта, что и создание таблицы t2 с индексом на генерируемом столбце (см. Индексирование генерируемого столбца для предоставления индекса столбца JSON), как этот:
```
CREATE TABLE t2 (
    j JSON,
    g INT GENERATED ALWAYS AS (j->"$.id"),
    INDEX i1 (g)
);
```
Вывод EXPLAIN для запроса к этой таблице, ссылающегося на генерируемый столбец, показывает, что индекс используется таким же образом, как и для предыдущего запроса к таблице t1:
```
mysql> EXPLAIN SELECT * FROM t2 WHERE g  = 123\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
```
Сведения об использовании индексов на генерируемых столбцах для косвенного индексирования столбцов JSON см. в разделе Индексирование генерируемого столбца для предоставления индекса столбца JSON.

value MEMBER OF(json_array)

Возвращает true (1), если value является элементом json_array, в противном случае возвращает false (0). value должен быть скаляром или JSON-документом; если это скаляр, оператор пытается обработать его как элемент JSON-массива. Если value или json_array — NULL, функция возвращает NULL.

Запросы, использующие MEMBER OF() для столбцов JSON таблиц InnoDB в пункте WHERE, могут быть оптимизированы с помощью индексов с множественными значениями. Подробную информацию и примеры см. в разделе Индексы с множественными значениями.

Простые скаляры обрабатываются как значения массивов, как показано здесь:

mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
+-------------------------------------------+
| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+
| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

Частичные совпадения значений элементов массива не соответствуют:

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

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

Преобразования к строковым типам и из них не выполняются:

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

Чтобы использовать этот оператор со значением, которое само по себе является массивом, необходимо явно привести его к типу JSON-массива. Это можно сделать с помощью CAST(... AS JSON):

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

Также возможно выполнить необходимое приведение с помощью функции JSON_ARRAY(), как показано здесь:

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

Любые JSON-объекты, используемые в качестве значений для проверки или которые появляются в целевом массиве, должны быть приведены к правильному типу с помощью CAST(... AS JSON) или JSON_OBJECT(). Кроме того, целевой массив, содержащий JSON-объекты, сам должен быть приведен к типу с помощью JSON_ARRAY. Это показано в следующей последовательности операторов:

mysql> SET @a = CAST('{"a":1}' AS JSON);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = JSON_OBJECT("b", 2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
+------------------+------------------+
| @a MEMBER OF(@c) | @b MEMBER OF(@c) |
+------------------+------------------+
|                1 |                1 |
+------------------+------------------+
1 row in set (0.00 sec)