How to aggregate multiple rows from subquery?

Question

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:

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

score 3 · Accepted Answer · 2020-07-30 08:51:04Z

3

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

edited Jul 30, 2020 at 8:51

answered Jul 30, 2020 at 8:26

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Christof Over a year ago

I got ERROR: cannot extract elements from an object

user330315 Over a year ago

@Sojtin: it works with the sample data you have given us: dbfiddle.uk/… apparently you haven't told us everything

Christof Over a year ago

default value of prod_prop is '{}'::jsonb is that any relevant?

user330315 Over a year ago

Yes, see my edit. But if your json is supposed to store an array, the default value should be '[]'::jsonb

user330315 Over a year ago

@Sojtin: the version with the CASE expression should be able to handle any "mess"

|

Barbaros Özhan · Accepted Answer · 2020-07-30 08:26:06Z

2

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

answered Jul 30, 2020 at 8:26

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Christof Over a year ago

I got ERROR: cannot extract elements from an object

Barbaros Özhan Over a year ago

there's no problem @Sojtin . Check this out please.

Collectives™ on Stack Overflow

How to aggregate multiple rows from subquery?

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related