How to merge JSONB field in a tree structure?

Question

I have a table in Postgres which stores a tree structure. Each node has a jsonb field: 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);

The thing I need is to select a node by id with additional generated params field which contains the result of merging all params_diff from the whole parents chain:

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"}

Answer 1

Generally, a recursive CTE can do the job. Example:

• Use table alias in another query to traverse a tree

We just need a more magic to decompose, process and re-assemble the JSON result. I am assuming from your example, that you want each key once only, with the first value in the search path (bottom-up):

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;

How?

1. Walk the tree with a classic recursive CTE.
2. Create a derived table with all keys and values with jsonb_each_text() in a LATERAL JOIN, remember the level in the search path (lvl).
3. Use DISTINCT ON to get the "first" (lowest lvl) value for each key. Details:
   • Select first row in each GROUP BY group?
4. Sort and aggregate resulting keys and values and feed the arrays to json_object() to build the final params value.

SQL Fiddle (only as far as pg 9.3 can go with json instead of jsonb).