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.

Answer 1

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.

• Shaun Scovil: If I could up-vote this ten times I would. Nicely done!