| Документация к Postgres Pro 9.5.12.1 | |||
|---|---|---|---|
| Пред. | Уровень выше | Глава 9. Функции и операторы | След. |
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}' |
Замечание: Оператор || соединяет элементы верхнего уровня обоих операндов. Вложенность при этом не учитывается. Например, если оба операнда содержат поле с одноимённым ключом, значением поля в результате будет значение, взятое из правого операнда.
В Табл. 9-42 показаны функции, позволяющие создавать значения типов json и jsonb. (Для типа jsonb нет аналогов функций row_to_json и array_to_json, но практически тот же результат можно получить с помощью to_jsonb.)
Таблица 9-42. Функции для создания JSON
| Функция | Описание | Пример | Результат примера |
|---|---|---|---|
to_json(anyelement) to_jsonb(anyelement) | Возвращает значение в виде json или jsonb. Массивы и составные структуры преобразуются (рекурсивно) в массивы и объекты; для других типов, для которых определено приведение к json, применяется эта функция приведения, а для всех остальных выдаётся скалярное значение. Значения всех скалярных типов, кроме числового, логического и NULL, представляются в текстовом виде, в стиле, допустимом для значений json или jsonb. | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
| array_to_json(anyarray [, pretty_bool]) | Возвращает массив в виде массива JSON. Многомерный массив Postgres Pro становится массивом массивов 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_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any") | Формирует массив JSON (возможно, разнородный) из переменного списка аргументов. | json_build_array(1,2,'3',4,5) | [1, 2, "3", 4, 5] |
json_build_object(VARIADIC "any") jsonb_build_object(VARIADIC "any") | Формирует объект JSON из переменного списка аргументов. По соглашению в этом списке перечисляются по очереди ключи и значения. | json_build_object('foo',1,'bar',2) | {"foo": 1, "bar": 2} |
json_object(text[]) jsonb_object(text[]) | Формирует объект JSON из текстового массива. Этот массив должен иметь либо одну размерность с чётным числом элементов (в этом случае они воспринимаются как чередующиеся ключи/значения), либо две размерности и при этом каждый внутренний массив содержит ровно два элемента, которые воспринимаются как пара ключ/значение. | json_object('{a, 1, b, "def", c, 3.5}') json_object('{{a, 1},{b, "def"},{c, 3.5}}') | {"a": "1", "b": "def", "c": "3.5"} |
json_object(keys text[], values text[]) jsonb_object(keys text[], values text[]) | Эта форма 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
| Функция | Тип результата | Описание | Пример | Результат примера |
|---|---|---|---|---|
json_array_length(json) jsonb_array_length(jsonb) | int | Возвращает число элементов во внешнем массиве JSON. | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') | 5 |
json_each(json) jsonb_each(jsonb) | setof key text, value json setof key text, value jsonb | Разворачивает внешний объект JSON в набор пар ключ/значение (key/value). | select * from json_each('{"a":"foo", "b":"bar"}') | key | value -----+------- a | "foo" b | "bar" |
json_each_text(json) jsonb_each_text(jsonb) | 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_extract_path(from_json json, VARIADIC path_elems text[]) jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[]) | json jsonb | Возвращает значение JSON по пути, заданному элементами пути (path_elems) (равнозначно оператору #> operator). | json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} |
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, как text (равнозначно оператору #>>). | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo |
json_object_keys(json) jsonb_object_keys(jsonb) | setof text | Возвращает набор ключей во внешнем объекте JSON. | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys ----------------- f1 f2 |
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) | anyelement | Разворачивает объект из from_json в табличную строку, в которой столбцы соответствуют типу строки, заданному параметром base (см. примечания ниже). | select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') | a | b ---+--- 1 | 2 |
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) | 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_array_elements(json) jsonb_array_elements(jsonb) | setof json setof jsonb | Разворачивает массив JSON в набор значений JSON. | select * from json_array_elements('[1,true, [2,false]]') | value ----------- 1 true [2,false] |
json_array_elements_text(json) jsonb_array_elements_text(jsonb) | setof text | Разворачивает массив JSON в набор значений text. | select * from json_array_elements_text('["foo", "bar"]') | value ----------- foo bar |
json_typeof(json) jsonb_typeof(jsonb) | text | Возвращает тип внешнего значения JSON в виде текстовой строки. Возможные типы: object, array, string, number, boolean и null. | json_typeof('-123.4') | number |
json_to_record(json) jsonb_to_record(jsonb) | 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] | |
json_to_recordset(json) jsonb_to_recordset(jsonb) | 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 | |
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) | json jsonb | Возвращает значение from_json, из которого исключаются все поля объекта, содержащие значения NULL. Другие значения NULL остаются нетронутыми. | json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] |
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) | jsonb | Возвращает значение target, в котором раздел с заданным путём (path) заменяется новым значением (new_value), либо в него добавляется значение new_value, если аргумент create_missing равен true (это значение по умолчанию) и элемент, на который ссылается path, не существует. Как и с операторами, рассчитанными на пути, отрицательные числа в пути (path) обозначают отсчёт от конца массивов JSON. | jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') | [{"f1":[2,3,4],"f2":null},2,null,3] [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] |
jsonb_pretty(from_json jsonb) | text | Возвращает значение 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приведение типов из 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.
| Пред. | Начало | След. |
| XML-функции | Уровень выше | Функции для работы с последовательностями |