Retrieving nested values from PostgreSQL JSON columns

Question

Our database contains a table "log" like this:

id | purchases (json)
1    {"apple":{"price":5,"seller":"frank"}, "bred":{"price":3,"seller":"kathy"}}
2    {"milk":{"price":3,"seller":"anne"}, "banana":{"price":2,"seller":"frank"}}
3    {"bred":{"price":4,"seller":"kathy"}}

We would like to retrieve all records containing "seller":"kathy". We tried simple queries like this:

SELECT id FROM log WHERE purchases ->> 'seller' LIKE 'kathy'
SELECT id FROM log WHERE purchases = '{"seller":"kathy"}'

We researched here and elsewhere for some hours ... it seems a bit more complex because the values are nested? We found e.g. some java or pgplsql implementations, but we are still hoping there is a "pure SQL" way. What would be a proper solution? Or should we re-organize our content like this:

id | purchases (json)
1    [{"product":"apple","price":5,"seller":"frank"},{"product":"bred","price":3,"seller":"kathy"}]
2    [{"product":"milk","price":3,"seller":"anne"},{"product":"banana","price":2,"seller":"frank"}]
3    [{"product":"bred","price":4,"seller":"kathy"}]

But what we found, this would be even more complex, because we have to explode the arrays within the query. Any short hint? Thanks!

Dzmitry Savinkou · Accepted Answer · 2016-02-12 01:33:49Z

Check json_each() and #>> Postgres JSON functions:

WITH log(id,purchases) AS ( VALUES
  (1,'{"apple":{"price":5,"seller":"frank"}, "bred":{"price":3,"seller":"kathy"}}'::JSON),
  (2,'{"milk":{"price":3,"seller":"anne"}, "banana":{"price":2,"seller":"frank"}}'::JSON),
  (3,'{"bred":{"price":4,"seller":"kathy"}}'::JSON)
)
SELECT log.* FROM log,
  json_each(log.purchases) as purchase
WHERE
  purchase.value#>>'{seller}' = 'kathy';

Result:

 id |                                  purchases                                  
----+-----------------------------------------------------------------------------
  1 | {"apple":{"price":5,"seller":"frank"}, "bred":{"price":3,"seller":"kathy"}}
  3 | {"bred":{"price":4,"seller":"kathy"}}
(2 rows)

perfect, helps a lot, thanks @dmitry, I understand much more now.

Collectives™ on Stack Overflow

Retrieving nested values from PostgreSQL JSON columns

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related