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

Следующие выражения json (или jsonb) являются допустимыми:

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
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.

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

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

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

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

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

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

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

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

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
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 документированы в Раздел 9.16.

GIN индексы могут быть использованы для эффективного поиска ключей или пар ключ/значение, встречающихся в большом количестве документов jsonb (датумов). Предоставляются два класса “операторов” 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, в столбце jsonb с именем jdoc. Если на этом столбце создан индекс GIN, запросы, подобные следующим, могут использовать индекс:

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

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

-- Find documents in which the key "tags" contains key or array element "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).

Другой подход к запросам - это использование вложенности, например:

-- Find documents in which the key "tags" contains array element "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 извлекает предложения вида accessors_chain = constant из шаблона jsonpath и выполняет поиск по индексу на основе ключей и значений, указанных в этих предложениях. Цепочка аксессоров может включать .key, [*], и [index]. Операторный класс 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 редко представляет большой интерес, но для полноты он следующий:

Object > Array > Boolean > Number > String > null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

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

key-1, value-1, key-2 ...

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

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

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

element-1, element-2 ...

Примитивные значения JSON сравниваются с использованием тех же правил сравнения, что и для базового типа данных Tantor SE-1C. Строки сравниваются с использованием сортировки базы данных по умолчанию.

Тип данных jsonb поддерживает выражения индексов в стиле массива для извлечения и изменения элементов. Вложенные значения можно указать путем цепочки выражений индексов, следуя тем же правилам, что и аргумент path в функции 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, процесс обхода не может продолжаться, поэтому возникает ошибка и транзакция прерывается.

Пример синтаксиса индексации:

-- Extract object value by key
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extract nested object value by key path
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extract array element by index
SELECT ('[1, "2", null]'::jsonb)[1];

-- Update object value by key. Note the quotes around '1': the assigned
-- value must be of the jsonb type as well
UPDATE table_name SET jsonb_field['key'] = '1';

-- This will raise an error if any record's jsonb_field['a']['b'] is something
-- other than an object. For example, the value {"a": 1} has a numeric value
-- of the key 'a'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Filter records using a WHERE clause with subscripting. Since the result of
-- subscripting is jsonb, the value we compare it against must also be jsonb.
-- The double quotes make "value" also a valid jsonb string.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

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

-- Where jsonb_field was NULL, it is now {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Where jsonb_field was NULL, it is now [1]
UPDATE table_name SET jsonb_field[0] = '1';

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

-- Where jsonb_field was [], it is now [null, null, 2];
-- where jsonb_field was [0], it is now [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

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

-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Where jsonb_field was [], it is now [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 в Tantor SE-1C для эффективного запроса данных JSON. Он предоставляет двоичное представление разобранного выражения SQL/JSON path, которое указывает элементы, которые должны быть извлечены из данных JSON для дальнейшей обработки с помощью функций запроса SQL/JSON.

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

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

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

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

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