Совет дня №10

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

Проблема в следующем. В базе лежит огромный JSON с каталогом услуг. Его схема примерно такая:

DOC = {
 "id": ID,
 "code": CODE,
 "children": [DOC]
}

Головной элемент содержит некоторые поля и children, в которых список элементов. У каждого элемента свои children и так далее. Вложенность значительна: иная ветка достигает 30 элементов.

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

Есть, однако, простой способ привести JSON к плоскому виду – рекурсивный запрос:

prepare flatten as
with recursive step as (
    select
        jt.*,
        0 as level
    from
        json_table($1::jsonb, '$' columns(
            id int4 path '$.id',
            code text path '$.code',
            children jsonb path '$.children'
    )) as jt
    UNION ALL
    select
        jt.*,
        step.level + 1 as level
    from
        step,
        json_table(step.children, '$[*]' columns(
            id int4 path '$.id',
            code text path '$.code',
            children jsonb path '$.children'
    )) as jt)
select id, code, level from step
order by level;

Первая часть производит строку с полями id, code и children. В нем – JSON-массив вложенных элементов. Рекурсивная часть берет каждый элемент и делает то же самое: расщепляет его на id, code и другие элементы. Все это колбасится, пока вложенные элементы не исчерпаются. А теперь – пример:

execute flatten($${
    "id": 1,
    "code": "Product A",
    "children": [
        {"id": 2, "code": "Product B", "children": [
            {"id": 5, "code": "Product E", "children": [
                {"id": 6, "code": "Product F", "children": []}
            ]}
        ]},
        {"id": 3, "code": "Product C", "children": [
            {"id": 7, "code": "Product F", "children": [
                {"id": 8, "code": "Product G", "children": []}
            ]}

        ]},
        {"id": 4, "code": "Product D", "children": [
            {"id": 5, "code": "Product E", "children": [
                {"id": 9, "code": "Product H", "children": []}
            ]}
        ]}
]}$$);

Результат:

┌────┬───────────┬───────┐
│ id │   code    │ level │
├────┼───────────┼───────┤
│  1 │ Product A │     0 │
│  2 │ Product B │     1 │
│  3 │ Product C │     1 │
│  4 │ Product D │     1 │
│  5 │ Product E │     2 │
│  7 │ Product F │     2 │
│  5 │ Product E │     2 │
│  6 │ Product F │     3 │
│  8 │ Product G │     3 │
│  9 │ Product H │     3 │
└────┴───────────┴───────┘

Такой обход очень полезен. Быстро выяснилось, что плоские продукты нужны аналитикам, которые не владеют Кложей или другим ЯП, чтобы их распутать. Я написал им запрос, и они чуть не заплакали от счастья. Во-вторых, продукты изменяются редко, поэтому из запроса легко сделать материализованную вьюху и обновлять ее каждый день. Для аналитиков это обычная таблица, которую можно джойнить в запросах.

Еще одно уточнение — код выше является чистым с точки зрения ФП. В нем нет побочных эффектов, например обращения к диску. Такая функция может быть вызвана параллельно для многих документов.