Типы данных JSON/JSONB
- Типы данных JSON и JSONB
- Синтаксис JSON и JSONB
- Сохранение данных JSON в таблицу
- Использование операторов в запросах данных JSON
- Использование операторов JSON в выражении WHERE
- Операторы "содержит" и "существует"
- Применение агрегатных функций к данным JSON
- Функции PostgreSQL для работы с типами JSON и JSONB
Типы данных 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 |
|
number |
numeric |
Значения |
boolean |
boolean |
Значения |
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" + }