9.15. Функции и операторы JSON
Пред. НаверхГлава 9. Функции и операторыНачало След.

9.15. Функции и операторы JSON

В Таблице 9.43 показаны операторы, позволяющие работать с двумя типами данных JSON (см. Раздел 8.14).

Таблица 9.43. Операторы для типов 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->>23
->>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. В Разделе 9.20 вы также можете узнать об агрегатной функции json_agg, которая агрегирует значения записи в виде JSON, и агрегатной функции json_object_agg, агрегирующей пары значений в объект JSON, а также их аналогах для jsonb, функциях jsonb_agg и jsonb_object_agg.

Некоторые из следующих операторов существуют только для jsonb, как показано в Таблице 9.44. Многие из этих операторов могут быть проиндексированы с помощью классов операторов jsonb. Полное описание проверок на вхождение и существование для jsonb приведено в Подразделе 8.14.3. Как эти операторы могут использоваться для эффективного индексирования данных jsonb, описано в Подразделе 8.14.4.

Таблица 9.44. Дополнительные операторы 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'
-text[]Удаляет множество пар ключ/значение или элементы-строки из левого операнда. Пары ключ/значение выбираются по значению ключа.'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
-integerУдаляет из массива элемент в заданной позиции (отрицательные номера позиций отсчитываются от конца). Выдаёт ошибку, если контейнер верхнего уровня — не массив.'["a", "b"]'::jsonb - 1
#-text[]Удаляет поле или элемент с заданным путём (для массивов JSON отрицательные номера позиций отсчитываются от конца)'["a", {"b":1}]'::jsonb #- '{1,b}'


Примечание

Оператор || соединяет два объекта JSON вместе, формируя объект с объединённым набором ключей и значений, при этом в случае совпадения ключей выбирается значение из второго объекта. С любыми другими аргументами формируется массив JSON: сначала любой аргумент, отличный от массива, преобразуется в одноэлементный массив, а затем два полученных массива соединяются. Эта операция не рекурсивна — объединение производится только на верхнем уровне структуры объекта или массива.

В Таблице 9.45 показаны функции, позволяющие создавать значения типов json и jsonb. (Для типа jsonb нет аналогов функций row_to_json и array_to_json, но практически тот же результат можно получить с помощью to_jsonb.)

Таблица 9.45. Функции для создания 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. Многомерный массив 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_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.46 показаны функции, предназначенные для работы со значениями json и jsonb.

Таблица 9.46. Функции для обработки 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"], "c": {"d": 4, "e": "a b c"}}')
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

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":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)
 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

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_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])

jsonb

Возвращает значение target с вставленным в него новым значением new_value. Если место в target, выбранное путём path, оказывается в массиве JSONB, new_value будет вставлен до (по умолчанию) или после (если параметр insert_after равен true) выбранной позиции. Если место в target, выбранное путём path, оказывается в объекте JSONB, значение new_value будет вставлено в него, только если заданный путь path не существует. Как и с операторами, рассчитанными на пути, отрицательные числа в пути (path) обозначают отсчёт от конца массивов JSON.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)

{"a": [0, "new_value", 1, 2]}

{"a": [0, 1, "new_value", 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[b]_populate_record, json[b]_populate_recordset, json[b]_to_record и json[b]_to_recordset принимают объект JSON или массив объектов и извлекают значения, связанные с ключами, по именам, соответствующим именам столбцов выходного типа. Поля объектов, не соответствующие никаким именам выходных столбцов, игнорируются, а выходные столбцы, для которых не находятся поля в объекте, заполняются значениями NULL. Для преобразования значения JSON в тип SQL выходного столбца последовательно применяются следующие правила:

  • Значение NULL в JSON всегда преобразуется в SQL NULL.

  • Если выходной столбец имеет тип json или jsonb, значение JSON воспроизводится без изменений.

  • Если выходной столбец имеет составной тип (тип кортежа) и значение JSON является объектом JSON, поля этого объекта преобразуются в столбцы типа выходного кортежа в результате рекурсивного применения этих правил.

  • Подобным образом, если выходной столбец имеет тип-массив и значение JSON представляет массив JSON, элементы данного массива преобразуются в элементы выходного массива в результате рекурсивного применения этих правил.

  • Если же значение JSON является строковой константой, содержимое этой строки передаётся входной функции преобразования для типа данных целевого столбца.

  • В противном случае функции преобразования для типа данных целевого столбца передаётся обычное текстовое представление значения JSON.

Хотя в примерах использования перечисленных функций применяются константы, обычно эти функции обращаются к таблицам в предложении FROM, а в качестве аргумента указывается один из столбцов типа json или jsonb. Извлечённые значения затем могут использоваться в других частях запроса, например, в предложениях WHERE и результирующих списках. Извлекая множество значений подобным образом, можно значительно увеличить производительность по сравнению с использованием операторов, работающих с отдельными ключами.

Примечание

В target должны присутствовать все элементы пути, заданного параметром path функций jsonb_set и jsonb_insert, за исключением последнего. Если create_missing равен false, должны присутствовать абсолютно все элементы пути path, переданного функции jsonb_set. Если это условие не выполняется, значение 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.14. XML-функции Начало 9.16. Функции для работы с последовательностями

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.