PostgreSQL 9.3: Aggregate collection of JSON arrays 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-&gt;$1-&gt;$2-&gt;&gt;0)::INT AS price
, (value::JSON-&gt;$1-&gt;$2-&gt;&gt;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.
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-&gt;&gt;0)::INT AS price
, (v.value-&gt;&gt;1)::INT AS quantity
FROM inventory i
LEFT JOIN LATERAL json_each(i.value::JSON-&gt;$1) v ON (true)
WHERE as_of_date = $3::DATE;


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