Тип JSON

  • JSON

Описание

Тип JSON используется для хранения данных JSON согласно стандартному синтаксису, описанному в спецификации.

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

Кавычки внутри текста в формате JSON должны быть двойными. А обрамляющие этот текст кавычки внутри выражения INSERT должны быть одинарными (см. пример ниже).

Примеры

Создадим таблицу js_table и вставим в столбец js_data данные в формате JSON из этого примера:

CREATE TABLE js_table(name VARCHAR, js_data JSON);

INSERT INTO js_table VALUES
    ('John Smith',
    '{
    "first_name": "John",
    "last_name": "Smith",
    "is_alive": true,
    "age": 27,
    "address": {
        "street_address": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postal_code": "10021-3100"
    },
    "phone_numbers": [
        {
        "type": "home",
        "number": "212 555-1234"
        },
        {
        "type": "office",
        "number": "646 555-4567"
        }
    ],
    "children": [
        "Catherine",
        "Thomas",
        "Trevor"
    ],
    "spouse": null
    }'
    );

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

SELECT
    json_keys(js_data) AS json_fields
FROM js_table;
+---------------------------------------------------------------------------+
|                                json_fields                                |
+---------------------------------------------------------------------------+
| {first_name,last_name,is_alive,age,address,phone_numbers,children,spouse} |
+---------------------------------------------------------------------------+

Выведем в отдельные столбцы полное имя из текстового столбца name, а из данных JSON возьмем возраст age и количество детей — длину массива в поле children. Для этого используем фунции json_extract и json_array_length.

SELECT
    name,
    json_extract(js_data, 'age') AS age,
    json_array_length(js_data, 'children') AS children_num
FROM js_table;
+------------+-----+--------------+
|    name    | age | children_num |
+------------+-----+--------------+
| John Smith | 27  | 3            |
+------------+-----+--------------+