PostgreSQL recursive rows to JSONB map

Question

This question is best explained with an example. So, if you have 2 tables category and event in PostgreSQL as follows: -

create table category (
    id integer primary key,
    type varchar(255),
    label varchar (255),
    parent_id integer
);
insert into category (id, type,           label,    parent_id) 
              values (1,  'organisation', 'Google', null),
                     (2,  'product',      'Gmail',  1),
                     (3,  'organisation', 'Apple',  null),
                     (4,  'product',      'iPhone', 3),
                     (5,  'product',      'Mac',    3);

create table event (
    id integer primary key,
    name varchar (255),
    category_id integer
);
insert into event (id, name, category_id) 
           values (1,  'add', 4),
                  (2,  'delete', 5),
                  (3,  'update', 2); 

As you can see, the category table is quite dynamic and a hierarchy of categories can be defined.

What I'm trying to achieve is selecting entries of the event table and join it with the categories but flatten it to a JSON structure. I can illustrate using the following query: -

select e.*, 
       jsonb_build_object( 
           c1.type, c1.label,
           c2.type, c2.label
       ) as categories
  from event e
  left join category c2 on c2.id = e.category_id
  left join category c1 on c1.id = c2.parent_id

This will return: -

+----+--------+-------------+------------------------------------------------+
| id | name   | category_id | categories                                     |
+----+--------+-------------+------------------------------------------------+
| 1  | add    | 4           | {"organisation": "Apple", "product": "iPhone"} |
| 2  | delete | 5           | {"organisation": "Apple", "product": "Mac"}    |
| 3  | update | 2           | {"organisation": "Google", "product": "Gmail"} |
+----+--------+-------------+------------------------------------------------+

However, this approach only works when an event.category_id column references a child category which has precisely 1 parent (2 levels). Really what I'm looking for is to generate categories, regardless if (a) it doesn't have a parent category (i.e. a 1 level category) OR (b) has more than 1 parent (e.g. 3 levels). For example, if I add the following rows to the event and category tables: -

insert into category (id, type,           label,    parent_id) 
              values (6,  'module',       'Mobile', 5),    /* has 2 parents  */
                     (7,  'organisation', 'AirBNB', null); /* has no parents */

insert into event (id, name, category_id) 
           values (4,  'event1', 6),
                  (5,  'event2', 7);

... and run the query from above it will return: -

ERROR: argument 1: key must not be null
SQL state: 

My gut feeling is a recursive CTE could solve this.

---

Update 1

 create or replace function category_array(category_parent_id int) returns setof jsonb as $$
     select case
         when count(x) > 0 then
              jsonb_agg(f.x) || jsonb_build_object (
                  c.type, c.label
              )
         else jsonb_build_object (
                  c.type, c.label
              )
      end as category_pair
 from category c
 left join category_array (c.parent_id) as f(x) on true
where c.id = category_parent_id
group by c.id, c.type, c.label;

$$ language sql;

... and call using this SQL ...

select *, 
       category_array(category_id) 
  from event;

... will return the following ...

+----+--------+-------------+--------------------------------------------------------------------------+
| id | name   | category_id | categories                                                               |
+----+--------+-------------+--------------------------------------------------------------------------+
| 1  | add    | 4           |  [{"organisation": "Apple"}, {"product": "iPhone"}]                      |
| 2  | delete | 5           |  [{"organisation": "Apple"}, {"product": "Mac"}]                         |
| 3  | update | 2           |  [{"organisation": "Google"}, {"product": "Gmail"}]                      |
| 4  | event1 | 6           |  [[{"organisation": "Apple"}, {"product": "Mac"}], {"module": "Mobile"}] |
| 5  | event2 | 7           |  {"organisation": "AirBNB"}                                              |
+----+--------+-------------+--------------------------------------------------------------------------+

Pretty close but not quite there just yet!

Answer 1

Use the concatenation operator || to build cumulative jsonb objects:

with recursive events as (
    select 
        e.id, e.name, e.category_id as parent_id,
        jsonb_build_object(c.type, c.label) as categories
    from event e
    left join category c on c.id = e.category_id
union all
    select 
        e.id, e.name, c.parent_id,
        categories || jsonb_build_object(c.type, c.label)
    from events e
    join category c on c.id = e.parent_id
)

select id, name, categories 
from events
where parent_id is null
order by id;

Note that the query is not protected against circular dependencies, so you need to be sure that all paths in the table are ended with nulls.

Test the query on DbFiddle.

---

Alternative solution:

create or replace function get_categories(int) 
returns jsonb language sql as $$
    select case
        when parent_id is null then
            jsonb_build_object (type, label)
        else 
            jsonb_build_object (type, label) || get_categories(parent_id)
    end as categories
    from category
    where id = $1
$$;

select id, name, get_categories(category_id)
from event
order by id;

DbFiddle.