Синтаксис ввода/вывода для типов данных 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, приведен в Раздел 2.6.16.

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

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

Проверка включение является важной функциональной возможностью jsonb. Для типа json отсутствует аналогичный набор средств для json типа данных. Операция проверки на включение определяет, содержит ли один jsonb документ другой документ. Данные примеры возвращают значение true, за исключением отмеченных случаев:

-- Простые скалярные/примитивные значения содержат только идентичные значения:
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 также имеет существование оператор, представляющий собой разновидность проверки на включение: он проверяет, присутствует ли строка (переданная как значение текст ) в качестве ключа объекта или элемента массива на верхнем уровне значения jsonb . Следующие примеры возвращают true, за исключением специально отмеченных случаев:

-- Строка существует как элемент массива:
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 лучше подходят для проверки включения или существования, чем массивы, при наличии большого количества ключей или элементов, поскольку, в отличие от массивов, они внутренне оптимизированы для поиска и не требуют линейного сканирования.

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, описаны в Раздел 2.6.16.

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

Стандартный класс операторов GIN для jsonb поддерживает запросы с операторами проверки существования ключа ?, ?| и ?&, оператором включения @>, а также jsonpath операторами соответствия @? и @@. (Подробное описание семантики этих операторов см. в Таблица 2.6.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, в jsonb столбце с именем jdoc. Если для данного столбца создан GIN-индекс, запросы, подобные приведенному ниже, могут использовать этот индекс:

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

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

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

Тем не менее, при надлежащем использовании индексов по выражениям вышеуказанный запрос может использовать индекс. Если поиск по конкретным элементам внутри ключа "теги" выполняется часто, создание индекса следующим образом может быть целесообразным:

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

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

Другой подход к выполнению запросов заключается в использовании оператора включения, например:

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

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

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-индекс извлекает выражения вида accessors_chain == constant из jsonpath шаблона и выполняет поиск по индексу на основе ключей и значений, указанных в данных выражениях. Цепочка аксессоров может включать .ключ, [*], и [index] операторы доступа. jsonb_ops класс операторов также поддерживает .* и .** аксессоры, но jsonb_path_ops класс операторов — нет.

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

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

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

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

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

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

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

за тем исключением, что (по историческим причинам) пустой массив верхнего уровня при сортировке считается меньше, чем null. Объекты с равным количеством пар сравниваются в следующем порядке:

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

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

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

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

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

Простые значения JSON сравниваются по тем же правилам, что и значения соответствующего базового Digital Q.DataBase типа данных. Строки сравниваются с использованием правила сортировки (collation), установленного в базе данных по умолчанию.

Тип данных jsonb jsonb поддерживает выражения с использованием индексов (субскриптов) в стиле массивов для извлечения и изменения элементов. Вложенные значения могут быть указаны путем последовательного указания индексов (субскриптов) согласно тем же правилам, что и для функции путь аргумент в jsonb_set функции. Если jsonb значение является массивом, числовые индексы начинаются с нуля, а отрицательные целые числа отсчитываются в обратном порядке от последнего элемента массива. Выражения срезов не поддерживаются. Результат выражения индексирования всегда имеет тип данных jsonb.

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

Пример синтаксиса использования индексов (subscripting):

-- Извлечение значения объекта по ключу
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, значение, с которым производится сравнение, также должно иметь тип jsonb.
-- Двойные кавычки делают "value" допустимой строкой jsonb.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

jsonb присваивание через обращение по индексу обрабатывает некоторые граничные случаи иначе, чем jsonb_set. Когда исходное jsonb значение равно NULL, присваивание через обращение по индексу будет выполнено так, как если бы это было пустое значение 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они будут дополнены значениями 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';

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

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

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

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

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

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

Числовые литералы в выражениях SQL/JSON path следуют правилам JavaScript, которые в некоторых деталях отличаются как от SQL, так и от JSON. Например, SQL/JSON path допускает .1 и 1., которые недопустимы в JSON. Поддерживаются недесятичные целочисленные литералы и символы подчеркивания в качестве разделителей, например, 1_000_000, 0x1EEE_FFFF, 0o273, 0b100101. В выражениях SQL/JSON path (а также в JavaScript, но не в самом SQL) непосредственно после префикса основания системы счисления не должен следовать разделитель в виде символа подчеркивания.

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

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

Для получения подробных сведений об использовании jsonpath выражений в функциях запросов SQL/JSON см. Раздел 2.6.16.2.