Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table shopunit(
  id uuid, 
  name text, 
  type text, 
  "parentId" uuid, 
  price numeric
);
-- POPULATE THE TABLE

with recursive jdata as (
  select '{
  "id": "3fa85f64-5717-4562-b3fc-2c963f66a111",
  "name": "Категория",
  "type": "CATEGORY",
  "parentId": null,
  "date": "2022-05-28T21:12:01.516Z",
  "price": 6,
  "children": [
    {
      "name": "Оффер 1",
      "id": "3fa85f64-5717-4562-b3fc-2c963f66a222",
      "price": 4,
      "date": "2022-05-28T21:12:01.516Z",
      "type": "OFFER",
      "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a111"
    },
    {
      "name": "Подкатегория",
      "type": "CATEGORY",
      "id": "3fa85f64-5717-4562-b3fc-2c963f66a333",
      "date": "2022-05-26T21:12:01.516Z",
      "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a111",
      "price": 8,
      "children": [
        {
          "name": "Оффер 2",
          "id": "3fa85f64-5717-4562-b3fc-2c963f66a444",
          "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a333",
          "date": "2022-05-26T21:12:01.516Z",
          "price": 8,
          "type": "OFFER"
        }
      ]
    }
  ]
}'::jsonb as j
), rec as (
  select (j->>'id')::uuid as id, 
         j->>'name' as name, 
         j->>'type' as type, 
         (j->>'parentId')::uuid as "parentId",
         case
           when j->>'type' = 'CATEGORY' then null
           else (j->>'price')::numeric
         end as price,
         j->'children' as children
    from jdata
  union all
  select (j->>'id')::uuid as id, 
         j->>'name' as name, 
         j->>'type' as type, 
         (j->>'parentId')::uuid as "parentId",
         case
           when j->>'type' = 'CATEGORY' then null
           else (j->>'price')::numeric
         end as price,
         j->'children'
    from rec r
         cross join lateral jsonb_array_elements(r.children) as e(j)
) 
insert into shopunit
select id, name, type, "parentId", price
  from rec;
4 rows affected

select * from shopunit;


id
name
type
parentId
price


3fa85f64-5717-4562-b3fc-2c963f66a111
Категория
CATEGORY
null
null

3fa85f64-5717-4562-b3fc-2c963f66a222
Оффер 1
OFFER
3fa85f64-5717-4562-b3fc-2c963f66a111
4

3fa85f64-5717-4562-b3fc-2c963f66a333
Подкатегория
CATEGORY
3fa85f64-5717-4562-b3fc-2c963f66a111
null

3fa85f64-5717-4562-b3fc-2c963f66a444
Оффер 2
OFFER
3fa85f64-5717-4562-b3fc-2c963f66a333
8


with recursive top_down as (
  select s.id, s.name, s.type, s."parentId", s.price, 1 as level, 
         array[s.id] as path, s.id as root
    from shopunit s
   where s."parentId" is null
  union all
  select c.id, c.name, c.type, c."parentId", c.price, p.level + 1 as level, 
         p.path||c.id as path, p.root
    from shopunit c
         join top_down p on p.id = c."parentId"
), category_averages as (
  select p."parentId", avg(c.price) as price, p.level, p.root
    from top_down p
         join top_down c 
           on p."parentId" = any(c.path)
   group by p."parentId", p.level, p.root
), fill_missing as (
  select s.id, s.name, s.type, s."parentId",
         coalesce(a.price, s.price)::numeric(8,2) as price, 
         t.level, max(t.level) over (partition by t.root) as max_depth,
         row_number() over (partition by s."parentId" order by s.id) as n,
         count(1) over (partition by s."parentId") as max_n,
         now() as date
    from shopunit s
         left join category_averages a on a."parentId" = s.id
         join top_down t on t.id = s.id
), build_json as (
  select id, "parentId", level, max_depth, n, max_n,
         to_jsonb(fill_missing) - 'level' - 'max_depth' - 'n' - 'max_n' as j
    from fill_missing 
   where level = max_depth
     and n = max_n
  union all
  select next.id, next."parentId", next.level, next.max_depth, next.n, next.max_n,
         case
           when next.level = prev.level 
             then '[]'::jsonb||(to_jsonb(next) - 'level' - 'max_depth' - 'n' - 'max_n')||prev.j
           else 
             jsonb_set(
               to_jsonb(next) - 'level' - 'max_depth' - 'n' - 'max_n', 
               '{children}', '[]'::jsonb || prev.j
             )
         end as j
    from fill_missing next
         join build_json prev 
           on    (prev.n = 1 and prev."parentId" = next.id and next.n = next.max_n)
              or (prev.n > 1 and prev."parentId" = next."parentId" and next.n = prev.n - 1)
)
select id, jsonb_pretty(j) as j
  from build_json
 where "parentId" is null;


id
j


3fa85f64-5717-4562-b3fc-2c963f66a111
{
    "id": "3fa85f64-5717-4562-b3fc-2c963f66a111",
    "date": "2022-06-27T14:28:45.091974+01:00",
    "name": "Категория",
    "type": "CATEGORY",
    "price": 6.00,
    "children": [
        {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66a222",
            "date": "2022-06-27T14:28:45.091974+01:00",
            "name": "Оффер 1",
            "type": "OFFER",
            "price": 4.00,
            "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a111"
        },
        {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66a333",
            "date": "2022-06-27T14:28:45.091974+01:00",
            "name": "Подкатегория",
            "type": "CATEGORY",
            "price": 8.00,
            "children": [
                {
                    "id": "3fa85f64-5717-4562-b3fc-2c963f66a444",
                    "date": "2022-06-27T14:28:45.091974+01:00",
                    "name": "Оффер 2",
                    "type": "OFFER",
                    "price": 8.00,
                    "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a333"
                }
            ],
            "parentId": "3fa85f64-5717-4562-b3fc-2c963f66a111"
        }
    ],
    "parentId": null
}
id	name	type	parentId	price
3fa85f64-5717-4562-b3fc-2c963f66a111	Категория	CATEGORY	null	null
3fa85f64-5717-4562-b3fc-2c963f66a222	Оффер 1	OFFER	3fa85f64-5717-4562-b3fc-2c963f66a111	4
3fa85f64-5717-4562-b3fc-2c963f66a333	Подкатегория	CATEGORY	3fa85f64-5717-4562-b3fc-2c963f66a111	null
3fa85f64-5717-4562-b3fc-2c963f66a444	Оффер 2	OFFER	3fa85f64-5717-4562-b3fc-2c963f66a333	8