1

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.

2

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.

  • 1
    If I could up-vote this ten times I would. Nicely done! – Shaun Scovil Apr 26 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.