How to parse JSON in postgresql

Question

I have a table in my database, which contains character varying column and this column has json. I need to write a query, which will somehow parse this json into separate columns.

I found json_each function here but I can't understand how to work with it.

PostgreSQL supports native JSON data type since version 9.2. — WEBjuju
– WEBjuju, Commented Apr 16, 2020 at 19:44

Ivan Ursul · Accepted Answer · 2015-09-17 09:02:13Z

173

I figured it out, guys

if I have a table books

I can easily write a query

SELECT 
   id, 
   data::json->'name' as name
FROM books;

And it will result in

I can also try to get non-existent column

SELECT 
   id, 
   data::json->'non_existant' as non_existant
FROM books;

And it this case I will get empty result

answered Sep 17, 2015 at 9:02

Ivan Ursul

3,7614 gold badges20 silver badges31 bronze badges

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

5 Comments

Dmitri Over a year ago

If you're on a recent-enough version of postgres, using data::jsonb->'foo' will be slightly more efficient (with 'json' it actually gets re-parsed for every element access).

Craig Ringer Over a year ago

@Dmitri In this context it would with data::jsonb too. You'd only benefit if you wrapped it in subquery and referenced the casted result from the inner query multiple times in the outer query.

nunespascal Over a year ago

Use data::json->>'name' if you don't want double quotes around strings. postgresql.org/docs/current/functions-json.html

Viktor Reinok Over a year ago

trim('"' FROM (data::json->'name')::text) in order to remove quotes

jayarjo Over a year ago

any solution for the case when one doesn't know structure of the underlying json blob?

hit3k · Accepted Answer · 2018-10-05 01:23:21Z

76

Awesome, thanks for sharing. I found that you can go deeper like:

SELECT 
   id, 
   data::json->'name' as name,
   data::json->'author' ->> 'last_name' as author
FROM books;

answered Oct 5, 2018 at 1:23

hit3k

1,5231 gold badge16 silver badges21 bronze badges

1 Comment

tr8er Sep 1 at 11:08

or like this if you want to access nested objects: data::json#>'{author,last_name}' as author

Collectives™ on Stack Overflow

How to parse JSON in postgresql

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related