Search code examples
sqlarraysjsonpostgresqllateral-join

How to move set-returning function into a LATERAL FROM item PostgreSQL

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

Solution

  • 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).