How to parse JSON in postgresql

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.


Solution 1:

I figured it out, guys

if I have a table books enter image description here

I can easily write a query

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

And it will result in

enter image description here

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

enter image description here

Solution 2:

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;