Extract all values from json in sql table

Question

I have a postgresql table which has a column in json format.

Sample column value:

{"Apple":{"category":"fruit","price":100},"Orange":{"category":"fruit","price":80}}

Now I want to select this column, and extract the "price" for all items in each row.

Query to get column:

select items from my_table

To extract the json value for a specific item, I can use

select items -> 'Orange' -> 'price' as price
from my_table

But how do I extract the price for all the items (Apple, Orange)? As an array maybe.

klin · Accepted Answer · 2017-07-10 11:17:49Z

1

Use json_each(), e.g.:

with my_table(items) as (
    values (
    '{"Apple":{"category":"fruit","price":100},"Orange":{"category":"fruit","price":80}}'::json
    )
)

select key, (value->>'price')::numeric as price
from my_table,
json_each(items)

  key   | price 
--------+-------
 Apple  |   100
 Orange |    80
(2 rows)

answered Jul 10, 2017 at 11:17

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thirupathi Thangavel Over a year ago

How do I replace the hard-coded json value here with the query from my actual table?

klin Over a year ago

Just skip with... and execute select ....

Vao Tsun · Accepted Answer · 2017-07-10 11:19:00Z

1

t=# with my_table(items) as (
  values('{"Apple":{"category":"fruit","price":100},"Orange":{"category":"fruit","price":80}}'::json)
)
select
  json_object_keys(items)
, items->json_object_keys(items)->>'price'
from my_table;
 json_object_keys | ?column?
------------------+----------
 Apple            | 100
 Orange           | 80
(2 rows)

json_object_keys: https://www.postgresql.org/docs/9.5/static/functions-json.html

answered Jul 10, 2017 at 11:19

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Extract all values from json in sql table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related