PostgreSQL JSON – Aggregate Collection of JSON Arrays

jsonpostgresqlpostgresql-9.3

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.

Best Answer

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.

Related Question