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

3

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.

answered Apr 26, 2016 at 1:47

Ezequiel Tolnay

5,0281 gold badge17 silver badges23 bronze badges

1

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

Add a comment |

Stack Exchange Network

PostgreSQL 9.3: Aggregate collection of JSON arrays

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL 9.3: Aggregate collection of JSON arrays

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions