Как объединить поле JSONB в древовидную структуру?

У меня есть таблица в Postgres, в которой хранится древовидная структура. Каждый узел имеет поле jsonb: params_diff:

CREATE TABLE tree (id INT, parent_id INT, params_diff JSONB);
INSERT INTO tree VALUES
  (1, NULL, '{ "some_key": "some value" }'::jsonb)
, (2, 1,    '{ "some_key": "other value", "other_key": "smth" }'::jsonb)
, (3, 2,    '{ "other_key": "smth else" }'::jsonb);

Мне нужно выбрать узел по id с дополнительным сгенерированным полем params, которое содержит результат слияния всех params_diff из всей цепочки родителей:

SELECT tree.*, /* some magic here */ AS params FROM tree WHERE id = 3;

 id | parent_id |        params_diff         |                        params
----+-----------+----------------------------+-------------------------------------------------------
  3 |         2 | {"other_key": "smth else"} | {"some_key": "other value", "other_key": "smth else"}

postgresql recursive-query jsonb tree postgresql-9.4
person fey    schedule 21.02.2015    source источник
comment
Непонятно, как именно объединить цепочку родителей. Возьмите только последнее (наиболее удаленное от корня) значение для каждого ключа, чтобы сформировать список? Порядок сортировки?   -  person Erwin Brandstetter    schedule 22.02.2015

Ответы (1)


arrow_upward
3
arrow_downward

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

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

WITH RECURSIVE cte AS (
   SELECT id, parent_id, params_diff, 1 AS lvl
   FROM   tree
   WHERE  id = 3

   UNION ALL
   SELECT t.id, t.parent_id, t.params_diff, c.lvl + 1
   FROM   cte  c
   JOIN   tree t ON t.id = c.parent_id
   )
SELECT id, parent_id, params_diff
    , (SELECT json_object(array_agg(key   ORDER BY lvl)
                        , array_agg(value ORDER BY lvl))::jsonb
        FROM  (
           SELECT key, value
           FROM (
                SELECT DISTINCT ON (key)
                       p.key, p.value, c.lvl
                FROM   cte c, jsonb_each_text(c.params_diff) p
                ORDER  BY p.key, c.lvl
                ) sub1
           ORDER  BY lvl
           ) sub2
       ) AS params

FROM   cte
WHERE  id = 3;

Как?

  1. Пройдитесь по дереву с помощью классического рекурсивного CTE.
  2. Создайте производную таблицу со всеми ключами и значениями с помощью jsonb_each_text() в LATERAL JOIN запомните уровень в пути поиска (lvl).
  3. Use DISTINCT ON to get the "first" (lowest lvl) value for each key. Details:
  4. Отсортируйте и агрегируйте полученные ключи и значения и передайте массивы в json_object() для создания окончательного значения params.

SQL Fiddle (только до стр. 9.3). с json вместо jsonb).

person Erwin Brandstetter    schedule 22.02.2015
comment
Эта функция работает, но она приводит все значения к строкам (включая вложенные объекты и массивы). Есть ли способ сохранить тип значения, а также объединить вложенные объекты? - person fey; 16.03.2015
comment
Хорошо, я сам с этим справился. В основном я изменил вызов json_object на json_object_agg(key, value) и jsonb_each_text на jsonb_each(c.params_diff). - person fey; 16.03.2015