Последнее обновление
JSON
Введение в JSON
JSON (JavaScript Object Notation) — открытый стандарт формата файлов и обмена данными, использующий понятный человеку текст для хранения и передачи данных. Согласно официальной спецификации RFC7159, JSON поддерживает следующие базовые типы:
- Bool
- Null
- Number
- String
- Array
- Object
Тип данных JSON эффективно хранит данные JSON в двоичном формате и позволяет обращаться к внутренним полям с помощью функций JSON.
По умолчанию поддерживается размер до 1048576 байт (1MB), который можно увеличить до 2147483643 байт (2GB). Это ограничение настраивается с помощью параметра string_type_length_soft_limit_bytes.
По сравнению с хранением строк JSON в обычном типе STRING, тип JSON имеет два основных преимущества:
- Проверка формата JSON при вставке данных.
- Более эффективный двоичный формат хранения, обеспечивающий быстрый доступ к внутренним полям JSON с помощью таких функций, как
json_extract, по сравнению с функциямиget_json_xx.
Примечание
В версии 1.2.x тип JSON назывался JSONB. Для совместимости с MySQL начиная с версии 2.0.0 он был переименован в JSON. В старых таблицах по-прежнему можно использовать прежнее имя.
Проблемы точности чисел в JSON
При использовании JSON следует уделять особое внимание точности чисел:
- В большинстве систем тип Number реализован на основе 64-разрядных чисел с плавающей точкой двойной точности по стандарту IEEE 754-2008 (например, тип double в C++)
- Поскольку спецификация JSON не определяет строго базовый тип для Number, а данные JSON передаются между системами в виде текста, возможна потеря точности
Для строки JSON вида {"abc": 18446744073709551616}:
-- Conversion result in MySQL
cast('{"abc": 18446744073709551616}' as json)
-- Result: {"abc": 1.8446744073709552e19}
// Conversion result in JavaScript
console.log(JSON.parse('{"abc": 18446744073709551616}'));
// Result: {abc: 18446744073709552000}
Чтобы сохранить точность чисел при обмене данными между системами, большие числа следует хранить в виде строк, например {"abc": "18446744073709551616"}.
Тип JSON в Doris
Doris поддерживает типы данных, соответствующие спецификации стандарта JSON, и использует эффективный формат JSONB (JSON Binary) для хранения в двоичном виде.
Поддерживаемые типы
JSONB в Doris поддерживает все стандартные типы JSON. Основное отличие заключается в том, что Doris предоставляет более детальные расширения типа Number для более точного сопоставления с внутренними типами Doris.
| Тип JSON | Подтип | Соответствующий тип Doris |
|---|---|---|
| Bool | - | BOOLEAN |
| Null | - | (Прямого аналога нет; представляет значение null в JSON) |
| Number | Int8 | TINYINT |
| Int16 | SMALLINT | |
| Int32 | INT | |
| Int64 | BIGINT | |
| Int128 | LARGEINT | |
| Double | DOUBLE | |
| Float | FLOAT | |
| Decimal | DECIMAL | |
| String | - | STRING |
| Array | - | ARRAY |
| Object | - | STRUCT |
Важные замечания:
- Значение Null:
- Null в JSON — допустимое значение, обозначающее «пустое значение». Оно отличается от NULL в SQL, которое обозначает «неизвестное» или «отсутствующее» значение.
- CAST('null' AS JSON) создаёт столбец JSONB, содержащий значение null в JSON, которое само по себе не является NULL на уровне SQL.
- CAST('null' AS JSON) IS NULL возвращает false (0), поскольку столбец содержит известное значение null в JSON, которое не является NULL в SQL.
Операции и ограничения
- Сравнение и арифметические операции:
- Столбцы JSONB нельзя напрямую сравнивать с другими типами данных (включая другие столбцы JSONB) или использовать в арифметических операциях.
- Решение: используйте функцию JSON_EXTRACT для извлечения скалярных значений (например, INT, DOUBLE, STRING, BOOLEAN) из JSONB, затем преобразуйте их в соответствующие собственные типы Doris для сравнения или вычислений.
- Сортировка:
- Столбцы JSONB не поддерживают операции ORDER BY.
- Неявное преобразование:
- Только при вводе: при вводе данных в столбец JSONB тип STRING может неявно преобразовываться в JSONB (если содержимое строки является допустимым текстом JSON). Другие типы Doris не могут неявно преобразовываться в JSONB.
Поддержка GROUP BY и DISTINCT для JSON
Пример 1: GROUP BY для столбцов JSON
mysql> SELECT * FROM test_jsonb_groupby;
+------+---------------+
| id | j |
+------+---------------+
| 1 | {"a":1,"b":2} |
| 2 | {"a":1,"b":3} |
| 3 | {"a":2,"b":2} |
| 4 | {"a":2,"b":2} |
| 5 | {"a":1,"b":2} |
| 6 | {"a":2,"b":2} |
+------+---------------+
6 rows in set (0.07 sec)
mysql> SELECT j, COUNT(*) FROM test_jsonb_groupby GROUP BY j;
+---------------+----------+
| j | COUNT(*) |
+---------------+----------+
| {"a":1,"b":3} | 1 |
| {"a":2,"b":2} | 3 |
| {"a":1,"b":2} | 2 |
+---------------+----------+
Пример 2: запрос DISTINCT для столбцов JSON
mysql> SELECT DISTINCT j FROM test_jsonb_groupby;
+---------------+
| j |
+---------------+
| {"a":1,"b":3} |
| {"a":2,"b":2} |
| {"a":1,"b":2} |
+---------------+
Примечания
-
Двоичное сравнение: сравнение JSON выполняется на уровне двоичного представления. Если два значения JSON семантически идентичны, но имеют разные двоичные представления, они не могут быть объединены в одну группу. Например:
mysql> SELECT * FROM test_jsonb;
+------+------+
| id | j |
+------+------+
| 1 | 123 |
| 2 | 123 |
+------+------+
mysql> SELECT j, COUNT(*) FROM test_jsonb GROUP BY j;
+------+----------+
| j | COUNT(*) |
+------+----------+
| 123 | 1 |
| 123 | 1 |
+------+----------+Это происходит потому, что первое
123хранится внутри как 64-разрядное целое число (json_typeвозвращаетbigint), а второе123— как целое число меньшей разрядности (json_typeвозвращаетintдля всех целочисленных типов с разрядностью меньше, чем уbigint), что приводит к разным двоичным представлениям. Проверить их типы можно следующим запросом:mysql> SELECT j, json_type(j, '$') FROM test_jsonb;
+------+------------------+
| j | json_type(j, '$') |
+------+------------------+
| 123 | bigint |
| 123 | int |
+------+------------------+Аналогично, объекты JSON с разным порядком ключей не могут быть объединены в одну группу. Например:
mysql> SELECT * FROM test_jsonb;
+------+---------------+
| id | j |
+------+---------------+
| 2 | {"b":2,"a":1} |
| 1 | {"a":1,"b":2} |
+------+---------------+
mysql> SELECT j, COUNT(*) FROM test_jsonb GROUP BY j;
+---------------+----------+
| j | COUNT(*) |
+---------------+----------+
| {"b":2,"a":1} | 1 |
| {"a":1,"b":2} | 1 |
+---------------+----------+ -
Единообразие числовых типов: чтобы не учитывать различия между числовыми типами, используйте функцию
NORMALIZE_JSON_NUMBERS_TO_DOUBLEдля преобразования всех чисел в JSON в типDOUBLE:mysql> SELECT NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j), COUNT(*)
FROM test_jsonb
GROUP BY NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j);
+-------------------------------------+----------+
| NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j) | COUNT(*) |
+-------------------------------------+----------+
| 123 | 2 |
+-------------------------------------+----------+При создании объектов JSON путём разбора текста (например, при преобразовании строк в JSON с помощью CAST) Doris автоматически выбирает подходящий числовой тип для хранения, поэтому различия между числовыми типами обычно не создают проблем.
-
Единообразие порядка ключей: Используйте функцию
SORT_JSON_OBJECT_KEYSдля сортировки ключей:mysql> SELECT SORT_JSON_OBJECT_KEYS(j), COUNT(*)
FROM test_jsonb
GROUP BY SORT_JSON_OBJECT_KEYS(j);
+--------------------------+----------+
| SORT_JSON_OBJECT_KEYS(j) | COUNT(*) |
+--------------------------+----------+
| {"a":1,"b":2} | 2 |
+--------------------------+----------+При создании объектов JSON путём разбора текста (например, при преобразовании строк в JSON с помощью CAST) Doris сохраняет порядок ключей из текста. Поэтому, если данные JSONB создаются путём разбора текста, вы не столкнётесь с описанными выше проблемами группировки.
Рекомендации
Если вы не можете обеспечить единообразие числовых типов или порядка ключей в данных JSON, рекомендуется предварительно обработать данные функциями NORMALIZE_JSON_NUMBERS_TO_DOUBLE и SORT_JSON_OBJECT_KEYS перед выполнением операций GROUP BY, чтобы получить ожидаемые результаты.
Синтаксис
Определение:
json_column_name JSON
Вставка:
- С помощью
INSERT INTO VALUES, где данные передаются в виде строки в кавычках. Например:
INSERT INTO table_name(id, json_column_name) VALUES (1, '{"k1": "100"}')
- Для STREAM LOAD данные соответствующего столбца передаются в виде строки без дополнительных кавычек. Например:
12 {"k1":"v31", "k2": 300}
13 []
14 [123, 456]
- Если в JSON встречаются специальные символы с
'\', такие как'\r','\t', необходимо с помощью функции replace заменить"\"на"\\", например заменить"\n"на"\\n"
Запрос:
- Выбор всего столбца JSON напрямую:
SELECT json_column_name FROM table_name;
- Извлечение определённых полей или другой информации из JSON с помощью функций JSON. Например:
SELECT json_extract(json_column_name, '$.k1') FROM table_name;
- Тип JSON можно преобразовывать в целочисленные типы, строки, BOOLEAN, ARRAY и MAP, а также выполнять обратное преобразование. Например:
SELECT CAST('{"k1": "100"}' AS JSON);
SELECT CAST(json_column_name AS STRING) FROM table_name;
SELECT CAST(json_extract(json_column_name, '$.k1') AS INT) FROM table_name;
подсказка
В настоящее время тип JSON нельзя использовать в ORDER BY или операциях сравнения.
Ввод JSON
Для преобразования строки, соответствующей синтаксису JSON, в JSONB используйте CAST.
-- Simple scalar/basic values (numeric types, bool, null, string)
mysql> SELECT cast('5' as json);
+-------------------+
| cast('5' as json) |
+-------------------+
| 5 |
+-------------------+
-- Arrays with zero or more elements (elements don't need to be the same type)
mysql> SELECT cast('[1, 2, "foo", null]' as json);
+-------------------------------------+
| cast('[1, 2, "foo", null]' as json) |
+-------------------------------------+
| [1,2,"foo",null] |
+-------------------------------------+
-- Objects containing key-value pairs
-- Note that object keys must always be quoted strings
mysql> SELECT cast('{"bar": "baz", "balance": 7.77, "active": false}' as json);
+------------------------------------------------------------------+
| cast('{"bar": "baz", "balance": 7.77, "active": false}' as json) |
+------------------------------------------------------------------+
| {"bar":"baz","balance":7.77,"active":false} |
+------------------------------------------------------------------+
-- Arrays and objects can be nested arbitrarily
mysql> SELECT cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json);
+---------------------------------------------------------------------+
| cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json) |
+---------------------------------------------------------------------+
| {"foo":[true,"bar"],"tags":{"a":1,"b":null}} |
+---------------------------------------------------------------------+
JSONB в Doris не сохраняет семантически незначимые детали, такие как пробелы.
mysql> -- The input text and JSON output may not look the same
mysql> SELECT cast('[1, 2]' as json);
+----------------------------------------+
| cast('[1, 2]' as json) |
+----------------------------------------+
| [1,2] |
+----------------------------------------+
Основные отличия и замечания:
- CAST(string AS JSON): используется для разбора строк, соответствующих синтаксису JSON. Для типа Number распознаются только типы Int8, Int16, Int32, Int64, Int128 и Double, но не Decimal.
- В отличие от большинства других реализаций JSON, тип JSONB в Doris поддерживает точность вплоть до Int128. Числа, превышающие точность Int128, могут вызвать переполнение.
- Если входная строка с числом имеет вид 12.34, она будет разобрана как Double; если десятичной точки нет, она будет разобрана как целое число (если значение выходит за диапазон Int128, оно будет преобразовано в Double с потерей точности)
Преобразование внутренних типов Doris в JSONB с помощью to_json
mysql> SELECT to_json(1) , to_json(3.14) , to_json("12345");
+------------+---------------+------------------+
| to_json(1) | to_json(3.14) | to_json("12345") |
+------------+---------------+------------------+
| 1 | 3.14 | "12345" |
+------------+---------------+------------------+
mysql> SELECT to_json(array(array(1,2,3),array(4,5,6)));
+-------------------------------------------+
| to_json(array(array(1,2,3),array(4,5,6))) |
+-------------------------------------------+
| [[1,2,3],[4,5,6]] |
+-------------------------------------------+
mysql> SELECT json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]');
+----------------------------------------------------------------------+
| json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]') |
+----------------------------------------------------------------------+
| 6 |
+----------------------------------------------------------------------+
mysql> SELECT to_json(struct(123,array(4,5,6),"789"));
+------------------------------------------+
| to_json(struct(123,array(4,5,6),"789")) |
+------------------------------------------+
| {"col1":123,"col2":[4,5,6],"col3":"789"} |
+------------------------------------------+
mysql> SELECT json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2");
+----------------------------------------------------------------+
| json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2") |
+----------------------------------------------------------------+
| [4,5,6] |
+----------------------------------------------------------------+
to_json поддерживает преобразование только тех типов Doris, которые сопоставляются с типами JSONB. Например, DECIMAL можно использовать с to_json. Однако DATE использовать нельзя: сначала его нужно преобразовать в STRING, а затем применить to_json.
Вывод JSONB
При преобразовании в обычный текст для взаимодействия с другими системами тип JSONB в Doris гарантирует формирование корректного текста JSON:
- Значения null:
- Выводятся как null (без кавычек)
- Логические значения:
- true → выводится true
- false → выводится false
- Числовые типы:
- Все числовые значения выводятся напрямую
- Пример: 5 → выводится 5, 3.14 → выводится 3.14
- Строки:
- Выводятся в двойных кавычках: "
" - Специальные символы экранируются:
- " → "
- \ → \
- / → /
- Возврат на одну позицию → \b
- Перевод страницы → \f
- Перевод строки → \n
- Возврат каретки → \r
- Табуляция → \t
- Другие управляющие символы (ASCII < 32) преобразуются в escape-последовательности Unicode: \uXXXX
- Выводятся в двойных кавычках: "
- Объекты:
- Формат: {
} - Формат пары «ключ — значение»: "
": - Несколько пар «ключ — значение» разделяются запятыми
- Формат: {
- Массивы:
- Формат: [
] - Несколько элементов разделяются запятыми
- Формат: [
- Обработка вложенных структур:
- Объекты и массивы поддерживают неограниченное число уровней вложенности
- Каждый уровень вложенности обрабатывается рекурсивно по тем же правилам
Проблемы точности чисел
При преобразовании внутренних типов Doris в JSONB с помощью to_json потери точности не происходит. При использовании внутренних функций JSON в Doris, если возвращаемое значение также имеет тип JSONB, потери точности не происходит. Однако преобразование JSONB в Doris в обычный текст и затем обратно в JSONB может привести к потере точности.
Пример: объект типа JSON в Doris
Object{
"a": (Decimal 18446744073709551616.123)
}
После преобразования в обычный текст:
{"a": 18446744073709551616.123}
После обратного преобразования обычного текста в тип JSON в Doris:
Object{
"a": (Double 18446744073709552000) // precision loss
}
Настройка и ограничения
- По умолчанию JSON поддерживает размер 1,048,576 байт (1 MB)
- Ограничение размера можно изменить с помощью параметра конфигурации BE string_type_length_soft_limit_bytes
- Максимально допустимое значение — 2,147,483,643 байт (приблизительно 2 GB)
- В объектах типа JSON в Doris длина ключа не может превышать 255 байт
- JSON поддерживает максимальную глубину вложенности 100 уровней
Пример использования
Руководство по типу данных JSON: создание таблицы, загрузка данных и выполнение запросов.
Создание базы данных и таблицы
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE test_json (
id INT,
j JSON
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES("replication_num" = "1");
Загрузка данных
Тестовые данные test_json.csv для Stream Load
- Данные содержат 2 столбца: 1-й столбец — id, 2-й — строка JSON
- Данные содержат 25 строк: первые 18 строк содержат корректный JSON, последние 7 — некорректный
1 \N
2 null
3 true
4 false
5 100
6 10000
7 1000000000
8 1152921504606846976
9 6.18
10 "abcd"
11 {}
12 {"k1":"v31", "k2": 300}
13 []
14 [123, 456]
15 ["abc", "def"]
16 [null, true, false, 100, 6.18, "abc"]
17 [{"k1":"v41", "k2": 400}, 1, "a", 3.14]
18 {"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
19 ''
20 'abc'
21 abc
22 100x
23 6.a8
24 {x
25 [123, abc]
- Поскольку 28% строк некорректны, Stream Load с настройками по умолчанию завершится с сообщением об ошибке "too many filtered rows"
curl --location-trusted -u root: -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
{
"TxnId": 12019,
"Label": "744d9821-9c9f-43dc-bf3b-7ab048f14e32",
"TwoPhaseCommit": "false",
"Status": "Fail",
"Message": "too many filtered rows",
"NumberTotalRows": 25,
"NumberLoadedRows": 18,
"NumberFilteredRows": 7,
"NumberUnselectedRows": 0,
"LoadBytes": 380,
"LoadTimeMs": 48,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 1,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 45,
"CommitAndPublishTimeMs": 0,
"ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_2/error_log_insert_stmt_95435c4bf5f156df-426735082a9296af_95435c4bf5f156df_426735082a9296af"
}
- После установки заголовка 'max_filter_ratio: 0.3' загрузка Stream Load завершится успешно
curl --location-trusted -u root: -H 'max_filter_ratio: 0.3' -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
{
"TxnId": 12017,
"Label": "f37a50c1-43e9-4f4e-a159-a3db6abe2579",
"TwoPhaseCommit": "false",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 25,
"NumberLoadedRows": 18,
"NumberFilteredRows": 7,
"NumberUnselectedRows": 0,
"LoadBytes": 380,
"LoadTimeMs": 68,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 2,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 45,
"CommitAndPublishTimeMs": 19,
"ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_0/error_log_insert_stmt_a1463f98a7b15caf-c79399b920f5bfa3_a1463f98a7b15caf_c79399b920f5bfa3"
}
- Используйте SELECT для просмотра данных, загруженных с помощью Stream Load. Столбец типа JSON отображается в виде обычной строки JSON.
mysql> SELECT * FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+
| id | j |
+------+---------------------------------------------------------------+
| 1 | NULL |
| 2 | null |
| 3 | true |
| 4 | false |
| 5 | 100 |
| 6 | 10000 |
| 7 | 1000000000 |
| 8 | 1152921504606846976 |
| 9 | 6.18 |
| 10 | "abcd" |
| 11 | {} |
| 12 | {"k1":"v31","k2":300} |
| 13 | [] |
| 14 | [123,456] |
| 15 | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
+------+---------------------------------------------------------------+
18 rows in set (0.03 sec)
Запись данных с помощью insert into
- После вставки 1 строки общее число строк увеличивается с 18 до 19
mysql> INSERT INTO test_json VALUES(26, '{"k1":"v1", "k2": 200}');
Query OK, 1 row affected (0.09 sec)
{'label':'insert_4ece6769d1b42fd_ac9f25b3b8f3dc02', 'status':'VISIBLE', 'txnId':'12016'}
mysql> SELECT * FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+
| id | j |
+------+---------------------------------------------------------------+
| 1 | NULL |
| 2 | null |
| 3 | true |
| 4 | false |
| 5 | 100 |
| 6 | 10000 |
| 7 | 1000000000 |
| 8 | 1152921504606846976 |
| 9 | 6.18 |
| 10 | "abcd" |
| 11 | {} |
| 12 | {"k1":"v31","k2":300} |
| 13 | [] |
| 14 | [123,456] |
| 15 | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+
19 rows in set (0.03 sec)
Запросы
Извлечение полей из JSON с помощью функций json_extract
- Извлечение всего JSON; '$' обозначает корень в пути JSON
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| id | j | json_extract(`j`, '$') |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | true |
| 4 | false | false |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | "abcd" |
| 11 | {} | {} |
| 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
| 13 | [] | [] |
| 14 | [123,456] | [123,456] |
| 15 | ["abc","def"] | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
19 rows in set (0.03 sec)
- Извлечение поля k1; если оно отсутствует, возвращается NULL
mysql> SELECT id, j, json_extract(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------------+
| id | j | json_extract(`j`, '$.k1') |
+------+---------------------------------------------------------------+----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | "v31" |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | "v31" |
| 26 | {"k1":"v1","k2":200} | "v1" |
+------+---------------------------------------------------------------+----------------------------+
19 rows in set (0.03 sec)
- Извлечение элемента 0 массива верхнего уровня
mysql> SELECT id, j, json_extract(j, '$[0]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------------+
| id | j | json_extract(`j`, '$[0]') |
+------+---------------------------------------------------------------+----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | 123 |
| 15 | ["abc","def"] | "abc" |
| 16 | [null,true,false,100,6.18,"abc"] | null |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | {"k1":"v41","k2":400} |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+----------------------------+
19 rows in set (0.03 sec)
- Извлечение всего массива JSON с именем a1
mysql> SELECT id, j, json_extract(j, '$.a1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+------------------------------------+
| id | j | json_extract(`j`, '$.a1') |
+------+---------------------------------------------------------------+------------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | [{"k1":"v41","k2":400},1,"a",3.14] |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+------------------------------------+
19 rows in set (0.02 sec)
- Извлечение вложенного поля из объекта в массиве
mysql> SELECT id, j, json_extract(j, '$.a1[0]'), json_extract(j, '$.a1[0].k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
| id | j | json_extract(`j`, '$.a1[0]') | json_extract(`j`, '$.a1[0].k1') |
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
| 1 | NULL | NULL | NULL |
| 2 | null | NULL | NULL |
| 3 | true | NULL | NULL |
| 4 | false | NULL | NULL |
| 5 | 100 | NULL | NULL |
| 6 | 10000 | NULL | NULL |
| 7 | 1000000000 | NULL | NULL |
| 8 | 1152921504606846976 | NULL | NULL |
| 9 | 6.18 | NULL | NULL |
| 10 | "abcd" | NULL | NULL |
| 11 | {} | NULL | NULL |
| 12 | {"k1":"v31","k2":300} | NULL | NULL |
| 13 | [] | NULL | NULL |
| 14 | [123,456] | NULL | NULL |
| 15 | ["abc","def"] | NULL | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v41","k2":400} | "v41" |
| 26 | {"k1":"v1","k2":200} | NULL | NULL |
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
19 rows in set (0.02 sec)
- Извлечение поля с определённым типом данных
- json_extract_string извлекает поле как строку; если поле имеет другой тип, его значение преобразуется в строку
mysql> SELECT id, j, json_extract_string(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| id | j | json_extract_string(`j`, '$') |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | true |
| 4 | false | false |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | abcd |
| 11 | {} | {} |
| 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
| 13 | [] | [] |
| 14 | [123,456] | [123,456] |
| 15 | ["abc","def"] | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_string(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_string(`j`, '$.k1') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | v31 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | v31 |
| 26 | {"k1":"v1","k2":200} | v1 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.03 sec)
- json_extract_int извлекает поле типа int; если поле имеет другой тип, возвращается NULL
mysql> SELECT id, j, json_extract_int(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------+
| id | j | json_extract_int(`j`, '$') |
+------+---------------------------------------------------------------+-----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+-----------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_int(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_int(`j`, '$.k2') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
- json_extract_bigint извлекает поле типа bigint; если поле имеет другой тип, возвращается NULL
mysql> SELECT id, j, json_extract_bigint(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_bigint(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
mysql> SELECT id, j, json_extract_bigint(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_bigint(`j`, '$.k2') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.02 sec)
- json_extract_double извлекает поле типа double; если поле имеет другой тип, возвращается NULL
mysql> SELECT id, j, json_extract_double(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_double(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1.152921504606847e+18 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_double(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_double(`j`, '$.k2') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.03 sec)
- json_extract_bool извлекает поле логического типа; если поле имеет другой тип, возвращается NULL
mysql> SELECT id, j, json_extract_bool(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+------------------------------+
| id | j | json_extract_bool(`j`, '$') |
+------+---------------------------------------------------------------+------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | 1 |
| 4 | false | 0 |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+------------------------------+
19 rows in set (0.01 sec)
mysql> SELECT id, j, json_extract_bool(j, '$[1]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+---------------------------------+
| id | j | json_extract_bool(`j`, '$[1]') |
+------+---------------------------------------------------------------+---------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+---------------------------------+
19 rows in set (0.01 sec)
- json_extract_isnull проверяет, имеет ли поле тип null в JSON; возвращает 1, если поле содержит null в JSON, иначе 0
- null в JSON отличается от NULL в SQL. NULL в SQL означает отсутствие значения у поля, а null в JSON означает, что поле содержит специальное значение null.
mysql> SELECT id, j, json_extract_isnull(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_isnull(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 1 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 0 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 0 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 0 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 0 |
| 26 | {"k1":"v1","k2":200} | 0 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
Проверка наличия поля в JSON с помощью json_exists_path
mysql> SELECT id, j, json_exists_path(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------+
| id | j | json_exists_path(`j`, '$') |
+------+---------------------------------------------------------------+-----------------------------+
| 1 | NULL | NULL |
| 2 | null | 1 |
| 3 | true | 1 |
| 4 | false | 1 |
| 5 | 100 | 1 |
| 6 | 10000 | 1 |
| 7 | 1000000000 | 1 |
| 8 | 1152921504606846976 | 1 |
| 9 | 6.18 | 1 |
| 10 | "abcd" | 1 |
| 11 | {} | 1 |
| 12 | {"k1":"v31","k2":300} | 1 |
| 13 | [] | 1 |
| 14 | [123,456] | 1 |
| 15 | ["abc","def"] | 1 |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 1 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 1 |
| 26 | {"k1":"v1","k2":200} | 1 |
+------+---------------------------------------------------------------+-----------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_exists_path(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_exists_path(`j`, '$.k1') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 0 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 1 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 0 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 0 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 1 |
| 26 | {"k1":"v1","k2":200} | 1 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
mysql> SELECT id, j, json_exists_path(j, '$[2]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_exists_path(`j`, '$[2]') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 0 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 0 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 1 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 0 |
| 26 | {"k1":"v1","k2":200} | 0 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.02 sec)
Получение типа данных поля в JSON с помощью json_type
- Возвращает тип данных поля, указанного путём JSON, или NULL, если поле отсутствует.
mysql> SELECT id, j, json_type(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------+
| id | j | json_type(`j`, '$') |
+------+---------------------------------------------------------------+----------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | bool |
| 4 | false | bool |
| 5 | 100 | int |
| 6 | 10000 | int |
| 7 | 1000000000 | int |
| 8 | 1152921504606846976 | bigint |
| 9 | 6.18 | double |
| 10 | "abcd" | string |
| 11 | {} | object |
| 12 | {"k1":"v31","k2":300} | object |
| 13 | [] | array |
| 14 | [123,456] | array |
| 15 | ["abc","def"] | array |
| 16 | [null,true,false,100,6.18,"abc"] | array |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | array |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | object |
| 26 | {"k1":"v1","k2":200} | object |
+------+---------------------------------------------------------------+----------------------+
19 rows in set (0.02 sec)
mysql> select id, j, json_type(j, '$.k1') from test_json order by id;
+------+---------------------------------------------------------------+-------------------------+
| id | j | json_type(`j`, '$.k1') |
+------+---------------------------------------------------------------+-------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | string |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | string |
| 26 | {"k1":"v1","k2":200} | string |
+------+---------------------------------------------------------------+-------------------------+
19 rows in set (0.03 sec)
Часто задаваемые вопросы
- Есть ли разница между null в JSON и NULL в SQL (то есть при проверке IS NULL)?
Да, разница есть. В JSON значение null (например, {"key1": null}) означает, что ключ key1 существует и его значение явно задано как null. Это специальный тип, который кодируется в двоичном представлении JSON.
В отличие от него, NULL в SQL (при использовании IS NULL) может указывать на то, что ключ вообще отсутствует в объекте JSON.
Например:
mysql> SELECT JSON_EXTRACT_STRING('{"key1" : null}', "$.key1") IS NULL;
+----------------------------------------------------------+
| JSON_EXTRACT_STRING('{"key1" : null}', "$.key1") IS NULL |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT_STRING('{"key1" : null}', "$.key_not_exist") IS NULL;
+-------------------------------------------------------------------+
| JSON_EXTRACT_STRING('{"key1" : null}', "$.key_not_exist") IS NULL |
+-------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------+
1 row in set (0.01 sec)
- В чём разница между функциями
GET_JSON_XXXиJSON_EXTRACT_XXXи как выбрать подходящие?
Функции GET_JSON_XXX предназначены для работы со строковыми типами: они извлекают значения непосредственно из исходных строк JSON. Функции JSON_EXTRACT_XXX реализованы специально для типа данных JSON и оптимизированы для него.
Ключевые слова
JSONB, JSON, json_parse, json_parse_error_to_null, json_parse_error_to_value, json_extract, json_extract_isnull, json_extract_bool, json_extract_int, json_extract_bigint, json_extract_double, json_extract_string, json_exists_path, json_type