Рекурсивная фильтрация JSONB с иерархией в Postgres

Использование JSONB в PostgreSQL для хранения сложных иерархических данных предоставляет гибкие возможности для обработки и получения нужной информации. В этом руководстве мы рассмотрим, как реализовать рекурсивную фильтрацию JSONB для того, чтобы извлечь данные, сохраняя оригинальную структуру JSON.

Описание задачи

Мы имеем JSON, который состоит из корневого объекта с секциями и символами (chars). Каждая секция может содержать дополнительные уровни вложенности с данными. Вам нужно фильтровать эти данные так, чтобы в результате мы получили только необходимые элементы, сохранив при этом всю иерархическую структуру.

Исходные данные JSON

Ваш оригинальный JSON выглядит следующим образом:

{
    "id": 1,
    "description": "user description",
    "datas": [
        {"id": "1231", "name": "ABC"},
        {"id": "123s1", "name": "AewqewC"}
    ],
    "sections": [
        {
            "id": "1sd231",
            "description": "description",
            "datas": [
                {"id": "1231", "name": "ABC"},
                {"id": "123s1", "name": "AewqewC"}
            ],
            "chars": [
                {
                    "id": "1sd231",
                    "description": "description",
                    "datas": [
                        {"id": "1231", "name": "ABC"},
                        {"id": "123s1", "name": "AewqewC"}
                    ]
                },
                {
                    "id": "1sqwe231",
                    "description": "description",
                    "datas": [
                        {"id": "12re31", "name": "ABC"},
                        {"id": "12ere3s1", "name": "AewqewC"}
                    ]
                }
            ]
        }
    ]
}

Ожидаемый результат

Ожидается, что после фильтрации JSON вы получите:

[
    {
        "datas": [{"id": "1231", "name": "ABC"}],
        "sections": [
            {
                "datas": [{"id": "1231", "name": "ABC"}],
                "chars": [
                    {
                        "datas": [{"id": "1231", "name": "ABC"}]
                    },
                    {
                        "datas": [{"id": "12re31", "name": "ABC"}]
                    }
                ]
            }
        ]
    }
]

Реализация рекурсивной фильтрации

Для того чтобы выполнить рекурсивную фильтрацию, вы можете использовать функции PostgreSQL, такие как jsonb_each, jsonb_array_elements и jsonb_agg, в сочетании с подзапросами. Ниже представлен пример SQL-запроса, который выполняет данную задачу.

Шаги по выполнению рекурсивной фильтрации:

Создание таблицы и вставка JSONB.

Сначала создайте таблицу для хранения вашего JSONB.

CREATE TABLE json_data (
   id serial PRIMARY KEY,
   data jsonb
);

INSERT INTO json_data (data) VALUES ('{
   "id": 1,
   "description": "user description",
   "datas": [...],  -- вставьте ваш массив данных
   "sections": [...] -- вставьте ваши секции
}');

Функция для рекурсивной фильтрации.

Затем создайте функцию для рекурсивной фильтрации.

CREATE OR REPLACE FUNCTION filter_jsonb(data jsonb, filter_id text)
RETURNS jsonb AS $$
DECLARE
   result jsonb = '{}'::jsonb;
BEGIN
   -- Фильтруем данные
   SELECT jsonb_agg(elem)
   INTO result
   FROM jsonb_array_elements(data->'datas') AS elem
   WHERE elem->>'id' = filter_id;

   -- Рекурсивная фильтрация для секций
   result = jsonb_set(result, '{sections}', (
       SELECT jsonb_agg(
           jsonb_set(sec, '{datas}', (
               SELECT jsonb_agg(d)
               FROM jsonb_array_elements(sec->'datas') AS d
               WHERE d->>'id' = filter_id
           )::jsonb)
       )
       FROM jsonb_array_elements(data->'sections') AS sec
   ));

   -- Рекурсивная фильтрация для chars
   result = jsonb_set(result, '{sections}', (
       SELECT jsonb_agg(
           jsonb_set(sec, '{chars}', (
               SELECT jsonb_agg(char)
               FROM jsonb_array_elements(sec->'chars') AS char
               WHERE char->'datas' @> jsonb_build_array(jsonb_build_object('id', filter_id))
           )::jsonb)
       )
       FROM jsonb_array_elements(data->'sections') AS sec
   ));

   RETURN result;
END;
$$ LANGUAGE plpgsql;

Вызов функции фильтрации.

После создания функции вы можете вызвать её, передав нужный ID.
```
SELECT filter_jsonb((SELECT data FROM json_data WHERE id = 1), '1231') AS filtered_data;
```

Заключение

Использование PostgreSQL для хранения и фильтрации JSONB-данных открывает широкие возможности для разработки современных приложений. Рекурсивная фильтрация позволяет вам извлекать только нужные данные, сохраняя при этом структуру документа. Надеюсь, что приведённые примеры помогут вам успешно реализовать эту функциональность. Если у вас есть дополнительные вопросы или нужна индивидуальная консультация, не стесняйтесь обращаться.

Рекурсивная фильтрация JSONB с иерархией в Postgres

Вопрос или проблема

Ответ или решение