Типы данных JSON/JSONB

Евгения Кузина

PostgreSQL предлагает два типа для хранения данных стандарта JSON, определенного в RFC 7159: JSON и JSONB. Чтобы реализовать эффективные механизмы запросов для этих типов данных, в PostgreSQL также есть тип данных JSONPath.

Типы данных JSON и JSONB

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

  • JSONB требует больше времени для создания из входящего представления.

  • Операции JSONB занимают значительно меньше времени, чем операции JSON. Функции обработки JSON анализируют данные при каждом выполнении, а JSONB хранит проанализированные и разобранные значения.

  • JSONB поддерживает индексы, JSON — нет.

Используйте формат JSON, если вы работаете с исходным представлением JSON, а PostgreSQL выполняет только хранение и извлечение данных. Если PostgreSQL производит много операций над данными JSON или применяется индексирование полей JSON, используйте JSONB. Для получения дополнительной информации об индексах формата JSONB обратитесь к статье jsonb Indexing.

JSON также сохраняет семантически несущественные пробелы между токенами и порядок ключей в объектах JSON. Если объект JSON содержит один и тот же ключ более одного раза, все пары ключ/значение сохраняются. Функции обработки считают последнее значение рабочим.

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

Кодировка JSON и JSONB

Стандарт RFC 7159 указывает, что строки JSON должны быть закодированы в UTF8. Это возможно только в том случае, если кодировка базы данных UTF8. Также вы не можете использовать символы, которые не могут быть представлены в кодировке базы данных, но допускаются символы, отличные от UTF8, поддерживаемые кодировкой базы данных.

RFC 7159 позволяет строкам JSON содержать escape-последовательности Unicode, обозначаемые \uXXXX. Входная функция для типа JSON допускает escape-последовательности Unicode независимо от кодировки базы данных. Проверяется только их синтаксис — следуют ли четыре шестнадцатеричных цифры за \u.

Входная функция JSONB запрещает последовательности Unicode для символов, которые не разрешены кодировкой базы данных. JSONB также отклоняет \u0000 (NULL), поскольку он не может быть представлен в текстовом типе PostgreSQL, и проверяет правильность использования суррогатных пар Unicode, применяющихся для обозначения символов за пределами базовой многоязычной плоскости (Basic Multilingual Plane, BMP). Входная функция JSONB преобразует допустимые escape-последовательности Unicode в эквивалентные одиночные символы и объединяет суррогатные пары в один символ.

Многие из функций обработки JSON преобразуют escape-последовательности Unicode в обычные символы и выдают ошибки. Тот факт, что входная функция JSON не проверяет правильность вводимых символов, позволяет только сохранять (без обработки) последовательности Unicode в кодировке базы данных, которая не поддерживает эти символы.

Конвертация JSONB

При преобразовании вводимого текста в JSONB примитивные типы JSON сопоставляются с собственными типами PostgreSQL, как показано в таблице ниже. JSONB имеет несколько незначительных дополнительных ограничений, соответствующих ограничениям типов данных PostgreSQL. Например, JSONB отклоняет числа, которые находятся за пределами диапазона numeric типа данных PostgreSQL.

Примитивные типы JSON Типы PostgreSQL Примечания

string

text

JSONB не допускает ввод \u0000 и других последовательностей Unicode, которые не поддерживаются кодировкой базы данных

number

numeric

Значения NaN и infinity не допускаются

boolean

boolean

Значения true и false допускаются только в нижнем регистре

null

 — 

NULL в SQL имеет другой смысл

Синтаксис JSON и JSONB

Следующие выражения JSON валидны для типов JSON и JSONB:

  • Простое скалярное/примитивное значение. Простыми значениями могут быть числа, строки, заключенные в кавычки, true, false или null. Пример:

    SELECT '5'::json;
SELECT '"Text string"'::json;

  • Массив из нуля и более элементов. Элементы могут быть разных типов. Пример:

    SELECT '[0, 1, "array element", null]'::json;

  • Объекты, содержащие пары ключей и значений. Ключ — это строка в кавычках. Пример:

    SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

  • Вложенные массивы и объекты. Пример:

    SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

Сохранение данных JSON в таблицу

Чтобы сохранить данные JSON в таблицу, используйте столбец типа JSONB или JSON. Например, создадим новую таблицу book_orders со столбцом JSONB:

CREATE TABLE book_orders (
    id serial NOT NULL PRIMARY KEY,
    content jsonb NOT NULL
);

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

INSERT INTO book_orders (content)
VALUES('{ "customer": "Jacob Johnson", "items": {"book": "Hyperion","qty": 3}}'),
        ('{ "customer": "Adam Brown", "items": {"book": "War and Peace","qty": 2}}'),
        ('{ "customer": "Andrew Nelson", "items": {"book": "1984","qty": 4}}');

Использование операторов в запросах данных JSON

Используйте SELECT для получения данных JSON так же, как и при работе с другими типами данных:

SELECT content FROM book_orders;

Результат:

                                 content
--------------------------------------------------------------------------
 {"items": {"qty": 2, "book": "War and Peace"}, "customer": "Adam Brown"}
 {"items": {"qty": 4, "book": "1984"}, "customer": "Andrew Nelson"}
 {"items": {"qty": 3, "book": "Hyperion"}, "customer": "Jacob Johnson"}

PostgreSQL также предоставляет операторы для работы с типами данных JSON. Например, оператор -> возвращает объект JSON по ключу, а ->> возвращает объект JSON по ключу в виде текста.

В следующем запросе оператор -> используется для получения всех покупателей в виде объектов JSON:

SELECT content -> 'customer' AS customer
FROM book_orders;

Результат:

    customer
-----------------
 "Jacob Johnson"
 "Adam Brown"
 "Andrew Nelson

В приведенном ниже запросе используется оператор ->>, чтобы получить всех покупателей в текстовой форме:

SELECT content ->> 'customer' AS customer
FROM book_orders;

Результат:

   customer
---------------
 Jacob Johnson
 Adam Brown
 Andrew Nelson

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

SELECT content -> 'items'->> 'book' AS book
FROM book_orders;

Результат:

     book
---------------
 War and Peace
 1984
 Hyperion

content -> 'items' возвращает items как объекты JSON. content->'items'->>'book' возвращает книги как текст.

Также возможно получить объект JSON по указанному пути. Для этого используйте оператор #>.

Следующий оператор возвращает объекты JSON по пути items→book:

SELECT content #> '{items, book}' AS books
FROM book_orders;

Результат:

      books
-----------------
 "War and Peace"
 "1984"
 "Hyperion"

Вы также можете вернуть объекты JSON по указанному пути в виде текста. Для этого используйте оператор #>>:

SELECT content #>> '{items, book}' AS books
FROM book_orders;

Результат:

     books
---------------
 War and Peace
 1984
 Hyperion

Использование операторов JSON в выражении WHERE

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

SELECT content ->> 'customer' AS customer
FROM book_orders
WHERE content -> 'items' ->> 'book' = 'War and Peace';

Результат:

  customer
------------
 Adam Brown

Чтобы использовать операторы сравнения с результатами JSON-операторов в выражении WHERE, примените приведение типа. В запросе ниже приведение типа используется для преобразования значения qty в тип INTEGER и сравнения его с числом 3:

SELECT content ->> 'customer' AS customer,
content -> 'items' ->> 'book' AS book
FROM book_orders
WHERE CAST ( content -> 'items' ->> 'qty' AS INTEGER) = 3;

Результат:

   customer    |   book
---------------+----------
 Jacob Johnson | Hyperion

Операторы "содержит" и "существует"

Можно переписать первый пример с WHERE с помощью дополнительного оператора JSONB @>, который определяет, содержит ли первое значение JSONB второе:

SELECT content ->> 'customer' AS customer
FROM book_orders
WHERE content -> 'items' -> 'book' @> '"War and Peace"'::jsonb;

Получим такой же результат:

  customer
------------
 Adam Brown

Оператор @> учитывает уровень вложенности элемента.

Например, следующее выражение возвращает true:

SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

Пример ниже вернет значение false:

SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;

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

SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

Оператор ? является разновидностью оператора @>. Он определяет, содержит ли объект JSONB указанную строку в качестве ключа объекта или элемента массива на верхнем уровне. Следующий пример возвращает true:

SELECT content ? 'customer'
FROM book_orders;

Для получения дополнительной информации об операторах JSONB обратитесь к статье Additional JSONB operators.

Применение агрегатных функций к данным JSON

Агрегатные функции (min. max, avg и другие) можно применять к данным JSON. Например, следующее выражение возвращает минимальное, максимальное, среднее и общее количество книг. Пример также использует функцию ROUND для округления результата работы функции AVG и функцию CAST для приведения типа входящих данных к типу, с которым могут работать используемые агрегатные функции.

SELECT
   ROUND(AVG (CAST (content -> 'items' ->> 'qty' AS INTEGER)),2) AS AVG,
   MIN (CAST (content -> 'items' ->> 'qty' AS INTEGER)),
   MAX (CAST (content -> 'items' ->> 'qty' AS INTEGER)),
   SUM (CAST (content -> 'items' ->> 'qty' AS INTEGER))

FROM book_orders;

Результат:

 avg  | min | max | sum
------+-----+-----+-----
 3.00 |   2 |   4 |   9

Функции PostgreSQL для работы с типами JSON и JSONB

PostgreSQL предоставляет множество функций для обработки данных JSON/JSONB, некоторые из них описаны ниже.

json_each/jsonb_each

Функции json_each и jsonb_each преобразуют JSON-объект верхнего уровня в набор пар ключ/значение.

Функции имеют следующий синтаксис:

json_each (<json>) → setof record (key text, value json)
jsonb_each (<jsonb>) → setof record (key text, value jsonb)

Пример:

SELECT jsonb_each(content)
FROM book_orders;

Результат:

                     jsonb_each
-----------------------------------------------------
 (items,"{""qty"": 2, ""book"": ""War and Peace""}")
 (customer,"""Adam Brown""")
 (items,"{""qty"": 4, ""book"": ""1984""}")
 (customer,"""Andrew Nelson""")
 (items,"{""qty"": 3, ""book"": ""Hyperion""}")
 (customer,"""Jacob Johnson""")

Чтобы получить набор пар ключ/значение в виде текста, используйте функцию json_each_text или jsonb_each_text.

json_object_keys/jsonb_object_keys

Функции json_object_keys и jsonb_object_keys возвращают набор ключей объекта JSON верхнего уровня.

Функции имеют следующий синтаксис:

json_object_keys(<json>) → setof text
jsonb_object_keys(<jsonb>) → setof text

Выражение ниже отображает набор ключей для объектов items:

SELECT jsonb_object_keys (content->'items')
FROM book_orders;

Результат:

 jsonb_object_keys
-------------------
 qty
 book
 qty
 book
 qty
 book

json_typeof/jsonb_typeof

Функции json_typeof и jsonb_typeof возвращают тип значения JSON верхнего уровня в виде строки. Они могут возвращать значения: number, boolean, null, object, array и string.

Функции имеют следующий синтаксис:

json_typeof(<json>) → setof text
jsonb_typeof(<jsonb>) → setof text

Выражение ниже отображает тип объектов qty:

SELECT jsonb_typeof (content->'items'->'qty')
FROM book_orders;

Результат:

 jsonb_typeof
--------------
 number
 number
 number

json_extract_path/jsonb_extract_path

Функции json_extract_path и jsonb_extract_path извлекают объекты JSON по указанному пути. Это эквивалентно оператору #>.

Функции имеют следующий синтаксис:

json_extract_path ( <from_json> json, <VARIADIC path_elems> text[] ) → json
jsonb_extract_path ( <from_json> jsonb, <VARIADIC path_elems> text[] ) → jsonb

Выражение ниже отображает значение объектов qty:

SELECT jsonb_extract_path (content, 'items','qty')
FROM book_orders;

Результат:

 jsonb_extract_path
--------------------
 2
 4
 3

jsonb_pretty

Функция jsonb_pretty преобразует входящее значение JSON в визуально улучшенное текстовое представление с отступами.

Функция имеет следующий синтаксис:

jsonb_pretty (<jsonb>) → text

В следующем примере отображаются объекты items:

SELECT jsonb_pretty(content->'items')
FROM book_orders;

Результат:

        jsonb_pretty
-----------------------------
 {                          +
     "qty": 2,              +
     "book": "War and Peace"+
 }
 {                          +
     "qty": 4,              +
     "book": "1984"         +
 }
 {                          +
     "qty": 3,              +
     "book": "Hyperion"     +
 }
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней