CODEWITHSUNDEEP
postgresql
user5871859
user5871859

Reputation:

Using SUM throws "values aggregate function calls cannot contain set-returning..." error

I try to use SUM with json_array_elements_text in a PostgreSql query as shown on the fiddle.

SELECT
    details->>'city' as city,
    SUM(json_array_elements_text(details->'prices')::numeric) as total_prices
FROM my_table
group by city

However, encounter the following error:

aggregate function calls cannot contain set-returning function calls LINE :SUM(json_array_elements_text(details->'prices')::numeric...

So, how to fix the problem?

Here is the records:

INSERT INTO my_table(details) VALUES('
    {
        "city": "London",
        "name": "Sainburry",
        "quantities": [112, 145, 222, 122, 124],
        "prices": [4, 4, 4, 2, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }',
    '{
        "city": "London",
        "name": "Forever",
        "quantities": [33, 44, 432, 134, 57],
        "prices": [5, 4, 7, 8, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }',
    '{
        "city": "Edinburgh",
        "name": "Circle",
        "quantities": [22, 44, 234, 123, 66],
        "prices": [5, 2, 8, 4, 6],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }
    ');

Upvotes: 3

Views: 7953

Answers (1)

Bergi
Bergi

Reputation: 665040

Move the set-returning function into a lateral query:

SELECT
    details->>'city' as city,
    SUM(price::numeric) as total_prices
FROM my_table,
    LATERAL json_array_elements_text(details->'prices') AS prices(price)
GROUP BY city

(updated fiddle)

Upvotes: 8

Related Questions