Тип 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            |
+------------+-----+--------------+

Работа с JSON через ассоциативный массив

Для работы с JSON через ассоциативный массив используется дополнительный тип MAP(VARCHAR, VARCHAR).

Структура JSON преобразуется в структуру «ключ — значение», что позволяет обращаться к данным по ключам более эффективным (быстрым) способом.

Пример

Преобразуем колонку js_data таблицы js_table в тип MAP(VARCHAR, VARCHAR) и прочитаем из нее несколько полей по ключам.

SELECT
    m['first_name'] AS first_name,
    m['last_name'] AS last_name,
    m['age']::BIGINT AS age,
FROM
    (SELECT CAST(js_data::JSON AS MAP(VARCHAR, VARCHAR)) AS m
    FROM js_table)

+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| John       | Smith     | 27  |
+------------+-----------+-----+

Полезные ссылки

Функции для JSON