Главы

  1. Введение в документы
  2. Базовые возможности JSON
  3. JSON в таблицах
  4. Индексирование JSON
  5. Ограничения в документах
  6. Язык путей JSONPath
  7. Отчеты и функции
  8. Функции на языке Python
  9. Версионирование и архивация документов
  10. Релевантный поиск

Содержание

В этой главе мы продолжим работу с JSON: познакомимся с языком JSON Path и его реализацией в Postgres. Рассмотрим виды JSON-путей, их функции и операторы, а также индексы, которые их поддерживают. Мы применим язык к документам и выясним, какую пользу он несет.

Прежде чем пускаться в технические детали, расскажем о стандарте JSON Path и о том, какие проблемы он решает.

Последние два десятилетия формат JSON популярен во всех сферах айти. Это привело к тому, что многие базы данных поддерживают его в той или иной мере. Слово “поддерживает” означает, что база не только хранит JSON, но и предлагает средства, чтобы извлечь его подмножество или обновить без передачи всего документа. В Postgres поддержка JSON особенно сильна; в последние годы такие же возможности появились в SQLite, MySQL и SQL Server.

Каждая база предлагает свои операторы и функции для работы с JSON. За прошедшие пять глав читатель убедился, как много их в Postgres. Однако практика показывает, что запомнить их все невозможно, особенно если вы работаете с разными базами данных.

По мере того, как в каждую СУБД добавляли JSON, стало ясно: многие действия можно выразить с помощью декларативного языка или пути. Например, чтобы получить часть документа, система принимает выражение, где описан путь и параметры фильтрации. Нас не интересует, как именно обрабатывается путь; в идеале результат одинаков на всех платформах.

Описанное выше напоминает XPath – язык путей XML. Формат XML, хоть и уступил в популярности JSON, все еще встречается на многих платформах. Причина, по которой появился язык XPath, была та же: привести частные решения к общему знаменателю. Удобно, когда одно и то же выражение XPath работает и в Python, и в Javascript, и в базе данных.

Формат JSON, хоть и отличается от XML, имеет с ним нечто общее – путь. Приведем один и тот же документ в двух форматах:

<?xml version="1.0" encoding="UTF-8"?>
<users>
  <user id="101" age="33">Ivan Petrov</user>
  <user id="135" age="52">Andrey Ivanov</user>
  <user id="399" age="41">Anna Smirnova</user>
</users>

{
  "users": [
    {
      "id": 101,
      "age": 33,
      "name": "Ivan Petrov"
    },
    {
      "id": 135,
      "age": 52,
      "name": "Andrey Ivanov"
    },
    {
      "id": 399,
      "age": 41,
      "name": "Anna Smirnova"
    }
  ]
}

Чтобы добраться до значения “Ivan Petrov” в XML, укажем следующий путь XPath:

/users/user[1]/text()

На языке JSON Path выглядит иначе:

$.users[0].name

В обоих случаях синтаксис отличается, но смысл одинаков. Путь – это цепочка элементов с разделителем (косой чертой или точкой). Путь может содержать функции или фильтры, и позже мы рассмотрим эти возможности.

Можно сказать, что документ состоит из значений и путей, которые к ним ведут. Массив пользователей легко выразить списком пар:

users.0.id   101
users.0.age  33
users.0.name Ivan Petrov

users.1.id   135
users.1.age  41
users.1.name Andrey Ivanov

users.2.id   399
users.2.age  41
users.2.name Anna Smirnova

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

Идея JSON Path в том, чтобы на любой платформе путь к элементу выглядел одинаково. В настоящий момент пути JSON работают в Postgres, Microsoft SQL Server, Oracle, а также документных базах вроде OpenSearch. У каждой реализации свои особенности, однако в целом они похожи.

В случае с Postgres у языка JSON Path есть прародитель под названием Jsquery. Просим читателя не путать его с библиотекой jQuery для JavaScript: разница во второй букве s. Jsquery решает ту же задачу: упрощенно описывает путь к элементам, которые нужно извлечь. Изначально Jsquery спонсировался фирмой Wargaming в период активной работы над Глобальной Картой. Это пошаговая игра с обширным состоянием, которое включает сведения о кланах, игроках, технике, положении фишек на карте и многое другое. Состояние игры хранилось в виде JSON в базе данных. Приходилось часто запрашивать его подмножество: какие игроки остались в игре, какие локации захвачены, какие свободны и так далее. Чтобы не выгружать документ целиком, в Wargaming пользовались расширением Jsquery, разработанным в PostgresPro.

Сегодня это расширение встроено в поставку PostgresPro; в обычном Postgres его устанавливают отдельно. Далее по тексту мы обсуждаем только классический JSON Path, потому что он доступен в Postgres по умолчанию. Разобравшись с JSON Path, читатель без труда освоит Jsquery.

Знакомство с JSON Path

Для дальнейших экспериментов подготовим таблицу demo с документом из примера выше:

create table demo(doc jsonb not null);

insert into demo values ($$
{
  "users": [
    {
      "id": 101,
      "age": 33,
      "name": "Ivan Petrov"
    },
    {
      "id": 135,
      "age": 52,
      "name": "Andrey Ivanov"
    },
    {
      "id": 399,
      "age": 41,
      "name": "Anna Smirnova"
    }
  ]
}
$$::jsonb);

Чтобы получить имя первого пользователя, выполним запрос:

select
    jsonb_path_query_first(doc, '$.users.name') as node
from demo;

┌───────────────┐
│     node      │
├───────────────┤
│ "Ivan Petrov" │
└───────────────┘

Разберемся, что здесь происходит. Знак доллара в пути означает ссылку на вершину документа. Каждый следующий уровень отделяется точкой. Лексемы между точками совпадают с именами ключей. Если ключ содержит только символы a-z, A-Z, цифры и подчеркивание, их указывают как есть. В противному случае лексемы экранируют – обрамляют двойными кавычками. Например, в языках семейства Lisp приняты ключи с дефисами: вместо user_id пишут user-id. Подобные ключи записываются так: $.user."user-id". Ключ user не нуждается в кавычках, однако их наличие не будет ошибкой.

Оговоримся, что путь $.users.name охватывает имена всех пользователей, однако функция jsonb_path_query_first выбирает только первый из них (что отражает суффикс _first). Возможны другие подходы, когда элементы возвращаются в виде массива jsonb. Такой результат производит функция jsonb_path_query_array:

select
    jsonb_path_query_array(doc, '$.users.name') as node
from demo;

┌───────────────────────────────────────────────────┐
│                       node                        │
├───────────────────────────────────────────────────┤
│ ["Ivan Petrov", "Andrey Ivanov", "Anna Smirnova"] │
└───────────────────────────────────────────────────┘

Функция jsonb_path_query возвращает таблицу с одним столбцом jsonb; ее можно соединить с другой таблицей, выбрать из нее данные как из подзапроса и так далее. С другими функциями семейства jsonb_path_ читатель ознакомится в документации.

select
    jsonb_path_query(doc, '$.users.name') as node
from demo;

┌─────────────────┐
│      node       │
├─────────────────┤
│ "Ivan Petrov"   │
│ "Andrey Ivanov" │
│ "Anna Smirnova" │
└─────────────────┘

Виды путей

Одна из особенностей JSON Path в следующем: путь к элементу может быть строгим или нет. В английском языке для этого используют термины strict и lax. Последнее переводится как “расслабленный” или “вялый”. Будем называть такой путь нестрогим. Тип пути указывают в самом начале. Это строгий путь:

strict $.path.to.node

, а это нет:

lax $.path.to.node

По умолчанию путь считается нестрогим (lax).

Разница между ними в том, что происходит, когда путь выходит за границы документа. Предположим, по ошибке мы составили путь, которого нет в документе. Пусть это будет $.hello.test. Если задать ему тип strict, при попытке извлечь элемент получим ошибку:

select
    jsonb_path_query(doc, 'strict $.hello.test') as node
from demo;

-- ERROR:  JSON object does not contain key "hello"

Если же пусть нестрогий (lax), поиск элемента окончится, как только мы вышли за границы документа. При этом ошибки не будет, и функция вернет null. Уточним – именно стандартный NULL, а не null-значение JSON:

select
    jsonb_path_query(doc, 'lax $.hello.test') as node
from demo;

-- null

Синтаксис field[index] указывает на элемент массива, при этом index должен быть неотрицательным числом. Отрицательные индексы вроде -1 для доступа с конца массива не работают. Звездочка на месте индекса означает все элементы массива; подобный путь описывает уже не один, а множество элементов.

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

select
    jsonb_path_query(doc, 'strict $.users[*].id') as node
from demo;

┌──────┐
│ node │
├──────┤
│ 101  │
│ 135  │
│ 399  │
└──────┘

Аналогичный результат вернет нестрогий путь lax $.users.id или просто $.users.id. В некоторых случаях строгость бывает важна, и ниже мы рассмотрим, когда именно.

Раскрытие массивов

Как видно из последнего примера, нестрогие пути автоматически раскрывают массивы. Вместо users[*] мы указали users; Postgres определил, что это массив и применил следующее поле id ко всем элементам. Запись удобна краткостью: не нужно явно указывать, где объекты, а где массивы. Объекты и массивы могут чередоваться произвольно с одним исключением: нелья, чтобы два массива следовали один за другим. Другими словами, нестрогий путь не работает с массивом массивов. Приведем слегка надуманный пример, где строгий путь сработает:

select jsonb_path_query($$
{
  "matrix": [
    [{"id": 1}, {"id": 2}, {"id": 3}],
    [{"id": 4}, {"id": 5}, {"id": 6}],
    [{"id": 7}, {"id": 8}, {"id": 9}]
  ]
}
$$, 'strict $.matrix[*][*].id') as x;

┌───┐
│ x │
├───┤
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘

, а нестрогий – нет:

select jsonb_path_query($$
{
  "matrix": [
    [{"id": 1}, {"id": 2}, {"id": 3}],
    [{"id": 4}, {"id": 5}, {"id": 6}],
    [{"id": 7}, {"id": 8}, {"id": 9}]
  ]
}
$$, 'lax $.matrix.id') as x;

-- (0 rows)

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

Какой вид пути использовать – строгий или нет – зависит от того, кто его передает. Когда один сервис обращается к другому и между ними доверительные отношения, подойдут нестрогие пути. Если же путь передает клиент, проверьте его на глубину и доступные варианты, а также предварите словом strict, чтобы исключить случайное раскрытие массивов.

Индекс с поддержкой путей

В главе 4 мы говорили об индексации всего json-документа. Напомним, мы использовали индекс GIN с опцией jsonb_ops (если ее опустить, она применится по умолчанию):

create index if not exists
idx_applications_doc_gin_jsonb_ops
on applications using gin
(doc jsonb_ops);

После этого оператор вхождения @> подхватывал индекс, что мы и видели в плане запроса.

Если указать в свойствах индекса jsonb_path_ops, он строится слегка по-иному. Наиболее важное отличие в том, что новый индекс поддерживает операторы @? и @@. Эти операторы принимают пути JSON, чтобы проверить их наличие или сравнить значение документа с переданным. Удалим прошлый индекс и построим новый:

drop index if exists idx_applications_doc_gin_jsonb_ops;

create index if not exists
idx_applications_doc_gin_jsonb_path_ops
on applications using gin
(doc jsonb_path_ops);

Оператор @? проверяет, есть ли в документе указанный путь. Результат будет истиной или ложью. Путь существует, если найдется хотя бы один элемент, который ему подходит (напомним, путь может описывать несколько элементов). Чтобы это продемонстрировать, добавим к произвольной заявке вложенное поле:

update applications
set doc['foo']['bar']['baz'] = to_jsonb(123)
where id = '00000000-0000-0000-0000-000000000070';

Если искать документы по новому пути, именно эту заявку мы и получим:

select id from applications
where doc @? 'lax $.foo.bar.baz'
limit 10;

┌─[ RECORD 1 ]──────────────────────────────┐
│ id │ 00000000-0000-0000-0000-000000000070 │
└────┴──────────────────────────────────────┘

Оператор @@ действует схожим образом, но принимает не путь, а предикат. В нем путь сравнивается с каким-то значением. Типичный пример – найти документ, где вложенное значение равно заданному. План подтверждает, что новый индекс используется:

explain analyze
select id from applications
where doc @@ 'lax $.departments.users.email == "user_65@test.com" '
limit 10;

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                         QUERY PLAN                                                                          │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit  (cost=90.20..126.64 rows=10 width=16) (actual time=1.703..1.747 rows=10 loops=1)                                                                     │
│   ->  Bitmap Heap Scan on applications  (cost=90.20..36325.58 rows=9943 width=16) (actual time=1.702..1.744 rows=10 loops=1)                                │
│         Recheck Cond: (doc @@ '($."departments"."users"."email" == "user_65@test.com")'::jsonpath)                                                          │
│         Heap Blocks: exact=10                                                                                                                               │
│         ->  Bitmap Index Scan on idx_applications_doc_gin_jsonb_path_ops  (cost=0.00..87.71 rows=9943 width=0) (actual time=0.901..0.902 rows=3000 loops=1) │
│               Index Cond: (doc @@ '($."departments"."users"."email" == "user_65@test.com")'::jsonpath)                                                      │
│ Planning Time: 0.113 ms                                                                                                                                     │
│ Execution Time: 1.770 ms                                                                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

select id from applications
where doc @@ 'lax $.created_by.name == "User 0999"'
limit 10;

select id from applications
where doc @@ 'lax $.application_id == 1513 '
limit 10;

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

Значение не обязательно должно быть скаляром. Можно сравнить два пути одного документа, что открывает интересные возможности. Предположим, в заявке указаны ее автор и пользователи, которые над ней работали. Требуется найти документы, где автор встречается среди исполнителей. Вот как это сделать:

select id from applications
where doc @@ '$.created_by.id == $.departments.users.id'
limit 10;

Автор признает, что операторы @? и @@ легко перепутать и получить странные результаты. Остается только запомнить: @@ принимает путь с предикатом (равно, больше, меньше и так далее), а @? – обычный путь, возможно с подзапросом. О подзапросах мы поговорим ближе к концу главы.

Методы и операторы JSON Path

Кроме операторов, JSON Path предлагает несколько удобных функций. Большинство из них выглядит как методы, потому что вызываются из последнего элемента пути через точку. Например, метод .size() вернет число элементов массива. С его помощью находят массивы, чей размер превышает заданный.

Один из возможных сценариев .size() – найти заявки, над над которыми работало более двух человек. Во время генерации мы указали по два сотрудника в каждом департаменте. Добавим к произвольной заявке еще одного пользователя:

update applications
set doc['departments'][1]['users'][2] = $$
{
  "id": "00000000-0000-0000-0000-000000009999",
  "name": "User 9999",
  "role": "manager",
  "email": "user_9999@test.com"
}
$$::jsonb
where id = '00000000-0000-0000-0000-000000000065';

Теперь условие ...users.size() > 2 вернет заявку 65:

select id from applications
where doc['departments'] @@ '$.users.size() > 2 '
limit 10;

┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000000065 │
└──────────────────────────────────────┘

С сожалением отметим, что .size() работает с массивом, но не объектом. Чтобы узнать размер объекта (число ключей в нем), его приводят к массиву методом .keyvalue(). Каждый его элемент – объект с ключами key, value и id (порядковым номером):

select jsonb_path_query($$
{
  "users": {
    "101": {"name": "Ivan"},
    "123": {"name": "John"},
    "523": {"name": "Johann"}
  }
}
$$::jsonb, '$.users.keyvalue()') as node;

┌───────────────────────────────────────────────────────┐
│                         node                          │
├───────────────────────────────────────────────────────┤
│ {"id": 20, "key": "101", "value": {"name": "Ivan"}}   │
│ {"id": 20, "key": "123", "value": {"name": "John"}}   │
│ {"id": 20, "key": "523", "value": {"name": "Johann"}} │
└───────────────────────────────────────────────────────┘

Итоговый размер объекта мы узнаем цепочкой методов $.users.keyvalue().size().

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

select id from applications
where doc @@ '$.departments.users.name like_regex "^user 12[0-9]{1}$" flag "i"'
limit 10;

┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000008129 │
│ 00000000-0000-0000-0000-000000009100 │
│ 00000000-0000-0000-0000-000000009101 │
│ 00000000-0000-0000-0000-000000009102 │
│ 00000000-0000-0000-0000-000000009103 │
│ 00000000-0000-0000-0000-000000009104 │
│ 00000000-0000-0000-0000-000000009105 │
│ 00000000-0000-0000-0000-000000009106 │
│ 00000000-0000-0000-0000-000000009107 │
│ 00000000-0000-0000-0000-000000009108 │
└──────────────────────────────────────┘

Флаги регулярного выражения – это подсказки о том, как его интерпретировать. Наиболее полезны из них “i” и “q”. Первый (insensitive) означает, что выражение игнорирует регистр символов. Как правило, регистр действительно не важен: нам интересны все варианты записи (строчными и прописными буквами), поэтому флаг добавляют к выражению. Второй флаг q (quoted) означает, что служебные символы читаются как есть – у них нет особой семантики. Так, с флагом q символы \d означают буквально обратную косую черту и букву d, а не любую цифру. Флаг quoted необходим, когда выражение приходит со стороны и ему нельзя доверять.

Огорчим читателя тем, что оператор like_regex не использует индекс. В общих случаях индекс работает с операторами равенства и сравнения, а именно <, <=, ==, =>, >. Более сложные операторы вроде like_regex, увы, провоцируют линейный обход таблицы. В случае с заявками запрос будет особо долгим, потому что их миллион. Таким образом like_regex не снимает проблему поиска по вхождению строки, которую мы решали при помощи триграммного индекса. Используйте этот оператор только для служебных нужд.

JSON Path поддерживает и другие операторы и методы. Предлагаем читателю ознакомиться с ними в документации.

Пути со звездочками

Еще одна сильная сторона JSON Path заключается в следующем: если мы не знаем один из ключей, его заменяют звездочкой. Предположим, документ содержит словарь, где ключи – коды пользователей; коды заранее неизвестны. Чтобы составить путь до имени пользователя, на месте кода ставят звездочку:

select jsonb_path_query($$
{
  "users": {
    "101": {"name": "Ivan"},
    "123": {"name": "John"},
    "523": {"name": "Johann"}
  }
}
$$::jsonb, '$.users.*.name') as name;

┌──────────┐
│   name   │
├──────────┤
│ "Ivan"   │
│ "John"   │
│ "Johann" │
└──────────┘

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

select jsonb_path_query($$
{
  "users": {
    "101": {"name": "Ivan", "friends": {"991": {"name": "Anna"}, "523": {"name": "Kirill"}}},
    "123": {"name": "John", "friends": {"353": {"name": "Gleb"}, "566": {"name": "Petr"}}},
    "523": {"name": "Johann", "friends": {"834": {"name": "Marina"}, "235": {"name": "Galina"}}}
  }
}
$$::jsonb, '$.users.**.name') as name;

┌──────────┐
│   name   │
├──────────┤
│ "Ivan"   │
│ "Kirill" │
│ "Anna"   │
│ "John"   │
│ "Gleb"   │
│ "Petr"   │
│ "Johann" │
│ "Galina" │
│ "Marina" │
└──────────┘

Двойную звездочку не используют в промышленном коде, потому что подобный поиск дорогой. Кроме того, путь может раскрыть данные, не предназначенные клиентам. Чаще всего двойную звездочку применяют для администрирования. Предположим, кто-то из разработчиков пожаловался, что документ содержит дату в неверном формате: 01-02-2025. Какому документу она принадлежала и в какой его части находилось, разработчик уже не помнит. Вот как выявить проблемный документ:

$.**.created_at == "01-02-2025"

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

$.**.id == "6c92590b-2daa-487f-a50d-434d34185015"

Составление путей в коде

До сих пор мы обходили вопрос: как составить выражение JSON Path в приложении? Выше мы писали запросы так, словно путь и значение известны заранее. На практике это не так: то и другое приходят от сторонних сервисов или от пользователей.

Приведем код на Python для построения пути. Выбор языка объясняется тем, что он знаком большинству читателей, и код легко переписать на других языках:

import json
import re

dеf needs_quoting(lexem):
    return re.search("[^a-zA-Z0-9_]+", lexem)

dеf maybe_quote(lexem):
    if needs_quoting(lexem):
        return '"%s"' % (lexem, )
    else:
        return lexem

dеf json_path(path, op, value):
    return "$.%s %s %s" % (
        ".".join([
            maybe_quote(part) for part in path
        ]),
        op,
        json.dumps(value)
    )

Функция needs_quoting проверяет, нуждается ли элемент пути в экранировании – обрамлении кавычками. Если в элемент содержит символы, отличные от латинских букв, цифр и подчеркивания, то результат положительный. Функция maybe_quote экранирует элемент, если это необходимо (отсюда префикс maybe). Финальная функция json_path принимает список элементов пути, оператор и значение, которое приводится к JSON. Примеры вызова:

print(json_path(["departments", "users", "email"], "==", "test@test.com"))
# $.departments.users.email == "test@test.com"

print(json_path(["test", "full-name", "is-active?"], "==", True))
# $.test."full-name"."is-active?" == true

Мы построили выражение JSON Path, и нужно передать его в запрос. Здесь возможны два варианта. Первый – передать путь параметром как обычную строку:

query = """
  select id, doc from applications
  where doc @@ ?
  limit 100
"""

path = '$.departments.users.email == "user_65@test.com"'

conn.execute(query, params=(path, ))

Этот способ защищает вас от инъекций и манипуляций с кавычками. Недостаток в том, что некоторые системы пишут в логи запросы без параметров. В результате вы увидите только условие where doc @@ ?, а что было на месте вопроса – неизвестно.

Второй способ в том, чтобы внедрить путь прямо в тело SQL. Чтобы обезопасить себя от инъекций, окружите путь тегами вида $xxx$, где xxx – случайное выражение. Ниже для ясности мы выбрали тег $jsonpath$. Напомним, что внутри двойных долларов любые символы читаются как есть.

select id, doc from applications
where doc @@ $jsonpath$$.departments.users.email == "user_65@test.com"$jsonpath$
limit 10;

Можно расширить вашу ORM, чтобы код ниже

Applications.objects.filter(doc__atat=(["attrs", "application_id"], "==", 12345))

разворачивался в следующее условие:

where doc @@ '$.attrs.application_id == 12345'

В случае с Django вам поможет следующий шаблон:

from django.db.models import Lookup
from django.db.models import Field

class JsonbAtAt(Lookup):
    lookup_name = "atat"

    dеf as_sql(self, compiler, connection):
        ...
        return "%s @@ %s" % (column, path), params

Field.register_lookup(JsonbAtAt)

На месте многоточия должен быть код, который строит путь. Скорее всего, он будет опираться на функции json_path и другие, приведенные выше.

Подзапросы

Рассмотрим еще одну возможность JSON Path, которую до сих пор избегали – подзапросы. Чтобы материал был понятней, подберем такой документ и требование, которое нельзя выполнить без подзапросов.

Предположим, заявка содержит пользователей, которые над ней работали. Дополнительно учитывается роль пользователя. Вполне может быть так, что в одной заявке человек выступил автором, во второй – менеджером, в третьей – аналитиком и так далее. Приведем фрагмент заявки, который хранит эти данные:

"departments": [
  {
    "id": "00000000-0000-0000-0000-000000000005",
    "code": "dep_5",
    "name": "Department 5",
    "users": [
      {
        "id": "00000000-0000-0000-0000-000000000055",
        "name": "User 55",
        "role": "support",
        "email": "user_55@test.com"
      },
      {
        "id": "00000000-0000-0000-0000-000000000065",
        "name": "User 65",
        "role": "decision-maker",
        "email": "user_65@test.com"
      }
    ]
  },
  {
    "id": "00000000-0000-0000-0000-000000000015",
    "code": "dep_15",
    "name": "Department 15",
    "users": [
      {
        "id": "00000000-0000-0000-0000-000000000075",
        "name": "User 75",
        "role": "support",
        "email": "user_75@test.com"
      },
      {
        "id": "00000000-0000-0000-0000-000000000035",
        "name": "User 35",
        "role": "decision-maker",
        "email": "user_35@test.com"
      }
    ]
  }
]

Требование в том, чтобы найти документы, над которыми работал пользователь с кодом X в роли Y, например 65 и support.

Попытаемся решить задачу в лоб. Составим запрос с двумя условиями: одно с кодом пользователя, второе – с ролью:

select id from applications
where
    doc @@ '$.departments.users.id == "00000000-0000-0000-0000-000000000065"'
and doc @@ '$.departments.users.role == "support"'
limit 10;

┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000001045 │
│ 00000000-0000-0000-0000-000000002045 │
│ 00000000-0000-0000-0000-000000003045 │
│ 00000000-0000-0000-0000-000000003055 │
│ 00000000-0000-0000-0000-000000006045 │
│ 00000000-0000-0000-0000-000000007055 │
│ 00000000-0000-0000-0000-000000011065 │
│ 00000000-0000-0000-0000-000000013045 │
│ 00000000-0000-0000-0000-000000016045 │
│ 00000000-0000-0000-0000-000000021055 │
└──────────────────────────────────────┘

Какие-то данные мы получили, однако верны ли они? Изучим первую заявку с номером 1045, точнее ее поле departments. Вот что в ней содержится:

"departments": [
  {
    "id": "00000000-0000-0000-0000-000000000005",
    "code": "dep_5",
    "name": "Department 5",
    "users": [
      {
        "id": "00000000-0000-0000-0000-000000000055",
        "name": "User 55",
        "role": "support",
        "email": "user_55@test.com"
      },
      {
        "id": "00000000-0000-0000-0000-000000000065",
        "name": "User 65",
        "role": "decision-maker",
        "email": "user_65@test.com"
      }
    ]
  }
]

Обратите внимание: у пользователя 65 роль decision-maker, а не support. Роль support, напротив, принадлежит пользователю 55. Почему же заявка попала в результат?

Вспомним, что оператор @@ проверяет, есть ли в документе значение, путь которому совпадает с шаблоном. Рассмотрим первое условие:

doc @@ '$.departments.users.id == "00000000-0000-0000-0000-000000000065"'

Такое значение есть, и оно находится по пути departments[0].users[1].id. Теперь второе условие:

doc @@ '$.departments.users.role == "support"'

Этот путь тоже встречается в документе и равен departments[0].users[0].role. Оба условия были истинны, а остальное очевидно: объединение AND было тоже истинным, поэтому мы получили документ. С точки зрения Postgres ошибок не было; проблема в том, что мы неверно запросили данные.

Сделать это нужно по-другому, в два этапа. На первом шаге составляют минимальный общий путь. Для обоих путей $.departments.users.id и $.departments.users.role он равен $.departments.users.

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

Алгоритм, что мы описали, называется подзапросом JSON Path – не путайте его с подзапросами SQL. Вот как его выразить:

select id from applications
where
    doc @? '$.departments.users ? (@.id == "00000000-0000-0000-0000-000000000065" && @.role == "support")'
limit 10;

После первичного пути идет вопросительный знак. Он выступает конвейером: элементы слева от него передаются подзапросу справа. Обратите внимание, что в справа вместо доллара используется символ @ – он означает текущую позицию в документе. В подзапросе можно по-прежнему сослаться на корень документа при помощи доллара, но как правило этого не требуется.

Также обратите внимание, что для путей с подзапросом используется оператор @?, а не @@. Это весьма неочевидное требование. Дело в том, что @@ служит только для путей с предикатом, а подзапрос предикатом не является, даже если содержит сравнение и другие логические операторы.

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

  1. отфильтровать департаменты по коду;
  2. выбрать их пользователей;
  3. отфильтровать пользователей по номеру и роли.

Путь на языке JSONPath:

select id from applications
where
    doc @? '$.departments[*] ? (@.code == "dep_5") .users ? (@.id == "00000000-0000-0000-0000-000000000065" && @.role == "support")'
limit 10;

Проверим план на миллионе заявок – индекс используется, но запрос занимает больше времени, потому что выражение сложное:

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                 QUERY PLAN                                                                                  │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit  (cost=137.69..174.12 rows=10 width=16) (actual time=5.307..5.412 rows=10 loops=1)                                                                                    │
│   ->  Bitmap Heap Scan on applications  (cost=137.69..36533.88 rows=9990 width=16) (actual time=5.303..5.407 rows=10 loops=1)                                               │
│         Recheck Cond: (doc @? '$."departments"[*]?(@."code" == "dep_5")."users"?(@."id" == "00000000-0000-0000-0000-000000000065" && @."role" == "support")'::jsonpath)     │
│         Rows Removed by Index Recheck: 21                                                                                                                                   │
│         Heap Blocks: exact=31                                                                                                                                               │
│         ->  Bitmap Index Scan on idx_applications_doc_gin_jsonb_path_ops  (cost=0.00..135.19 rows=9990 width=0) (actual time=5.199..5.199 rows=470 loops=1)                 │
│               Index Cond: (doc @? '$."departments"[*]?(@."code" == "dep_5")."users"?(@."id" == "00000000-0000-0000-0000-000000000065" && @."role" == "support")'::jsonpath) │
│ Planning Time: 0.170 ms                                                                                                                                                     │
│ Execution Time: 5.830 ms                                                                                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

Итак, мы рассмотрели язык JSON Path и его основные возможности, предикаты, методы, подзапросы. Мы коснулись лишь малой части того, что описано в документации; предлагаем читателю ознакомиться с ней на официальном сайте. С другой стороны, мы заострили внимание на тех неочевидных моментах, которые даже в документации описаны скупо. Автор надеется, что JSON Path поможет вам в работе со сложными документами. В следующей главе мы рассмотрим еще одну технику, основанную на этом языке.

← Глава 5. Ограничения в документах