10

I try this

select created_at, 
sum((json_array_elements(shipping_lines::json) ->> 'price')::float) as shipping_price
from t1
group by 1

It show Error:

ERROR: aggregate function calls cannot contain set-returning function calls LINE 5: sum(((json_array_elements(shipping_lines::json) ->> 'price')... ^ HINT: You might be able to move the set-returning function into a LATERAL FROM item.

How to fix this using Lateral From? I read this PsSQL docs but not really understand Lateral function

GMB
  • 216,147
  • 25
  • 84
  • 135
Tom Tom
  • 328
  • 4
  • 15

3 Answers3

8

That would be:

select t1.created_at, sum((x.obj->>'price')::float)  as shipping_price
from t1
left join lateral jsonb_array_element(t1.shipping_lines::jsonb) as x(obj) on true 
group by 1

Or, you can compute the sum() in the lateral join itself, which avoids the need for outer aggregation (assuming that created_at is unique in the table to start with):

select t1.created_at, x.shipping_price
from t1
cross join lateral (
    select sum((x.obj->>'price')::float) as shipping_price
    from jsonb_array_elements(t1.shipping_lines::jsonb) as x(obj)
) x

Note that I slightly changed the query to use jsonb instead of json: this new datatype is more flexible and efficient than json (even if it won't make a real difference here, it should be preferred whenever there is a choice).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Why it is `LEFT JOIN on True` on first answer and not Cross join? What is the difference? you miss the `s` in `jsonb_array_elements` – Tom Tom Oct 28 '20 at 11:19
  • 1
    @HaoHao: that's in case the `shipping_lines` array is empty. In that case, `cross join lateral` would return no row, and the original row would be filtered out. – GMB Oct 28 '20 at 11:20
  • So the second answer would also `LEFT JOIN on True`? – Tom Tom Oct 28 '20 at 11:56
4

Hmmm. Move the logic to the from clause:

select created_at, sum( (j->>'price')::float) as shipping_price
from t1 left join lateral
     json_array_elements(shipping_lines::json) j
     on true
group by 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

One option is to use a common table expression to do it later.

e.g.

with cte1 as
(
select created_at, 
(json_array_elements(shipping_lines::json) ->> 'price')::float) as c1
from t1
)
select created_at, 
sum(c1) as shipping_price
from cte1
group by 1

Very similar to above answer but easier to read imo.

Mike Packer
  • 63
  • 5