Блог

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

Как я могу этого добиться?

 {
  id: 999
  name: "Sprinting",
  slug: "sprinting",
  description: "sprinting is fast running",
  parent: {
    id: 2
    name: "Running",
    slug: "running ",
    description: "All plans related to running.",
    parent: {
      id: 1
      name: "Sport",
      slug: "sport ",
      description: null,
    }
  }
}
 

 CREATE TABLE public.categories (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    slug text NOT NULL,
    parent_id integer
);

 

 INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (1, 'Sport', NULL, 'sport', NULL);
INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (2, 'Running', 'All plans related to running.', 'running', 1);
INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (999, 'Sprinting', 'sprinting is fast running', 'sprinting', 2);```
 

демонстрация: db<>скрипка

(Объяснение ниже)

 WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id
    FROM categories 
    WHERE id = 999
    
    UNION
    
    SELECT
        c.id, c.parent_id
    FROM categories c
    JOIN hierarchy h ON h.parent_id = c.id
),
jsonbuilder AS (
    SELECT 
        c.id, 
        h.parent_id,
        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug) as jsondata
    FROM hierarchy h
    JOIN categories c ON c.id = h.id
    WHERE h.parent_id IS NULL
    
    UNION
    
    SELECT
        c.id,
        h.parent_id,
        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug, 'parent', j.jsondata)  
    FROM hierarchy h
    JOIN categories c ON c.id = h.id
    JOIN jsonbuilder j ON j.id = h.parent_id
)
SELECT 
    jsondata
FROM jsonbuilder
WHERE id = 999
 

Как правило, вам нужен рекурсивный запрос для создания вложенных объектов JSON. Наивный подход:

1. Получить запись с id = 999 помощью, создать объект JSON
2. Получить запись с id = parent_id помощью функции записи с 999 помощью ( id = 2 ), создать объект JSON, добавить этот родительский атрибут als к предыдущему объекту.
3. Повторяйте шаг 2, пока родительский файл не станет нулевым

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

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

 WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id
    FROM categories 
    WHERE id = 999
    
    UNION
    
    SELECT
        c.id, c.parent_id
    FROM categories c
    JOIN hierarchy h ON h.parent_id = c.id
)
SELECT * FROM hierarchy
 

Извлечение записи с id = 999 помощью и ее родительского элемента. После этого извлеките запись родительского элемента, его id и его parent_id . Делайте это до parent_id тех пор, пока не будет NULL .

Это дает:

  id | parent_id
--: | --------:
999 |         2
  2 |         1
  1 |      null
 

Теперь у нас есть простой список сопоставлений, который показывает дерево обхода. В чем разница с нашими исходными данными? Если бы ваши данные содержали два или более дочерних элемента для записи id = 1 , мы бы не знали, какой дочерний элемент мы должны использовать, чтобы, наконец, достичь дочернего 999 элемента . Однако в этом результате перечислены только отношения привязки и не будут возвращены какие-либо родственные элементы.

Хорошо имея это, мы можем перемещаться по дереву от самого верхнего элемента, который может быть встроен на самом глубоком уровне:

1. Извлеките запись, у которой нет родительского элемента. Создайте объект JSON из его данных.
2. Извлеките дочерний элемент предыдущей записи. Создайте объект JSON из его данных и вставьте предыдущие данные JSON в качестве родительских.
3. Продолжайте, пока не останется дочернего элемента.

Как это работает?

В этом запросе используются рекурсивные CTE. Первая часть — это начальный запрос, так сказать, первая запись. Вторая часть, часть после UNION , является рекурсивной частью, которая обычно ссылается на само WITH предложение. Это всегда ссылка на предыдущий ход.

Часть JSON — это просто создание объекта JSON с использованием jsonb_build_object(), который принимает произвольное количество значений. Таким образом, мы можем использовать данные текущей записи и дополнительно для parent атрибута уже созданные данные JSON из предыдущего хода.