Массив JSON содержит список значений, разделенных запятыми и заключенных в символы [ и ]:

["abc", 10, null, true, false]

Объект JSON содержит набор пар ключ-значение, разделенных запятыми и заключенных в символы { и }:

{"k1": "value", "k2": 10}

Как показывают примеры, массивы и объекты JSON могут содержать скалярные значения, которые являются строками или числами, литерал JSON null или логические литералы JSON true или false. Ключи в объектах JSON должны быть строками. Также допускаются скалярные временные (дата, время или дата и время) значения:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

Вложенность разрешена внутри элементов массива JSON и значений ключей объектов JSON:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

Вы также можете получить значения JSON с помощью ряда функций MySQL для этой цели (см. Раздел 12.17.2, «Функции для создания значений JSON»), а также путем приведения значений других типов к типу JSON с помощью CAST(value AS JSON) (см. Преобразование между значениями JSON и не-JSON). Следующие несколько абзацев описывают, как MySQL обрабатывает значения JSON, предоставленные в качестве входных данных.

В MySQL значения JSON записываются как строки. MySQL анализирует любую строку, используемую в контексте, требующем значения JSON, и выдает ошибку, если она не является допустимым значением JSON. Эти контексты включают вставку значения в столбец, имеющий тип данных JSON, и передачу аргумента функции, которая ожидает значение JSON (обычно показано как json_doc или json_val в документации по функциям MySQL JSON), как демонстрируют следующие примеры:

MySQL обрабатывает строки, используемые в контексте JSON, с помощью набора символов utf8mb4 и сортировки utf8mb4_bin. Строки в других наборах символов преобразуются в utf8mb4 по мере необходимости. (Для строк в наборах символов ascii или utf8 преобразование не требуется, потому что ascii и utf8 являются подмножествами utf8mb4.)

В качестве альтернативы записи значений JSON с помощью литералов строк существуют функции для составления значений JSON из составных элементов. JSON_ARRAY() принимает (возможно, пустой) список значений и возвращает массив JSON, содержащий эти значения:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() принимает (возможно, пустой) список пар ключ-значение и возвращает объект JSON, содержащий эти пары:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE() принимает два или более документов JSON и возвращает объединенный результат:

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

Сведения о правилах объединения см. в Нормализация, объединение и автоматическое обертывание значений JSON.

Значения JSON могут быть назначены пользовательским переменным:

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

Однако пользовательские переменные не могут иметь тип данных JSON, поэтому, хотя @j в приведенном выше примере выглядит как значение JSON и имеет тот же набор символов и сортировку, что и значение JSON, оно не имеет тип данных JSON. Вместо этого результат функции JSON_OBJECT() преобразуется в строку при присваивании переменной.

Строки, полученные при преобразовании значений JSON, имеют набор символов utf8mb4 и сортировку utf8mb4_bin:

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

Поскольку utf8mb4_bin является двоичной сортировкой, сравнение значений JSON зависит от регистра.

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

Чувствительность к регистру также применяется к литералам JSON null, true и false, которые всегда должны быть написаны строчными буквами:

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

Чувствительность к регистру JSON-литералов отличается от чувствительности к регистру SQL-литералов NULL, TRUE и FALSE, которые могут быть написаны в любом регистре:

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

Иногда может потребоваться или быть желательным вставить символы кавычек (" или ') в документ JSON. Предположим для этого примера, что вы хотите вставить некоторые объекты JSON, содержащие строки, представляющие предложения, которые утверждают некоторые факты о MySQL, каждый сопоставленный с соответствующим ключевым словом, в таблицу, созданную с помощью SQL-запроса, показанного здесь:

mysql> CREATE TABLE facts (sentence JSON);

Среди этих пар ключевое слово-предложение — это:

mascot: The MySQL mascot is a dolphin named "Sakila".

Один из способов вставки этого как объекта JSON в таблицу facts — использование функции MySQL JSON_OBJECT(). В этом случае вы должны экранировать каждый символ кавычек с помощью обратной косой черты, как показано здесь:

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

Это не работает так же, если вы вставляете значение как литерал объекта JSON, в этом случае вам необходимо использовать двойную последовательность экранирования обратной косой черты, как показано здесь:

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

Использование двойной обратной косой черты предотвращает обработку последовательности экранирования MySQL и вместо этого заставляет ее передавать строковый литерал в движок хранения для обработки. После вставки объекта JSON любым из только что показанных способов вы можете увидеть, что обратные косые черты присутствуют в значении столбца JSON, выполнив простой запрос SELECT, как показано здесь:

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

Чтобы найти это конкретное предложение, используя mascot в качестве ключа, вы можете использовать оператор пути столбца ->, как показано здесь:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

Это оставляет обратные косые черты нетронутыми, вместе с окружающими кавычками. Чтобы отобразить нужное значение, используя mascot в качестве ключа, но без включения окружающих кавычек или каких-либо экранирований, используйте оператор пути в строке ->>, как показано здесь:

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

mysql> INSERT INTO facts VALUES
     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

При парсинге строки, которая оказывается корректным документом JSON, также выполняется нормализация: члены с ключами, дублирующими ключ, найденный ранее в документе, отбрасываются (даже если значения отличаются). Значение объекта, произведённое следующим вызовом JSON_OBJECT(), не включает второй key1 элемент, поскольку это имя ключа встречается ранее в значении:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"}                           |
+------------------------------------------------------+

MySQL также отбрасывает лишние пробелы между ключами, значениями или элементами в исходном документе JSON и оставляет (или вставляет, когда это необходимо) один пробел после каждой запятой (,) или двоеточия (:) при отображении. Это делается для повышения читаемости.

Функции MySQL, создающие значения JSON (см. Раздел 12.17.2, «Функции, создающие значения JSON»), всегда возвращают нормализованные значения.

Для повышения эффективности поиска также сортируются ключи объекта JSON. Следует учитывать, что результат этой сортировки может меняться и не гарантируется, что он будет согласован в разных релизах.

Объединение значений JSON

В контекстах, объединяющих несколько массивов, массивы объединяются в один массив путём добавления массивов, указанных позже, в конец первого массива. В следующем примере JSON_MERGE() объединяет свои аргументы в один массив:

mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false]                       |
+-----------------------------------------------------+

Нормализация также выполняется при вставке значений в столбцы JSON, как показано здесь:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+-----------+
| c1        |
+-----------+
| {"x": 17} |
| {"x": 17} |
+-----------+

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

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

Немассивовые значения, используемые в контексте, требующем значение массива, автоматически оборачиваются: значение помещается в [ и ] символы, чтобы преобразовать его в массив. В следующем выражении каждый аргумент автоматически оборачивается в массив ([1], [2]). Затем они объединяются для создания одного результирующего массива:

mysql> SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2]               |
+----------------------+

Значения массивов и объектов объединяются путём автоматического обертывания объекта в массив и объединения двух массивов:

mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}]                 |
+------------------------------------------------+

Выражение пути JSON выбирает значение внутри документа JSON.

Выражения путей полезны для функций, которые извлекают части или изменяют документ JSON, чтобы указать, где в этом документе нужно выполнить операцию. Например, следующее выражение извлекает из документа JSON значение элемента с ключом name:

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Синтаксис пути использует ведущий символ $ для представления рассматриваемого документа JSON, за которым необязательно следуют селекторы, указывающие последовательно более специфичные части документа:

Пусть $ относится к этому массиву JSON с тремя элементами:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

Тогда:

Поскольку $[1] и $[2] возвращают нескалярные значения, они могут быть использованы как основа для более специфичных выражений пути, выбирающих вложенные значения. Примеры:

Как упоминалось ранее, компоненты пути, которые называют ключи, должны быть заключены в кавычки, если имя ключа без кавычек не допустимо в выражениях пути. Пусть $ относится к этому значению:

{"a fish": "shark", "a bird": "sparrow"}

Ключи оба содержат пробел и должны быть заключены в кавычки:

Пути, использующие подстановочные знаки, возвращают массив, который может содержать несколько значений:

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

В следующем примере путь $**.b возвращает несколько путей ($.a.b и $.c.b) и генерирует массив соответствующих значений пути:

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

В MySQL 5.7.9 и более поздних версиях вы можете использовать column->path с идентификатором столбца JSON и выражением пути JSON как синоним для JSON_EXTRACT(column, path). Дополнительную информацию см. в разделе Раздел 12.17.3, «Функции поиска значений JSON». См. также Индексирование генерируемого столбца для создания индекса столбца JSON.

Некоторые функции принимают существующий документ JSON, изменяют его каким-либо образом и возвращают измененный документ. Выражения путей указывают, где в документе нужно произвести изменения. Например, функции JSON_SET(), JSON_INSERT() и JSON_REPLACE() каждая принимает документ JSON плюс одну или несколько пар путь/значение, которые описывают, где изменить документ и какие значения использовать. Функции различаются по тому, как они обрабатывают существующие и несуществующие значения в документе.

Рассмотрим этот документ:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET() заменяет значения для существующих путей и добавляет значения для несуществующих путей:

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

В этом случае путь $[1].b[0] выбирает существующее значение (true), которое заменяется значением, следующим за аргументом пути (1). Путь $[2][2] не существует, поэтому соответствующее значение (2) добавляется к значению, выбранному по пути $[2].

JSON_INSERT() добавляет новые значения, но не заменяет существующие:

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON_REPLACE() заменяет существующие значения и игнорирует новые:

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

Пары путь/значение обрабатываются слева направо. Документ, полученный в результате обработки одной пары, становится новым значением, по отношению к которому обрабатывается следующая пара.

JSON_REMOVE() принимает документ JSON и один или несколько путей, которые указывают на значения, которые необходимо удалить из документа. Возвращаемое значение — исходный документ минус значения, выбранные путями, которые существуют в документе:

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

Пути имеют следующие эффекты:

Многие функции JSON, поддерживаемые MySQL и описанные в другом месте этого руководства (см. Раздел 12.17, «Функции JSON»), требуют выражения пути для идентификации конкретного элемента в документе JSON. Путь состоит из области действия пути, за которой следуют один или несколько отрезков пути. Для путей, используемых в функциях JSON MySQL, область действия всегда является документом, в котором выполняется поиск или выполняется операция, представленная ведущим символом $. Отрезки пути разделяются точками (.). Ячейки массивов представлены как [N], где N — неотрицательное целое число. Имена ключей должны быть строками в двойных кавычках или допустимыми идентификаторами ECMAScript (см. http://www.ecma-international.org/ecma-262/5.1/#sec-7.6). Выражения путей, как и тексты JSON, должны быть закодированы с использованием набора символов ascii, utf8 или utf8mb4. Другие кодировки символов неявно преобразуются в utf8mb4. Полный синтаксис показан здесь:

pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

Как отмечалось ранее, в MySQL область действия пути всегда является документом, над которым выполняется операция, представленная как $. Вы можете использовать '$' как синоним документа в выражениях путей JSON.

Дикий символ * и токен ** используются следующим образом:

Примеры синтаксиса путей см. в описаниях различных функций JSON, которые принимают пути в качестве аргументов, таких как JSON_CONTAINS_PATH(), JSON_SET() и JSON_REPLACE(). Примеры, включающие использование диких символов * и **, см. в описании функции JSON_SEARCH().

Значения JSON могут быть сравнены с помощью операторов =, <, <=, >, >=, <>, != и <=>.

Следующие операторы сравнения и функции пока не поддерживаются со значениями JSON:

Обходным путём для операторов сравнения и функций, перечисленных выше, является приведение значений JSON к собственному численный или строковому типу данных MySQL, чтобы они имели согласованный тип скаляра, не являющегося JSON.

Сравнение значений JSON происходит на двух уровнях. Первый уровень сравнения основан на типах JSON сравниваемых значений. Если типы отличаются, результат сравнения определяется исключительно тем, какой тип имеет более высокий приоритет. Если два значения имеют одинаковый тип JSON, происходит второй уровень сравнения, используя правила, специфичные для типа.

Следующий список показывает приоритеты типов JSON, от самого высокого к самому низкому. (Имена типов возвращаются функцией JSON_TYPE()). Типы, показанные вместе в строке, имеют одинаковый приоритет. Любое значение, имеющее тип JSON, указанный ранее в списке, сравнивается больше любого значения, имеющего тип JSON, указанный позже в списке.

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

Для значений JSON с одинаковым приоритетом правила сравнения специфичны для типа:

Для сравнения любого значения JSON со SQL NULL результат равен UNKNOWN.

Для сравнения значений JSON и не-JSON значений значение не-JSON преобразуется в JSON по правилам в следующей таблице, а затем значения сравниваются, как описано ранее.

В следующей таблице представлен сводный обзор правил, которые MySQL использует при преобразовании между JSON значениями и значениями других типов:

Сортировка ORDER BY и GROUP BY для JSON значений работает по этим принципам:

Для сортировки может быть полезно преобразовать скаляр JSON в какой-либо другой базовый тип MySQL. Например, если столбец с именем jdoc содержит JSON объекты, содержащие член с ключом id и неотрицательным значением, используйте это выражение для сортировки по значениям id:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

Если определён сгенерированный столбец, использующий то же выражение, что и в ORDER BY, оптимизатор MySQL распознаёт это и рассматривает использование индекса для плана выполнения запроса. См. Раздел 8.3.10, «Использование оптимизатором индексов сгенерированных столбцов».

При агрегации JSON значений SQL NULL значения игнорируются, как и в случае с другими типами данных. Не-NULL значения преобразуются в числовой тип и агрегируются, за исключением MIN(), MAX() и GROUP_CONCAT(). Преобразование в число должно давать осмысленный результат для JSON значений, являющихся числовыми скалярами, хотя (в зависимости от значений) может произойти усечение и потеря точности. Преобразование в число других JSON значений может не дать осмысленного результата.