CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.6
Christof
Christof

Reputation: 2734

How to aggregate multiple rows from subquery?

I want to get array of chd_id's for each prod_id, query below works but only with limit 1 i want exact result but with multiple rows

select 
    prod_id,
    array_agg((select * from jsonb_array_elements(products.prod_prop) limit 1)->>'chd_id')
from products
group by prod_id
limit 10

current result:

enter image description here

Raw value of products.prod_prop is

[{"val": ["xxx"], "chd_id": 25812}, {"val": ["yyy"], "chd_id": 2342}]

Without limit 1 i get ERROR: more than one row returned by a subquery used as an expression

Upvotes: 0

Views: 2037

Answers (2)

user330315
user330315

Reputation:

I think you need to do this with a lateral join:

select prod_id,
       array_agg(t.chd_id)
from products p
  left join lateral (
    select e ->> 'chd_id' as chd_id
    from jsonb_array_elements(p.prod_prop) as t(e)
  ) t on true
group by prod_id
limit 10;

The outer join is needed in case there are empty prod_prop values

Apparently the data in the JSONB is sometimes an array and sometimes a plain object. If all "plain objects" are indeed empty values, you can work around that using:

select prod_id,
       array_agg(t.chd_id) filter (where t.chd_id is not null)
from products p
  left join lateral (
    select e ->> 'chd_id' as chd_id
    from jsonb_array_elements(nullif(p.prod_prop,'{}')) as t(e)
  ) t on true
group by prod_id
limit 10;

If you do indeed mix plain objects and arrays in that column, you need a different approach:

select prod_id,
       array_agg(t.chd_id) filter (where t.chd_id is not null)
from products p
  left join lateral (
    select e ->> 'chd_id' as chd_id
    from jsonb_array_elements(case jsonb_typeof(p.prod_prop)
                                when 'array' then p.prod_prop
                                else '[]'
                              end) as t(e)
  ) t on true
group by prod_id
limit 10;

Online example

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You can use a CROSS JOIN among the table and the subquery generated through use of jsonb_array_elements() function :

SELECT p.prod_id, array_agg(j.e->>'chd_id') AS chd_id
  FROM products p
 CROSS JOIN jsonb_array_elements(prod_prop) j(e)
 GROUP BY prod_id

Upvotes: 2

Related Questions