8.14. Типы JSON

Типы JSON предназначены для хранения данных JSON (JavaScript Object Notation, Запись объекта JavaScript) согласно стандарту RFC 7159. Такие данные можно хранить и в типе text, но типы JSON лучше тем, что проверяют, соответствует ли вводимое значение формату JSON. Для работы с ними есть также несколько специальных функций и операторов; см. Раздел 9.16.

В PostgreSQL имеются два типа для хранения данных JSON: json и jsonb. Для реализации эффективного механизма запросов к этим типам данных в PostgreSQL также имеется тип jsonpath, описанный в Подразделе 8.14.7.

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

Так как тип json сохраняет точную копию введённого текста, он сохраняет семантически незначащие пробелы между элементами, а также порядок ключей в JSON-объектах. И если JSON-объект внутри содержит повторяющиеся ключи, этот тип сохранит все пары ключ/значение. (Функции обработки будут считать действительной последнюю пару.) Тип jsonb, напротив, не сохраняет пробелы, порядок ключей и значения с дублирующимися ключами. Если во входных данных оказываются дублирующиеся ключи, сохраняется только последнее значение.

Для большинства приложений предпочтительнее хранить данные JSON в типе jsonb (если нет особых противопоказаний, например важны прежние предположения о порядке ключей объектов).

В RFC 7159 говорится, что строки JSON должны быть представлены в кодировке UTF-8. Поэтому данные JSON не будут полностью соответствовать спецификации, если кодировка базы данных не UTF-8. При этом нельзя будет вставить в JSON символы, непредставимые в кодировке сервера, и наоборот, допустимыми будут символы, представимые в кодировке сервера, но не в UTF-8.

RFC 7159 разрешает включать в строки JSON спецпоследовательности Unicode в виде \uXXXX. В функцию ввода для типа json эти спецпоследовательности допускаются вне зависимости от кодировки базы данных, и проверяется только правильность их синтаксиса (за \u должны следовать четыре шестнадцатеричных цифры). Однако функция ввода для типа jsonb более строгая: она не допускает спецпоследовательности Unicode для символов, которые не могут быть представлены в кодировке базы. Тип jsonb также не принимает \u0000 (так как это значение не может быть представлено в типе text PostgreSQL) и требует, чтобы суррогатные пары Unicode использовались для представления символов вне основной многоязыковой плоскости (BMP) правильно. Корректные спецпоследовательности Unicode преобразуются для хранения в один соответствующий символ (это подразумевает сворачивание суррогатных пар в один символ).

Примечание

Многие из функций обработки JSON, описанные в Разделе 9.16, преобразуют спецпоследовательности Unicode в обычные символы, поэтому могут выдавать подобные ошибки, даже если им на вход поступает тип json, а не jsonb. То, что функция ввода в тип json не производит этих проверок, можно считать историческим артефактом, хотя это и позволяет просто сохранять (но не обрабатывать) в JSON спецкоды Unicode в базе данных с кодировкой, в которой представленные таким образом символы отсутствуют.

При преобразовании вводимого текста JSON в тип jsonb, примитивные типы, описанные в RFC 7159, по сути отображаются в собственные типы PostgreSQL как показано в Таблице 8.23. Таким образом, к содержимому типа jsonb предъявляются некоторые дополнительные требования, продиктованные ограничениями представления нижележащего типа данных, которые не распространяются ни на тип json, ни на формат JSON вообще. В частности, тип jsonb не принимает числа, выходящие за диапазон типа данных PostgreSQL numeric, тогда как с json такого ограничения нет. Такие ограничения, накладываемые реализацией, допускаются согласно RFC 7159. Однако на практике такие проблемы более вероятны в других реализациях, так как обычно примитивный тип JSON number представляется в виде числа с плавающей точкой двойной точности IEEE 754 (что RFC 7159 явно признаёт и допускает). При использовании JSON в качестве формата обмена данными с такими системами следует учитывать риски потери точности чисел, хранившихся в PostgreSQL.

И напротив, как показано в таблице, есть некоторые ограничения в формате ввода примитивных типов JSON, не актуальные для соответствующих типов PostgreSQL.

Таблица 8.23. Примитивные типы JSON и соответствующие им типы PostgreSQL

Примитивный тип JSONТип PostgreSQLЗамечания
stringtext\u0000 не допускается как спецпоследовательность Unicode, представляющая символ, который отсутствует в кодировке базы
numbernumericЗначения NaN и infinity не допускаются
booleanbooleanДопускаются только варианты true и false (в нижнем регистре)
null(нет)NULL в SQL имеет другой смысл

8.14.1. Синтаксис вводимых и выводимых значений JSON

Синтаксис ввода/вывода типов данных JSON соответствует стандарту RFC 7159.

Примеры допустимых выражений с типом json (или jsonb):

-- Простое скалярное/примитивное значение
-- Простыми значениями могут быть числа, строки в кавычках, true, false или null
SELECT '5'::json;

-- Массив из нуля и более элементов (элементы могут быть разных типов)
SELECT '[1, 2, "foo", null]'::json;

-- Объект, содержащий пары ключей и значений
-- Заметьте, что ключи объектов — это всегда строки в кавычках
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Массивы и объекты могут вкладываться произвольным образом
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

Как было сказано ранее, когда значение JSON вводится и затем выводится без дополнительной обработки, тип json выводит тот же текст, что поступил на вход, а jsonb не сохраняет семантически незначащие детали, такие как пробелы. Например, посмотрите на эти различия:

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

Первая семантически незначимая деталь, заслуживающая внимания: с jsonb числа выводятся по правилам нижележащего типа numeric. На практике это означает, что числа, заданные в записи с E, будут выведены без неё, например:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

Однако как видно из этого примера, jsonb сохраняет конечные нули дробного числа, хотя они и не имеют семантической значимости, в частности для проверки на равенство.

Список встроенных функций и операторов, позволяющих создавать и обрабатывать значения JSON, приведён в Разделе 9.16.

8.14.2. Проектирование документов JSON

Представлять данные в JSON можно гораздо более гибко, чем в традиционной реляционной модели данных, что очень привлекательно там, где нет жёстких условий. И оба этих подхода вполне могут сосуществовать и дополнять друг друга в одном приложении. Однако даже для приложений, которым нужна максимальная гибкость, рекомендуется, чтобы документы JSON имели некоторую фиксированную структуру. Эта структура обычно не навязывается жёстко (хотя можно декларативно диктовать некоторые бизнес-правила), но когда она предсказуема, становится гораздо проще писать запросы, которые извлекают полезные данные из набора «документов» (информации) в таблице.

Данные JSON, как и данные любых других типов, хранящиеся в таблицах, находятся под контролем механизма параллельного доступа. Хотя хранить большие документы вполне возможно, не забывайте, что при любом изменении устанавливается блокировка всей строки (на уровне строки). Поэтому для оптимизации блокировок транзакций, изменяющих данные, стоит ограничить размер документов JSON разумными пределами. В идеале каждый документ JSON должен собой представлять атомарный информационный блок, который, согласно бизнес-логике, нельзя разделить на меньшие, индивидуально изменяемые блоки.

8.14.3. Проверки на вхождение и существование jsonb

Проверка вхождения — важная особенность типа jsonb, не имеющая аналога для типа json. Эта проверка определяет, входит ли один документ jsonb в другой. В следующих примерах возвращается истинное значение (кроме упомянутых исключений):

-- Простые скалярные/примитивные значения включают только одно идентичное значение:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- Массив с правой стороны входит в массив слева:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Порядок элементов в массиве не важен, поэтому это условие тоже выполняется:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- А повторяющиеся элементы массива не имеют значения:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- Объект с одной парой справа входит в объект слева:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- Массив справа не считается входящим в
-- массив слева, хотя в последний и вложен подобный массив:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- выдаёт false

-- Но если добавить уровень вложенности, проверка на вхождение выполняется:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Аналогично, это вхождением не считается:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- выдаёт false

-- Ключ с пустым объектом на верхнем уровне входит в объект с таким ключом:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

Общий принцип этой проверки в том, что входящий объект должен соответствовать объекту, содержащему его, по структуре и данным, возможно, после исключения из содержащего объекта лишних элементов массива или пар ключ/значение. Но помните, что порядок элементов массива для проверки на вхождение не имеет значения, а повторяющиеся элементы массива считаются только один раз.

В качестве особого исключения для требования идентичности структур, массив может содержать примитивное значение:

-- В этот массив входит примитивное строковое значение:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- Это исключение действует только в одну сторону -- здесь вхождения нет:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- выдаёт false

Для типа jsonb введён также оператор существования, который является вариацией на тему вхождения: он проверяет, является ли строка (заданная в виде значения text) ключом объекта или элементом массива на верхнем уровне значения jsonb. В следующих примерах возвращается истинное значение (кроме упомянутых исключений):

-- Строка существует в качестве элемента массива:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- Строка существует в качестве ключа объекта:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Значения объектов не рассматриваются:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- выдаёт false

-- Как и вхождение, существование определяется на верхнем уровне:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- выдаёт false

-- Строка считается существующей, если она соответствует примитивной строке JSON:
SELECT '"foo"'::jsonb ? 'foo';

Объекты JSON для проверок на существование и вхождение со множеством ключей или элементов подходят больше, чем массивы, так как, в отличие от массивов, они внутри оптимизируются для поиска, и поиск элемента не будет линейным.

Подсказка

Так как вхождение в JSON проверяется с учётом вложенности, правильно написанный запрос может заменить явную выборку внутренних объектов. Например, предположим, что у нас есть столбец doc, содержащий объекты на верхнем уровне, и большинство этих объектов содержит поля tags с массивами вложенных объектов. Данный запрос найдёт записи, в которых вложенные объекты содержат ключи "term":"paris" и "term":"food", и при этом пропустит такие ключи, находящиеся вне массива tags:

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

Этого же результата можно добиться, например, так:

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

Но данный подход менее гибкий и часто также менее эффективный.

С другой стороны, оператор существования JSON не учитывает вложенность: он будет искать заданный ключ или элемент массива только на верхнем уровне значения JSON.

Различные операторы вхождения и существования, а также все другие операторы и функции для работы с JSON документированы в Разделе 9.16.

8.14.4. Индексация jsonb

Для эффективного поиска ключей или пар ключ/значение в большом количестве документов jsonb можно успешно применять индексы GIN. Для этого предоставляются два «класса операторов» GIN, предлагающие выбор между производительностью и гибкостью.

Класс операторов GIN по умолчанию для jsonb поддерживает запросы с операторами существования ключа (?, ?| и ?&), оператором включения (@>) и операторами соответствия для jsonpath (@? и @@). (Подробнее семантика, реализуемая этими операторами, описана в Таблице 9.46.) Пример создания индекса с этим классом операторов:

CREATE INDEX idxgin ON api USING GIN (jdoc);

Дополнительный класс операторов GIN jsonb_path_ops не поддерживает операторы существования ключа, но поддерживает @>, @? и @@. Пример создания индекса с этим классом операторов:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

Рассмотрим пример таблицы, в которой хранятся документы JSON, получаемые от сторонней веб-службы, с документированным определением схемы. Типичный документ:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

Мы сохраняем эти документы в таблице api, в столбце jdoc типа jsonb. Если по этому столбцу создаётся GIN-индекс, он может применяться в подобных запросах:

-- Найти документы, в которых ключ "company" имеет значение "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

Однако в следующих запросах он не будет использоваться, потому что, несмотря на то, что оператор ? — индексируемый, он применяется не к индексированному столбцу jdoc непосредственно:

-- Найти документы, в которых ключ "tags" содержит ключ или элемент массива "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

И всё же, правильно применяя индексы выражений, в этом запросе можно задействовать индекс. Если запрос определённых элементов в ключе "tags" выполняется часто, вероятно стоит определить такой индекс:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

Теперь предложение WHERE jdoc -> 'tags' ? 'qui' будет выполняться как применение индексируемого оператора ? к индексируемому выражению jdoc -> 'tags'. (Подробнее об индексах выражений можно узнать в Разделе 11.7.)

Ещё один подход к использованию проверок на существование:

-- Найти документы, в которых ключ "tags" содержит элемент массива "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

Этот запрос может задействовать простой GIN-индекс по столбцу jdoc. Но заметьте, что такой индекс будет хранить копии всех ключей и значений в поле jdoc, тогда как индекс выражения из предыдущего примера хранит только данные внутри объекта с ключом tags. Хотя подход с простым индексом гораздо более гибкий (так как он поддерживает запросы по любому ключу), индексы конкретных выражений скорее всего будут меньше и быстрее, чем простые индексы.

Также индексы GIN поддерживают операторы @? и @@, которые сопоставляют jsonpath с данными. Примеры:

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

Для этих операторов индекс GIN извлекает из jsonpath предложения вида цепочка_обращения = константа и выполняет поиск по ключам и значениям из этих предложений. Цепочка обращения может включать указания обращения .ключ, [*] и [индекс]. Класс операторов jsonb_ops также поддерживает указания .* и .**, которые не поддерживает класс операторов jsonb_path_ops.

Класс операторов jsonb_path_ops поддерживает только запросы с операторами @>, @? и @@, но он значительно производительнее класса по умолчанию jsonb_ops. Индекс jsonb_path_ops обычно гораздо меньше индекса jsonb_ops для тех же данных и более точен при поиске, особенно если запросы обращаются к ключам, часто встречающимся в данных. Таким образом, с ним операции поиска выполняются гораздо эффективнее, чем с классом операторов по умолчанию.

Техническое различие между GIN-индексами jsonb_ops и jsonb_path_ops состоит в том, что для первых создаются независимые элементы индекса для каждого ключа/значения в данных, тогда как для вторых создаются элементы только для значений. [7] По сути, каждый элемент индекса jsonb_path_ops представляет собой хеш значения и ключа(ей), приводящего к нему; например, при индексации {"foo": {"bar": "baz"}} будет создан один элемент индекса с хешем, рассчитанным по всем трём значениям: foo, bar и baz. Таким образом, проверка на вхождение этой структуры будет использовать крайне точный поиск по индексу, но определить, является ли foo ключом, с помощью такого индекса нельзя. С другой стороны, индекс jsonb_ops создаст три отдельных элемента индекса, представляющих foo, bar и baz по отдельности; для выполнения проверки на вхождение будут проверены строки таблицы, содержащие все эти три значения. Хотя GIN-индексы позволяют вычислить AND довольно эффективно, такой поиск всё же будет менее точным и более медленным, чем равнозначный поиск с jsonb_path_ops, особенно если любое одно из этих трёх значений содержится в большом количестве строк.

Недостаток класса jsonb_path_ops заключается в том, что он не учитывает в индексе структуры JSON, не содержащие никаких значений {"a": {}}. Для поиска по документам, содержащих такие структуры, потребуется выполнить полное сканирование индекса, что довольно долго, поэтому jsonb_path_ops не очень подходит для приложений, часто выполняющих такие запросы.

Тип jsonb также поддерживает индексы btree и hash. Они полезны, только если требуется проверять равенство JSON-документов в целом. Порядок сортировки btree для типа jsonb редко имеет большое значение, но для полноты он приводится ниже:

Объект > Массив > Логическое значение > Число > Строка > Null

Объект с n парами > Объект с n - 1 парами

Массив с n элементами > Массив с n - 1 элементами

Объекты с равным количеством пар сравниваются в таком порядке:

ключ-1, значение-1, ключ-2 ...

Заметьте, что ключи объектов сравниваются согласно порядку при хранении; в частности, из-за того, что короткие ключи хранятся перед длинными, результаты могут оказаться несколько не интуитивными:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Массивы с равным числом элементом упорядочиваются аналогично:

элемент-1, элемент-2 ...

Примитивные значения JSON сравниваются по тем же правилам сравнения, что и нижележащие типы данных PostgreSQL. Строки сравниваются с учётом порядка сортировки по умолчанию в текущей базе данных.

8.14.5. Обращение по индексу к элементам jsonb

Тип данных jsonb поддерживает извлечение и изменение элементов в стиле обращения к элементам массива. Указывать на вложенные значения можно, задавая цепочку обращений к элементам, при этом будут действовать правила использования аргумента path функции jsonb_set. Если значение jsonb является массивом, числовые индексы начинаются с нуля, а отрицательные целые числа отсчитывают элементы c конца массива к началу. Обращения к срезам массивов не поддерживаются. Результат обращения по индексу всегда имеет тип jsonb.

Используя операцию обращения по индексу в предложении SET оператора UPDATE, можно изменять значения jsonb. Путь такого обращения должен быть «проходимым» для всех указанных значений, если они существуют. Например, путь val['a']['b']['c'] можно пройти полностью до c, если val, val['a'] и val['a']['b'] — объекты. Если же значение val['a'] или val['a']['b'] не определено, будет создан пустой объект, заполняемый по мере необходимости. Однако, если значение собственно val или любое из промежуточных значений существует и является не объектом, а, например, строкой, числом или сущностью jsonb null, пройти этот путь невозможно, поэтому возникает ошибка и транзакция прерывается.

Пример синтаксиса обращения по индексу:

-- Извлечь значение объекта по ключу
SELECT ('{"a": 1}'::jsonb)['a'];

-- Извлечь значение вложенного объекта по пути ключа
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Извлечь элемент массива по индексу
SELECT ('[1, "2", null]'::jsonb)[1];

-- Изменить значение объекта по ключу. Обратите внимание на апострофы вокруг '1': присваиваемое
-- значение также должно быть типа jsonb
 UPDATE table_name SET jsonb_field['key'] = '1';

-- Это вызовет ошибку, если jsonb_field['a']['b'] в какой-либо записи является не объектом.
-- Например, в {"a": 1} ключу 'a' соответствует числовое значение
-- ключа 'а'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Отфильтровать записи предложением WHERE с обращением по ключу. Поскольку результат
-- обращения по ключу будет иметь тип jsonb, такой же тип должно иметь сравниваемое с ним значение.
-- Двойные кавычки добавлены, чтобы строка "value" стала допустимой строкой jsonb.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

Присваивание jsonb при обращении по индексу в некоторых особых случаях работает не так, как с функцией jsonb_set. Когда исходное значение jsonbNULL, присваивание при обращении по ключу будет работать, как будто это значение — пустое значение JSON (типа массив или объект, в зависимости от типа ключа):

-- Там, где поле jsonb_field было NULL, оно станет {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Там, где поле jsonb_field было NULL, оно станет [1]
UPDATE table_name SET jsonb_field[0] = '1';

Если индекс указан для массива, содержащего недостаточно элементов, в него будут добавляться элементы со значением NULL до тех пор, пока индекс не станет достижимым и пока не станет возможным задать значение.

-- Там, где поле jsonb_field было [], оно станет [null, null, 2];
-- там, где поле jsonb_field было [0], оно станет [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

Значение jsonb будет принимать присваивания по несуществующим путям, если последний существующий элемент, который нужно пройти, — объект или массив, в зависимости от соответствующего ключа (элемент, на который указывает последний компонент пути, не проходится и может быть любым). Вложенные структуры массивов и объектов будут созданы и для первых дополнятся элементами null до нужной позиции (заданной в пути), в которую может быть помещено присваиваемое значение.

-- Там, где поле jsonb_field было {}, оно станет {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Там, где поле jsonb_field было [], оно станет [null, {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';

8.14.6. Трансформации

Для различных процедурных языков представлены дополнительные расширения, реализующие трансформации для типа jsonb.

Расширения для PL/Perl называются jsonb_plperl и jsonb_plperlu. Когда они используются, значения jsonb отображаются в соответствующие структуры Perl: массивы, хеши или скаляры.

Расширение для PL/Python называется jsonb_plpython3u. Когда оно используется, значения jsonb отображаются в соответствующие структуры Python: словари, списки или скаляры.

Из этих расширений «доверенным» считается jsonb_plperl, то есть его могут устанавливать обычные пользователи, имеющие право CREATE в текущей базе данных. Остальные расширения могут устанавливать только суперпользователи.

8.14.7. Тип jsonpath

Тип jsonpath предназначен для реализации поддержки языка путей SQL/JSON в PostgreSQL, позволяющего эффективно выполнять запросы к данным JSON. Он обеспечивает двоичное представление разобранного выражения пути SQL/JSON, определяющего, какие элементы должны извлекаться из данных JSON для дальнейшей обработки в функциях SQL/JSON.

Семантика предикатов и операторов языка путей SQL/JSON в целом соответствует SQL. В то же время, чтобы с данными JSON можно было оперировать естественным образом, в синтаксисе путей SQL/JSON приняты некоторые соглашения JavaScript:

  • Точка (.) применяется для доступа к члену объекта.

  • Квадратные скобки ([]) применяются для обращения к массиву.

  • Элементы массивов в SQL/JSON нумеруются с 0, тогда как обычные массивы SQL — с 1.

Выражение пути SQL/JSON обычно записывается в SQL-запросе в виде символьной константы SQL, и поэтому должно заключаться в апострофы, а любой апостроф, который нужно заключить в это значение, должен дублироваться (см. Подраздел 4.1.2.1). Нередко строковые константы требуется использовать и внутри выражений путей. На такие константы распространяются соглашения JavaScript/ECMAScript: они должны заключаться в двойные кавычки, а для представления символов, которые сложно ввести иначе, используются спецпоследовательности с обратной косой чертой. В частности, символ двойных кавычек внутри строковой константы записывается как \", а собственно обратная косая черта как \\. В число других спецпоследовательностей, воспринимаемых в строках JSON, входят: \b, \f, \n, \r, \t, \v, выражающие различные управляющие символы ASCII, а также \uNNNN, выражающая символ Unicode кодом в виде четырёх шестнадцатеричных цифр. Синтаксис спецпоследовательностей допускает также две записи, выходящие за рамки JSON: \xNN, выражающая символ кодом в виде только двух шестнадцатеричных цифр, и \u{N...}, позволяющая для записи кода символа использовать от 1 до 6 шестнадцатеричных цифр.

Выражение пути состоит из последовательности элементов пути, которые могут быть следующими:

  • Константы примитивных типов JSON: текст Unicode, числа и значения true, false и null.

  • Переменные пути перечислены в Таблице 8.24.

  • Операторы обращения перечислены в Таблице 8.25.

  • Операторы и методы jsonpath перечислены в Подразделе 9.16.2.2.

  • Скобки, применяющиеся для образования выражений фильтра и изменения порядка вычисления пути.

Более подробно использование выражений jsonpath с функциями запросов SQL/JSON описано в Подразделе 9.16.2.

Таблица 8.24. Переменные jsonpath

ПеременнаяОписание
$Переменная, представляющая значение JSON, фигурирующее в запросе (элемент контекста).
$varnameИменованная переменная. Её значение может быть задано в параметре vars, который принимают различные функции обработки JSON; подробности в Таблице 9.48.
@Переменная, представляющая результат вычисления пути в выражениях фильтров.

Таблица 8.25. Операторы обращения в jsonpath

Оператор обращенияОписание

.ключ

."$имя_переменной"

Оператор обращения к члену объекта, выбираемому по заданному ключу. Если имя ключа совпадает с именем какой-либо переменной, начинающимся с $, или не соответствует действующим в JavaScript требованиям к идентификаторам, оно должно заключаться в двойные кавычки и таким образом представляться как строковая константа.

.*

Оператор обращения по звёздочке, который возвращает значения всех членов, находящихся на верхнем уровне объекта.

.**

Рекурсивный оператор обращения по звёздочке, который проходит по всем уровням иерархии JSON текущего объекта и возвращает все значения членов, вне зависимости от их уровня вложенности. Это реализованное в PostgreSQL расширение стандарта SQL/JSON.

.**{уровень}

.**{начальный_уровень to конечный_уровень}

Этот оператор подобен .**, но выбирает только указанные уровни иерархии JSON. Уровни вложенности задаются целыми числами, при этом нулевой уровень соответствует текущему объекту. Для обращения к самому нижнему уровню вложенности можно использовать ключевое слово last. Это реализованное в PostgreSQL расширение стандарта SQL/JSON.

[селектор, ...]

Оператор обращения к элементу массива. Селектор может задаваться в двух формах: индекс или начальный_индекс to конечный_индекс. Первая форма выбирает единственный элемент по индексу. Вторая форма выбирает срез массива по двум индексам, включающий крайние элементы, соответствущие значениям начальный_индекс и конечный_индекс.

Задаваемый индекс может быть целочисленным значением или выражением, возвращающим единственное число, которое автоматически приводится к целому. Индекс 0 соответствует первому элементу массива. Также в качестве индекса принимается ключевое слово last, обозначающее индекс последнего элемента массива, что полезно при обработке массивов неизвестной длины.

[*]

Оператор обращения к элементам массива по звёздочке, возвращающий все элементы массива.




[7] Поэтому понятие «значение» включает и элементы массивов, хотя в терминологии JSON иногда элементы массивов считаются отличными от значений внутри объектов.