Parent average value for children sql - recursive

In order to determine if a unit is a child of another unit, you need to capture the path for each element in your recursive CTE. Then you can use a LATERAL JOIN on the unit_tree to find and average the price of the children of each unit.

WITH RECURSIVE shop_unit(id,name,price,parent_id,type) as (
    (VALUES
        ('a','Propane',null,null,'CATEGORY'),
          ('b','Fuels',null,'a','CATEGORY'),
            ('c','HD5',5,'b','ITEM'),
            ('d','HD10',10,'b','ITEM'),
            ('e','Commercial',15,'b','ITEM'),
          ('f','Accessories',null,'a','CATEGORY'),
            ('g','Grill',100,'f','ITEM'),
            ('h','NFT',null,'f','CATEGORY'),
              ('i','bwaah.jpg',20000,'h','ITEM'),
              ('j','jaypeg.jpg',100000,'h','ITEM'),
         ('k','WD-40',2,null,'ITEM')
      )
),

unit_tree as (
    SELECT
        s1.id,
        s1.name,
        s1.price,
        s1.parent_id,
        s1.type,
        0 as level,
        array[id] as path
    FROM
      shop_unit s1
    WHERE
      s1.id = 'a'

    UNION ALL

    SELECT
      s2.id,
      s2.name,
      s2.price,
      s2.parent_id,
      s2.type,
      level + 1,
      ut.path ||  s2.id as path --generate the path for every unit so that we can check if it is a child of another element
    FROM
      shop_unit s2
      JOIN unit_tree ut ON ut.id = s2.parent_id
)

SELECT
  ut.id,
  ut.name,
  ut.parent_id,
  ut.type,
  case when ut.type = 'CATEGORY' then ap.avg_price else ut.price end as price,
  ut.level,
  ut.path

FROM
  unit_tree ut
  -- The JOIN LATERAL subquery roughly means "for each row of ut run this query"
  -- Must be a LEFT JOIN LATERAL in order to keep rows of ut that have no children.
  LEFT JOIN LATERAL (
    SELECT
      avg(ut2.price) avg_price
    FROM
      unit_tree ut2
    WHERE
      ut.level < ut2.level --is deeper level
      and  ut.id = any(path) --is in the path
    GROUP BY
      ut.id
  ) ap ON TRUE

ORDER BY id

You can use recursion to build the output JSON you want, as well:

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;

Which results in:

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

db<>fiddle here

(The hidden query populates the table from your example JSON)