9.15. Функции и операторы JSON
В Таблице 9.40 показаны операторы, позволяющие работать с двумя типами данных JSON (см. Раздел 8.14).
Таблица 9.40. Операторы для типов json и jsonb
| Оператор | Тип правого операнда | Описание | Пример | Результат примера |
|---|---|---|---|---|
-> | int | Выдаёт элемент массива JSON (по номеру от 0, отрицательные числа задают позиции с конца) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | Выдаёт поле объекта JSON по ключу | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | Выдаёт элемент массива JSON в типе text | '[1,2,3]'::json->>2 | 3 |
->> | text | Выдаёт поле объекта JSON в типе text | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | Выдаёт объект JSON по заданному пути | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | text[] | Выдаёт объект JSON по заданному пути в типе text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
Примечание
Эти операторы существуют в двух вариациях для типов json и jsonb. Операторы извлечения поля/элемента/пути возвращают тот же тип, что у операнда слева (json или jsonb), за исключением тех, что возвращают тип text (они возвращают значение как текстовое). Если входные данные JSON не содержат структуры, удовлетворяющей запросу, например в них нет искомого элемента, то операторы извлечения поля/элемента/пути не выдают ошибку, а возвращают NULL. Все операторы извлечения поля/элемента/пути, принимающие целочисленные позиции в массивах JSON, поддерживают и отсчёт от конца массива по отрицательной позиции.
Стандартные операторы сравнения, приведённые в Таблице 9.1, есть для типа jsonb, но не для json. Они следуют правилам сортировки для операций B-дерева, описанным в Подразделе 8.14.4.
Некоторые из следующих операторов существуют только для jsonb, как показано в Таблице 9.41. Многие из этих операторов могут быть проиндексированы с помощью классов операторов jsonb. Полное описание проверок на вхождение и существование для jsonb приведено в Подразделе 8.14.3. Как эти операторы могут использоваться для эффективного индексирования данных jsonb, описано в Подразделе 8.14.4.
Таблица 9.41. Дополнительные операторы jsonb
| Оператор | Тип правого операнда | Описание | Пример |
|---|---|---|---|
@> | jsonb | Левое значение JSON содержит на верхнем уровне путь/значение JSON справа? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ | jsonb | Путь/значение JSON слева содержится на верхнем уровне в правом значении JSON? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? | text | Присутствует ли строка в качестве ключа верхнего уровня в значении JSON? | '{"a":1, "b":2}'::jsonb ? 'b' |
?| | text[] | Какие-либо строки массива присутствуют в качестве ключей верхнего уровня? | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
?& | text[] | Все строки массива присутствуют в качестве ключей верхнего уровня? | '["a", "b"]'::jsonb ?& array['a', 'b'] |
|| | jsonb | Соединяет два значения jsonb в новое значение jsonb | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
- | text | Удаляет пару ключ/значение или элемент-строку из левого операнда. Пары ключ/значение выбираются по значению ключа. | '{"a": "b"}'::jsonb - 'a' |
- | integer | Удаляет из массива элемент в заданной позиции (отрицательные номера позиций отсчитываются от конца). Выдаёт ошибку, если контейнер верхнего уровня — не массив. | '["a", "b"]'::jsonb - 1 |
#- | text[] | Удаляет поле или элемент с заданным путём (для массивов JSON отрицательные номера позиций отсчитываются от конца) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
Примечание
Оператор || соединяет два объекта JSON вместе, формируя объект с объединённым набором ключей и значений, при этом в случае совпадения ключей выбирается значение из второго объекта. С любыми другими аргументами формируется массив JSON: сначала любой аргумент, отличный от массива, преобразуется в одноэлементный массив, а затем два полученных массива соединяются. Эта операция не рекурсивна — объединение производится только на верхнем уровне структуры объекта или массива.
В Таблице 9.42 показаны функции, позволяющие создавать значения типов json и jsonb. (Для типа jsonb нет аналогов функций row_to_json и array_to_json, но практически тот же результат можно получить с помощью to_jsonb.)
Таблица 9.42. Функции для создания JSON
| Функция | Описание | Пример | Результат примера |
|---|---|---|---|
| Возвращает значение в виде json или jsonb. Массивы и составные структуры преобразуются (рекурсивно) в массивы и объекты; для других типов, для которых определено приведение к json, применяется эта функция приведения, а для всех остальных выдаётся скалярное значение. Значения всех скалярных типов, кроме числового, логического и NULL, представляются в текстовом виде, в стиле, допустимом для значений json или jsonb. | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
array_to_json(anyarray [, pretty_bool]) | Возвращает массив в виде массива JSON. Многомерный массив PostgreSQL становится массивом массивов JSON. Если параметр pretty_bool равен true, между элементами 1-ой размерности вставляются разрывы строк. | array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json(record [, pretty_bool]) | Возвращает кортеж в виде объекта JSON. Если параметр pretty_bool равен true, между элементами 1-ой размерности вставляются разрывы строк. | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
| Формирует массив JSON (возможно, разнородный) из переменного списка аргументов. | json_build_array(1,2,'3',4,5) | [1, 2, "3", 4, 5] |
| Формирует объект JSON из переменного списка аргументов. По соглашению в этом списке перечисляются по очереди ключи и значения. | json_build_object('foo',1,'bar',2) | {"foo": 1, "bar": 2} |
| Формирует объект JSON из текстового массива. Этот массив должен иметь либо одну размерность с чётным числом элементов (в этом случае они воспринимаются как чередующиеся ключи/значения), либо две размерности и при этом каждый внутренний массив содержит ровно два элемента, которые воспринимаются как пара ключ/значение. |
| {"a": "1", "b": "def", "c": "3.5"} |
| Эта форма json_object принимает ключи и значения по парам из двух отдельных массивов. Во всех остальных отношениях она не отличается от формы с одним аргументом. | json_object('{a, b}', '{1,2}') | {"a": "1", "b": "2"} |
Примечание
Функции array_to_json и row_to_json подобны to_json, но предлагают возможность улучшенного вывода. Действие to_json, описанное выше, распространяется на каждое отдельное значение, преобразуемое этими функциями.
Примечание
В расширении hstore определено преобразование из hstore в json, так что значения hstore, преобразуемые функциями создания JSON, будут представлены в виде объектов JSON, а не как примитивные строковые значения.
В Таблице 9.43 показаны функции, предназначенные для работы со значениями json и jsonb.
Таблица 9.43. Функции для обработки JSON
| Функция | Тип результата | Описание | Пример | Результат примера |
|---|---|---|---|---|
| int | Возвращает число элементов во внешнем массиве JSON. | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') | 5 |
|
| Разворачивает внешний объект JSON в набор пар ключ/значение (key/value). | select * from json_each('{"a":"foo", "b":"bar"}') | key | value -----+------- a | "foo" b | "bar" |
| setof key text, value text | Разворачивает внешний объект JSON в набор пар ключ/значение (key/value). Возвращаемые значения будут иметь тип text. | select * from json_each_text('{"a":"foo", "b":"bar"}') | key | value -----+------- a | foo b | bar |
|
| Возвращает значение JSON по пути, заданному элементами пути (path_elems) (равнозначно оператору #> operator). | json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} |
| text | Возвращает значение JSON по пути, заданному элементами пути path_elems, как text (равнозначно оператору #>>). | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo |
| setof text | Возвращает набор ключей во внешнем объекте JSON. | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys ----------------- f1 f2 |
| anyelement | Разворачивает объект из from_json в табличную строку, в которой столбцы соответствуют типу строки, заданному параметром base (см. примечания ниже). | select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') | a | b ---+--- 1 | 2 |
| setof anyelement | Разворачивает внешний массив объектов из from_json в набор табличных строк, в котором столбцы соответствуют типу строки, заданному параметром base (см. примечания ниже). | select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') | a | b ---+--- 1 | 2 3 | 4 |
|
| Разворачивает массив JSON в набор значений JSON. | select * from json_array_elements('[1,true, [2,false]]') | value ----------- 1 true [2,false] |
| setof text | Разворачивает массив JSON в набор значений text. | select * from json_array_elements_text('["foo", "bar"]') | value ----------- foo bar |
| text | Возвращает тип внешнего значения JSON в виде текстовой строки. Возможные типы: object, array, string, number, boolean и null. | json_typeof('-123.4') | number |
| record | Формирует обычную запись из объекта JSON (см. примечания ниже). Как и со всеми функциями, возвращающими record, при вызове необходимо явно определить структуру записи с помощью предложения AS. | select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text) | a | b | d ---+---------+--- 1 | [1,2,3] | |
| setof record | Формирует обычный набор записей из массива объекта JSON (см. примечания ниже). Как и со всеми функциями, возвращающими record, при вызове необходимо явно определить структуру записи с помощью предложения AS. | select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); | a | b ---+----- 1 | foo 2 | |
|
| Возвращает значение from_json, из которого исключаются все поля объекта, содержащие значения NULL. Другие значения NULL остаются нетронутыми. | json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] |
|
| Возвращает значение target, в котором раздел с заданным путём (path) заменяется новым значением (new_value), либо в него добавляется значение new_value, если аргумент create_missing равен true (это значение по умолчанию) и элемент, на который ссылается path, не существует. Как и с операторами, рассчитанными на пути, отрицательные числа в пути (path) обозначают отсчёт от конца массивов JSON. |
|
|
|
| Возвращает значение from_json в виде текста JSON с отступами. | jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') | [
{
"f1": 1,
"f2": null
},
2,
null,
3
] |
Примечание
Многие из этих функций и операторов преобразуют спецпоследовательности Unicode в JSON-строках в соответствующие одиночные символы. Для входных данных типа jsonb это ничем не грозит, так как преобразование уже выполнено; однако для типа json в результате может произойти ошибка, как отмечено в Разделе 8.14.
Примечание
Хотя в примерах использования функций json_populate_record, json_populate_recordset, json_to_record и json_to_recordset применяются константы, обычно эти функции обращаются к таблицам в предложении FROM, а в качестве аргумента указывается один из столбцов типа json или jsonb. Извлечённые значения затем могут использоваться в других частях запроса, например, в предложениях WHERE и результирующих списках. Извлекая множество значений подобным образом, можно значительно увеличить производительность по сравнению с использованием операторов, работающих с отдельными ключами.
Ключи JSON сопоставляются с идентичными именами столбцов в целевом типе записей. Приведение типов JSON для этих функций выполняется «насколько это возможно», и его результаты могут быть не вполне ожидаемыми для некоторых типов. Поля JSON, отсутствующие в целевом типе, в выходные данные не попадают, а целевые столбцы, для которых нет соответствующих полей JSON, будут просто содержать NULL.
Примечание
В target должны присутствовать все элементы пути, заданного параметром path функции jsonb_set, если только create_missing не равно true — в этом случае должны присутствовать все элементы, кроме последнего. Если это условие не выполняется, значение target возвращается неизменённым.
Если последним элементом пути оказывается ключ объекта, он будет создан в случае отсутствия и получит новое значение. Если последний элемент пути — позиция в массиве, то когда она положительна, целевой элемент отсчитывается слева, а когда отрицательна — справа, то есть -1 указывает на самый правый элемент и т. д. Если позиция лежит вне диапазона -длина_массива .. длина_массива -1, и параметр create_missing равен true, новое значение добавляется в начало массива, если позиция отрицательна, и в конец, если положительна.
Примечание
Значение null, возвращаемое функцией json_typeof, не следует путать с SQL NULL. Тогда как при вызове json_typeof('null'::json) возвращается null, при вызове json_typeof(NULL::json) будет возвращено значение SQL NULL.
Примечание
Если аргумент функции json_strip_nulls содержит повторяющиеся имена полей в любом объекте, в результате могут проявиться семантические различия, в зависимости от порядка этих полей. Это не проблема для функции jsonb_strip_nulls, так как в значениях jsonb имена полей не могут дублироваться.
В Разделе 9.20 вы также можете узнать об агрегатной функции json_agg, которая агрегирует значения записи в виде JSON, и агрегатной функции json_object_agg, агрегирующей пары значений в объект JSON, а также их аналогах для jsonb, функциях jsonb_agg и jsonb_object_agg.