PostgreSQL 9.3: Aggregate collection of JSON arrays

Question

I have a table inventory with a value column that contains JSON strings, with the following data structure:

{ "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15] "bar": [500, 5], "baz": [400, 10] }, ... }

To query for a particular item, I use the following parameterized SQL:

SELECT (value::JSON->$1->$2->>0)::INT AS price , (value::JSON->$1->$2->>1)::INT AS quantity FROM inventory WHERE as_of_date = $3::DATE

...where $1 is the category, $2 is the item and $3 is the date.

Now I need to sum() all of the prices and quantities for each item in a category, and I'm not sure how to go about it. I've tried using JSON functions like json_each, but can't manage to get the nth array element from all JSON field values.

Ezequiel Tolnay · Accepted Answer · 2016-04-26 01:47:09Z

You can use json_each and LEFT JOIN LATERAL for this purpose:

WITH inventory AS ( SELECT '{ "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15], "bar": [500, 5], "baz": [400, 10] } }'::text AS value ) SELECT v.key, (v.value->>0)::INT AS price , (v.value->>1)::INT AS quantity FROM inventory i LEFT JOIN LATERAL json_each(i.value::JSON->$1) v ON (true) WHERE as_of_date = $3::DATE;

You can then filter, group by and sum as you need.

If I could up-vote this ten times I would. Nicely done!

Shaun Scovil
– Shaun Scovil

2016-04-26 14:08:59 +00:00
Commented Apr 26, 2016 at 14:08 — Shaun Scovil
– Shaun Scovil, Commented Apr 26, 2016 at 14:08

Stack Exchange Network

PostgreSQL 9.3: Aggregate collection of JSON arrays

1 Answer 1

Hot Network Questions

PostgreSQL 9.3: Aggregate collection of JSON arrays

1 Answer 1

Related

Hot Network Questions