current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

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.

Thanks in advance!

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
1  
If I could up-vote this ten times I would. Nicely done! – Shaun Scovil Apr 26 at 14:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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