В Таблице 9.44 перечислены операторы, которые можно использовать с типами данных JSON (см. Раздел 8.14).

Стандартные операторы сравнения, приведённые в Таблице 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.45. Многие из этих операторов могут быть проиндексированы с помощью классов операторов jsonb. Полное описание проверок на вхождение и существование для jsonb приведено в Подразделе 8.14.3. Как эти операторы могут использоваться для эффективного индексирования данных jsonb, описано в Подразделе 8.14.4.

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

В Таблице 9.47 показаны функции, предназначенные для работы со значениями json и jsonb.

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

 key | value
-----+-------
 a   | foo
 b   | bar

 json_object_keys
-----------------
 f1
 f2

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

 a | b
---+---
 1 | 2
 3 | 4

   value
-----------
 1
 true
 [2,false]

   value
-----------
 foo
 bar

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

 a |  b
---+-----
 1 | foo
 2 |

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

 jsonb_path_query
------------------
 2
 3
 4

Выражения путей SQL/JSON определяют элементы, извлекаемые из данных JSON, подобно тому, как выражения XPath позволяют обращаться из SQL к XML. В Postgres Pro выражения путей представляются в виде типа данных jsonpath и могут использовать любые элементы, описанные в Подразделе 8.14.6.

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

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

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

Например, предположим, что у вас есть данные JSON, полученные от GPS-трекера, которые вы хотели бы проанализировать:

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

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

'$.track.segments'

Если извлекаемая сущность является элементом массива, этот массив нужно развернуть, воспользовавшись оператором [*]. Например, следующий путь выдаст координаты концов всех имеющихся сегментов треков:

'$.track.segments[*].location'

Чтобы получить координаты только первого сегмента, можно задать соответствующий индекс в операторе обращения []. Заметьте, что индексы массивов в SQL/JSON отсчитываются с 0:

'$.track.segments[0].location'

Результат каждого шага вычисления выражения может быть обработан операторами и методами jsonpath, перечисленными в Подразделе 9.15.2.3. Перед именем метода должна стоять точка. Например, так можно получить размер массива:

'$.track.segments.size()'

Дополнительные примеры использования операторов и методов jsonpath в выражениях пути приведены в Подразделе 9.15.2.3.

Определяя путь, также можно использовать выражения фильтра, работающие подобно предложению WHERE в SQL. Выражение фильтра начинается со знака вопроса и содержит условие в скобках:

? (условие)

Выражение фильтра должно указываться сразу после шага вычисления пути, к которому оно применяется. Результаты этого шага проходят через фильтр, и на выходе остаются только те элементы, которые удовлетворяют заданному условию. В SQL/JSON действует троичная логика, то есть результатом выражения может быть true, false или unknown (неизвестность). Значение unknown играет ту же роль, что и NULL в SQL, и может быть проверено предикатом is unknown. На последующих шагах вычисления пути будут обрабатываться только те элементы, для которых выражение фильтра выдало true.

Функции и операторы, которые можно использовать в выражениях фильтра, перечислены в Таблице 9.49. Результат вычисления пути, проходящий через фильтр, представляется переменной @. Чтобы обратиться к элементу JSON, находящемуся на более низком уровне вложенности, добавьте после @ один или несколько операторов обращения.

Предположим, что вы хотите получить все показатели пульса, превышающие 130. Это можно сделать с помощью следующего выражения:

'$.track.segments[*].HR ? (@ > 130)'

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

'$.track.segments[*] ? (@.HR > 130)."start time"'

Можно также использовать несколько выражений фильтра на одном уровне, когда это требуется. Например, следующее выражение выбирает все сегменты с определёнными координатами и высоким показателем пульса:

'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'

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

'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'

Можно также вкладывать выражения фильтра одно в другое:

'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'

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

Реализация языка путей SQL/JSON в Postgres Pro имеет следующие отличия от стандарта SQL/JSON:

'lax $.track.segments.location'

'strict $.track.segments[*].location'

lax $.**.HR

strict $.**.HR

'$[*] ? (@ like_regex "^[aeiou]" flag "i")'

$.* ? (@ like_regex "^\\d+$")