Работа с данными JSON
Greengage DB поддерживает типы JSON и JSONB для работы с данными в формате JSON.
Обычно они используются для хранения результатов API-запросов, логов приложений, конфигурационных данных и другой полуструктурированной информации, которая не имеет естественного соответствия реляционной структуре.
Greengage DB реализует JSON в соответствии со спецификацией RFC 7159 и проверяет входные данные на соответствие правилам формата JSON. Некорректный JSON-текст отклоняется при выполнении операций вставки или обновления.
Greengage DB также предоставляет набор функций и операторов JSON для извлечения полей, фильтрации данных, преобразования JSON-структур, а также создания JSON-объектов и массивов. Узнайте больше в разделе Операторы и функции JSON.
Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql.
Затем создайте новую базу данных и подключитесь к ней:
CREATE DATABASE marketplace;
\c marketplaceОбзор
О работе с JSON
Greengage DB поддерживает два типа данных JSON: JSON и JSONB.
Оба типа принимают одинаковые значения, но различаются способом хранения и производительностью:
-
JSONсохраняет исходный текст без изменений.Сохраняются все пробелы, порядок ключей и дубликаты ключей. Если в объекте присутствуют дубликаты, функции обработки
JSONиспользуют последнее значение для повторяющихся ключей. Поскольку данныеJSONпри каждом обращении нужно анализировать заново, операции выполняются медленнее. -
JSONBхранит JSON в двоичном формате, используя предварительно разобранное представление для ускорения операций.Пробелы удаляются, порядок ключей игнорируется, а при дублировании ключей сохраняется только последнее значение. Ввод данных в формате
JSONBнемного медленнее, чем вJSON, зато последующие операции надJSONBзначительно быстрее, поскольку разбор структуры выполняется только один раз. ТипJSONBтакже поддерживает индексацию.
В большинстве случаев следует использовать JSONB, за исключением ситуаций, когда необходимо сохранить исходное форматирование или порядок ключей.
Кодировка JSON и JSONB
Документ RFC 7159 допускает наличие в строках JSON управляющих последовательностей Unicode, обозначаемых как \uXXXX.
Однако в Greengage DB для каждой базы данных допускается только одна кодировка символов.
Тип данных JSON не может полностью соответствовать спецификации JSON, если кодировка базы данных отличается от UTF-8.
Попытки вставить символы, которые невозможно представить в выбранной кодировке базы данных, завершатся ошибкой.
Символы, которые могут быть представлены в кодировке базы данных, но отсутствующие в UTF-8, допускаются.
-
Функция ввода для данных
JSONпринимает управляющие последовательности Unicode независимо от кодировки базы данных и проверяет только корректность их синтаксиса (\u, за которым следуют четыре шестнадцатеричные цифры). -
Функция ввода для данных
JSONBболее строго контролирует ввод:-
Не-ASCII символы (
U+0080и выше) допускаются, только если кодировка базы данных UTF-8. -
Символы
\u0000отклоняются. -
Суррогатные пары Unicode должны быть корректными; допустимые последовательности преобразуются в эквивалентные символы ASCII или UTF-8 для хранения.
-
Многие JSON-функции автоматически преобразуют управляющие последовательности Unicode в обычные символы. Если символ невозможно представить в кодировке базы данных, функция выдаст ошибку. Чтобы избежать проблем, не используйте управляющие последовательности Unicode вместе с кодировкой базы данных, отличной от UTF-8.
Пример:
Данная команда пытается привести JSON-литерал, содержащий знак авторского права, к типу JSONB:
SELECT '{ "a": "the Copyright \u00A9 sign" }'::jsonb;В базе данных с кодировкой LATIN1 эта команда выдаст следующую ошибку:
ERROR: unsupported Unicode escape sequence
Преобразование в JSONB
При преобразовании JSON-текста в JSONB примитивные типы JSON сопоставляются с соответствующими типами Greengage DB, как показано в таблице ниже.
JSONB накладывает некоторые дополнительные ограничения, связанные с возможностями типов данных Greengage DB.
Например, JSONB отклоняет числовые значения, выходящие за пределы диапазона, поддерживаемого типом NUMERIC в Greengage DB.
| Примитивный тип JSON | Тип Greengage DB | Примечание |
|---|---|---|
string |
text |
|
number |
numeric |
Значения |
boolean |
boolean |
Значения |
null |
(отсутствует) |
Представляет JSON |
Создание таблицы для JSON-данных
Для того чтобы сохранять JSON-документы, создайте таблицу со столбцом типа JSON или JSONB.
Пример ниже показывает создание таблицы customer_profiles, в которой каждый профиль клиента хранится в виде документа JSONB:
CREATE TABLE customer_profiles
(
profile JSONB
)
WITH (appendoptimized = true)
DISTRIBUTED RANDOMLY;Узнайте больше про определение таблиц в статье Обзор таблиц.
Загрузка JSON-данных
Для эффективной вставки больших объемов JSON-данных используйте внешние таблицы или команду COPY.
Подробнее о массовой загрузке данных смотрите в следующих статьях:
Оптимизированные для добавления (AO) таблицы предназначены для массовой загрузки данных, поэтому использование одиночных команд INSERT для них не рекомендуется.
Следующий файл customers.jsonl содержит по одному JSON-объекту на строку; каждый объект представляет собой профиль клиента:
{"id": "c001", "name": "Alice Johnson", "email": "alice@example.com", "active": true, "registered": "2020-01-15", "tags": {"membership": ["premium", "newsletter"], "region": "US"}}
{"id": "c002", "name": "Bob Smith", "email": "bob@example.com", "active": false, "registered": "2019-06-30", "tags": {"membership": ["trial"], "region": "EU"}}
{"id": "c003", "name": "Charlie Brown", "email": "charlie@example.com", "active": true, "registered": "2021-09-10"}
{"id": "c004", "name": "Diana Miller", "email": "diana@example.com", "tags": {"region": "UK", "membership": ["newsletter", "trial"]}}Вы можете загрузить JSON-документы в таблицу напрямую из файла с помощью команды COPY:
COPY customer_profiles (profile)
FROM '/home/gpadmin/examples/json/customers.jsonl';Запросы к JSON-данным
Вы можете выполнять запросы к полям JSON с помощью операторов и функций JSON.
В примере ниже поля верхнего уровня извлекаются из JSON-документа с помощью оператора ->>:
SELECT profile ->> 'name' AS name,
profile ->> 'email' AS email
FROM customer_profiles;Результат должен выглядеть так:
name | email ---------------+--------------------- Alice Johnson | alice@example.com Bob Smith | bob@example.com Charlie Brown | charlie@example.com Diana Miller | diana@example.com (4 rows)
Вы можете обращаться к вложенным атрибутам в объектах JSON с помощью нескольких операторов -> и ->>: используйте -> для получения JSON-объекта или массива для дальнейшей навигации, а ->> — для извлечения конечного значения в виде обычного текста:
SELECT profile ->> 'name' AS name,
profile -> 'tags' ->> 'region' AS region
FROM customer_profiles;Результат выглядит следующим образом:
name | region ---------------+-------- Alice Johnson | US Bob Smith | EU Charlie Brown | Diana Miller | UK (4 rows)
В качестве альтернативы можно использовать функцию jsonb_extract_path_text(), чтобы извлекать вложенные значения, указывая путь явно:
SELECT profile ->> 'name' AS name,
jsonb_extract_path_text(profile, 'tags', 'region') AS region_json
FROM customer_profiles;Подробнее о получении строк из таблиц и представлений см. в статье Обзор команды SELECT.
Фильтрация JSON-данных
Вы можете фильтровать строки по значениям JSON-полей с помощью выражений WHERE или HAVING.
Операторы JSON позволяют напрямую сравнивать поля, включая вложенные атрибуты.
Этот пример отбирает записи, где указанный ключ JSON верхнего уровня имеет заданное значение:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ->> 'active' = 'true';Результат:
name --------------- Alice Johnson Charlie Brown (2 rows)
В этом примере фильтрация выполняется по вложенному JSON-ключу:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile -> 'tags' ->> 'region' = 'US';Результат должен выглядеть так:
name --------------- Alice Johnson (1 row)
Вы также можете приводить строковые значения JSON к нативным типам для сравнения:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE (profile ->> 'registered')::date > '2020-01-01';Вывод выглядит следующим образом:
name --------------- Alice Johnson Charlie Brown (2 rows)
JSONB: проверки вхождения и существования
Тип JSONB поддерживает операции проверки вхождения и существования:
-
Проверка вхождения определяет, содержится ли один JSON-документ внутри другого,
-
Проверка существования определяет, присутствует ли заданный ключ или элемент массива на верхнем уровне JSON-значения.
В приведенном ниже примере используется оператор @> для получения строк, в которых столбец JSONB содержит указанный поддокумент:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile -> 'tags' @> '{"region": "EU"}';Результат:
name ----------- Bob Smith (1 row)
Этот запрос возвращает строки, у которых JSON-объект верхнего уровня содержит указанный ключ:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ? 'tags';Результат выглядит следующим образом:
name --------------- Alice Johnson Bob Smith Diana Miller (3 rows)
Этот запрос возвращает строки, в которых JSON-объект содержит любой из указанных ключей:
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ?| ARRAY ['active', 'registered'];Вывод выглядит следующим образом:
name --------------- Charlie Brown Alice Johnson Bob Smith (3 rows)
Индексация JSONB
JSONB поддерживает индексы GIN и B-дерево:
-
GIN-индексы эффективно выполняют поиск ключей или пар ключ/значение в большом количестве документов
JSONB. -
Индексы B-tree полезны в основном для сравнения или проверки равенства JSON-документов целиком.
GIN-индексы поддерживают два класса операторов для JSONB:
-
jsonb_ops— класс операторов GIN, используемый по умолчанию, который поддерживает запросы с операторами@>,?,?&и?|. Этот класс обеспечивает большую гибкость, но может занимать больше места в индексе и имеет немного более низкую производительность для запросов на вхождение. -
jsonb_path_ops— альтернативный класс операторов GIN, который поддерживает индексацию только для оператора@>. Индексируются только пути и значения, необходимые для проверки вхождения, что обеспечивает меньший размер индекса и более быстрый поиск.
Создайте GIN-индекс по столбцу profile в таблице customer_profiles:
CREATE INDEX profile_gin_idx ON customer_profiles USING gin (profile);После создания индекса выполните ANALYZE, чтобы обновить статистику таблицы для планировщика запросов:
ANALYZE;Проверьте, что индекс используется для поиска документов JSONB, соответствующих условию вхождения:
EXPLAIN (COSTS OFF)
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile @> '{"active": false}';Результат должен включать Bitmap Index Scan по индексу profile_gin_idx:
QUERY PLAN
-------------------------------------------------------------------------
Gather Motion 4:1 (slice1; segments: 4)
-> Result
-> Bitmap Heap Scan on customer_profiles
Recheck Cond: (profile @> '{"active": false}'::jsonb)
-> Bitmap Index Scan on profile_gin_idx
Index Cond: (profile @> '{"active": false}'::jsonb)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
Операторы и функции JSON
Операторы JSON
Следующие операторы доступны для типов данных JSON и JSONB.
Получение элемента JSON-массива (→)
Оператор: ->
Тип правого операнда: int
Возвращает элемент JSON-массива по указанному индексу, начиная с нуля.
Пример:
SELECT '[
{
"id": 1,
"name": "Alice"
},
{
"id": 2,
"name": "Bob"
}
]'::jsonb -> 1;Результат:
?column?
--------------------------
{"id": 2, "name": "Bob"}
(1 row)
Получение поля JSON-объекта (→)
Оператор: ->
Тип правого операнда: text
Возвращает поле JSON-объекта по ключу.
Пример:
SELECT '{
"user": {
"name": "Alice",
"email": "alice@example.com"
}
}'::jsonb -> 'user';Результат:
?column?
-------------------------------------------------
{"name": "Alice", "email": "alice@example.com"}
(1 row)
Получение элемента JSON-массива в виде текста (→>)
Оператор: ->>
Тип правого операнда: int
Возвращает элемент JSON-массива в виде текста.
Пример:
SELECT '[
"apple",
"banana",
"cherry"
]'::jsonb ->> 1;Результат:
?column? ---------- banana (1 row)
Получение поля JSON-объекта в виде текста (->>)
Оператор: ->>
Тип правого операнда: text
Возвращает поле JSON-объекта в виде текста.
Пример:
SELECT '{
"product": "Book",
"price": 9.99
}'::jsonb ->> 'product';Результат:
?column? ---------- Book (1 row)
Получение JSON-объекта по указанному пути (#>)
Оператор: #>
Тип правого операнда: text[]
Возвращает JSON-объект по указанному пути.
Пример:
SELECT '{
"customer": {
"address": {
"city": "Amsterdam",
"zip": "1011AB"
}
}
}'::jsonb #> '{customer,address}';Результат:
?column?
----------------------------------------
{"zip": "1011AB", "city": "Amsterdam"}
(1 row)
Получение JSON-объекта по указанному пути в виде текста (#>>)
Оператор: #>>
Тип правого операнда: text[]
Возвращает JSON-объект по указанному пути в виде текста.
Пример:
SELECT '{
"customer": {
"address": {
"city": "Amsterdam",
"zip": "1011AB"
}
}
}'::jsonb #>> '{customer,address,city}';Результат:
?column? ----------- Amsterdam (1 row)
Операторы JSONB
Следующие операторы доступны только для значений типа JSONB.
Многие из них могут быть проиндексированы с помощью классов операторов JSONB.
Вхождение (@>)
Оператор: @>
Тип правого операнда: JSONB
Проверяет, содержится ли правое JSON-значение внутри левого JSON-значения.
Пример:
SELECT '{
"name": "Alice",
"active": true,
"role": "admin"
}'::jsonb @> '{
"role": "admin"
}'::jsonb;Результат:
?column? ---------- t (1 row)
Входит в (<@)
Оператор: <@
Тип правого операнда: JSONB
Проверяет, содержится ли левое JSON-значение внутри правого JSON-значения.
Пример:
SELECT '{
"role": "admin"
}'::jsonb <@ '{
"name": "Alice",
"active": true,
"role": "admin"
}'::jsonb;Результат:
?column? ---------- t (1 row)
Существование ключа/элемента (?)
Оператор: ?
Тип правого операнда: text
Проверяет, существует ли указанный ключ или элемент в JSON-значении.
Пример:
SELECT '{
"name": "Alice",
"email": "alice@example.com"
}'::jsonb ? 'address';Результат:
?column? ---------- f (1 row)
Существование любого ключа/элемента из перечисленных (?|)
Оператор: ?|
Тип правого операнда: text[]
Проверяет, существует ли хотя бы один из указанных ключей или элементов в JSON-значении.
Пример:
SELECT '{
"name": "Alice",
"email": "alice@example.com",
"active": true
}'::jsonb ?| array ['phone','email'];Результат:
?column? ---------- t (1 row)
Существование всех перечисленных ключей/элементов (?&)
Оператор: ?&
Тип правого операнда: text[]
Проверяет, существуют ли все указанные ключи или элементы.
Пример:
SELECT '{
"name": "Alice",
"email": "alice@example.com",
"active": true
}'::jsonb ?& array ['phone','email'];Результат:
?column? ---------- f (1 row)
Функции создания JSON
Этот раздел описывает функции, которые генерируют значения JSON.
Хотя у этих функций нет отдельных версий для JSONB, их результаты можно привести к типу JSONB.
array_to_json()
Функция: array_to_json(anyarray [, pretty_bool])
Возвращает массив в виде JSON-массива.
Многомерный массив преобразуется в JSON-массив массивов.
Если параметр pretty_bool установлен в true, между элементами верхнего уровня добавляются переносы строки.
Пример:
SELECT array_to_json('{{10, 20}, {30, 40}}'::int[]);Результат:
array_to_json ------------------- [[10,20],[30,40]] (1 row)
json_build_array()
Функция: json_build_array(VARIADIC "any")
Создает JSON-массив, элементы которого могут быть разных типов, из списка аргументов VARIADIC.
Пример:
SELECT json_build_array(1001, 'Alice', true, 89.5);Результат:
json_build_array ----------------------------- [1001, "Alice", true, 89.5] (1 row)
json_build_object()
Функция: json_build_object(VARIADIC "any")
Создает JSON-объект из списка аргументов VARIADIC.
Список аргументов обрабатывается по порядку и преобразуется в набор пар ключ/значение.
Пример:
SELECT json_build_object('id', 1001, 'name', 'Alice', 'active', true);Результат:
json_build_object
--------------------------------------------------
{"id" : 1001, "name" : "Alice", "active" : true}
(1 row)
json_object(text[])
Функция: json_object(text[])
Создает JSON-объект из текстового массива. Массив должен быть одномерным или двумерным. Одномерный массив должен содержать четное число элементов. Элементы интерпретируются как пары ключ/значение.
Для двумерного массива каждый внутренний массив должен содержать ровно два элемента, которые рассматриваются как пара ключ/значение.
Пример 1:
SELECT json_object('{id, 1, name, "Book", price, "19.99"}');Результат:
json_object
--------------------------------------------------
{"id" : "1", "name" : "Book", "price" : "19.99"}
(1 row)
Пример 2:
SELECT json_object('{{id, 1}, {name, "Book"}, {price, "19.99"}}');Результат:
json_object
--------------------------------------------------
{"id" : "1", "name" : "Book", "price" : "19.99"}
(1 row)
json_object(text[], text[])
Функция: json_object(keys text[], values text[])
Создает JSON-объект из текстового массива.
В этой форме json_object() ключи и значения берутся по парам из двух отдельных массивов.
В остальном поведение совпадает с формой функции с одним аргументом.
Пример:
SELECT json_object('{id, name}', '{1, "Book"}');Результат:
json_object
-------------------------------
{"id" : "1", "name" : "Book"}
(1 row)
row_to_json()
Функция: row_to_json(record [, pretty_bool])
Возвращает строку в виде JSON-объекта.
Если параметр pretty_bool установлен в true, между элементами верхнего уровня добавляются переносы строки.
Пример:
SELECT row_to_json(row (10, 'Laptop'));Результат:
row_to_json
-------------------------
{"f1":10,"f2":"Laptop"}
(1 row)
to_json()
Функция: to_json(anyelement)
Возвращает значение в виде JSON-объекта.
Массивы и составные типы обрабатываются рекурсивно и преобразуются в массивы и объекты.
Если входные данные содержат приведение типа к JSON, для преобразования используется функция приведения; в противном случае создается скалярное JSON-значение.
Для всех скалярных типов, кроме числа, boolean-значения или null, используется текстовое представление с кавычками и экранированием, чтобы получилось корректное JSON-значение.
Пример:
SELECT to_json('Alice said "Hi!"'::text);Результат:
to_json ---------------------- "Alice said \"Hi!\"" (1 row)
Агрегатные функции JSON
CREATE TABLE products
(
id SERIAL,
name VARCHAR(100),
price DECIMAL(10, 2)
)
WITH (appendoptimized = true)
DISTRIBUTED BY (id);INSERT INTO products (name, price)
VALUES ('Laptop', 999.99),
('Smartphone', 499.99),
('Headphones', 89.99);json_agg()
Функция: json_agg(<record>)
Агрегирует записи в виде JSON-массива объектов.
Пример:
SELECT json_agg(name ORDER BY price DESC) AS product_names
FROM products;Результат:
product_names ---------------------------------------- ["Laptop", "Smartphone", "Headphones"] (1 row)
json_object_agg()
Функция: json_object_agg(name, value)
Агрегирует пары имя/значение в виде JSON-объекта.
Пример:
SELECT json_object_agg(id, name ORDER BY price DESC) AS products_map
FROM products;Результат:
products_map
------------------------------------------------------------
{ "1" : "Laptop", "2" : "Smartphone", "3" : "Headphones" }
(1 row)
Функции обработки JSON
В этом разделе перечислены функции, доступные для обработки значений типов JSON и JSONB.
json_array_elements() / jsonb_array_elements()
Функция: json_array_elements(json), jsonb_array_elements(jsonb)
Тип возвращаемого значения: setof json, setof jsonb
Разворачивает JSON-массив в набор JSON-значений.
Пример:
SELECT *
FROM jsonb_array_elements('[
{
"name": "Alice"
},
{
"name": "Bob"
},
{
"name": "Carol"
}
]');Результат:
value
-------------------
{"name": "Alice"}
{"name": "Bob"}
{"name": "Carol"}
(3 rows)
json_array_elements_text() / jsonb_array_elements_text()
Функция: json_array_elements_text(json), jsonb_array_elements_text(jsonb)
Тип возвращаемого значения: setof text
Разворачивает JSON-массив в набор текстовых значений.
Пример:
SELECT *
FROM jsonb_array_elements_text('["Laptop","Smartphone","Headphones"]');Результат:
value ------------ Laptop Smartphone Headphones (3 rows)
json_array_length() / jsonb_array_length()
Функция: json_array_length(json), jsonb_array_length(jsonb)
Тип возвращаемого значения: int
Возвращает количество элементов в JSON-массиве верхнего уровня.
Пример:
SELECT json_array_length('[{"name":"Alice"},{"name":"Bob"},{"name":"Carol"}]');Результат:
json_array_length
-------------------
3
(1 row)
json_each() / jsonb_each()
Функция: json_each(json), jsonb_each(jsonb)
Тип возвращаемого значения: setof record со столбцами:
-
key text -
value json(json_each) /value jsonb(jsonb_each)
Разворачивает JSON-объект верхнего уровня в набор пар ключ/значение.
Пример:
SELECT *
FROM json_each('{
"product": "Laptop",
"price": 999.99,
"in_stock": true
}');Результат:
key | value ----------+---------- product | "Laptop" price | 999.99 in_stock | true (3 rows)
json_each_text() / jsonb_each_text()
Функция: json_each_text(json), jsonb_each_text(jsonb)
Тип возвращаемого значения: setof record со столбцами:
-
key text -
value text
Разворачивает JSON-объект верхнего уровня в набор пар ключ/значение.
Возвращаемые значения будут иметь тип text.
Пример:
SELECT *
FROM json_each_text('{
"product": "Laptop",
"price": 999.99,
"in_stock": true
}');Результат:
key | value ----------+-------- product | Laptop price | 999.99 in_stock | true (3 rows)
json_extract_path() / jsonb_extract_path()
Функция: json_extract_path(from_json json, VARIADIC path_elems text[]), jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
Тип возвращаемого значения: JSON, JSONB
Возвращает JSON-значение, на которое указывает path_elems.
Эквивалентно оператору #> (см. Получение JSON-объекта по указанному пути (#>)).
Пример:
SELECT jsonb_extract_path(
'{
"order": {
"id": 123,
"customer": {
"name": "Alice",
"vip": true
}
}
}',
'order', 'customer'
);Результат:
jsonb_extract_path
--------------------------------
{"vip": true, "name": "Alice"}
(1 row)
json_extract_path_text() / jsonb_extract_path_text()
Функция: json_extract_path_text(from_json json, VARIADIC path_elems text[]), jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
Тип возвращаемого значения: text
Возвращает JSON-значение, на которое указывает path_elems, в виде текста.
Эквивалентно оператору #>> (см. Получение JSON-объекта по указанному пути в виде текста (#>>)).
Пример:
SELECT jsonb_extract_path_text(
'{
"order": {
"id": 123,
"customer": {
"name": "Alice",
"vip": true
}
}
}',
'order', 'customer', 'name'
);Результат:
jsonb_extract_path_text ------------------------- Alice (1 row)
json_object_keys() / jsonb_object_keys()
Функция: json_object_keys(json), jsonb_object_keys(jsonb)
Тип возвращаемого значения: setof text
Возвращает набор ключей из JSON-объекта верхнего уровня.
Пример:
SELECT *
FROM json_object_keys('{
"id": 1,
"name": "Laptop",
"price": 999.99
}');Результат:
json_object_keys ------------------ id name price (3 rows)
json_populate_record() / jsonb_populate_record()
Функция: json_populate_record(base anyelement, from_json json), jsonb_populate_record(base anyelement, from_json jsonb)
Тип возвращаемого значения: anyelement
Разворачивает объект из from_json в строку, столбцы которой соответствуют типу записи, определенному параметром base.
Пример:
CREATE TYPE product_info AS
(
id INT,
name TEXT,
price NUMERIC
);
SELECT *
FROM json_populate_record(NULL::product_info,
'{
"id": 1,
"name": "Laptop",
"price": 999.99
}');Результат:
id | name | price ----+--------+-------- 1 | Laptop | 999.99 (1 row)
json_populate_recordset() / jsonb_populate_recordset()
Функция: json_populate_recordset(base anyelement, from_json json), jsonb_populate_recordset(base anyelement, from_json jsonb)
Тип возвращаемого значения: setof anyelement
Разворачивает внешний массив объектов из from_json в набор строк, столбцы которых соответствуют типу записи, определенному параметром base.
Пример:
CREATE TYPE item_info AS
(
id INT,
qty INT
);
SELECT *
FROM json_populate_recordset(NULL::item_info,
'[
{
"id": 1,
"qty": 10
},
{
"id": 2,
"qty": 25
},
{
"id": 3,
"qty": 40
}
]');Результат:
id | qty ----+----- 1 | 10 2 | 25 3 | 40 (3 rows)
json_to_record() / jsonb_to_record()
Функция: json_to_record(json), jsonb_to_record(jsonb)
Тип возвращаемого значения: record
Создает произвольную запись из JSON-объекта.
Как и для всех функций, возвращающих запись, вызывающий должен явно указать структуру записи с помощью ключевого слова AS.
Пример:
SELECT *
FROM json_to_record('{"id":1,"name":"Laptop","price":999.99}')
AS x(id INT, name TEXT, price NUMERIC);Результат:
id | name | price ----+--------+-------- 1 | Laptop | 999.99 (1 row)
json_to_recordset() / jsonb_to_recordset()
Функция: json_to_recordset(json), jsonb_to_recordset(jsonb)
Тип возвращаемого значения: setof record
Создает произвольный набор записей из JSON-массива объектов.
Как и для всех функций, возвращающих запись, вызывающий должен явно указать структуру записи с помощью ключевого слова AS.
Пример:
SELECT *
FROM json_to_recordset('[{"id":1,"name":"Laptop"},{"id":2,"name":"Smartphone"}]')
AS x(id INT, name TEXT);Результат:
id | name ----+------------ 1 | Laptop 2 | Smartphone (2 rows)
json_typeof() / jsonb_typeof()
Функция: json_typeof(json), jsonb_typeof(jsonb)
Тип возвращаемого значения: text
Возвращает тип JSON-значения верхнего уровня в виде строки.
Возможные типы: object, array, string, number, boolean и null.
Значение null, возвращаемое функцией json_typeof(), не следует путать с SQL-значением NULL.
Вызов json_typeof('null'::json) возвращает текстовое значение null, тогда как вызов json_typeof(NULL::json) возвращает SQL-значение NULL.
Пример:
SELECT json_typeof('{"name":"Alice"}'),
json_typeof('["a","b","c"]'),
json_typeof('123'),
json_typeof('true');Результат:
json_typeof | json_typeof | json_typeof | json_typeof -------------+-------------+-------------+------------- object | array | number | boolean (1 row)
Примеры функций json_populate_record(), json_populate_recordset(), json_to_record() и json_to_recordset() используют константные значения.
На практике эти функции обычно применяются к столбцам типов JSON или JSONB из таблицы в конструкции FROM.
Извлеченные значения ключей можно использовать в других частях запроса, например, в WHERE или в списке целевых столбцов.
Извлечение нескольких значений одновременно обычно быстрее, чем использование отдельных операторов для каждого ключа.