How to Query a JSON Column

SQL Tutorial Home

Postgres supports json columns in a regular table, as any other data type. Then you can query a json column as any other kind of column. However, as a json value can store a unstructure data, there are several functions that allow to convert a json column into relational data, or extract an element from a json value, or even create a json value from regular relational data.

Let’s create a table with a json column, populated with relational data using the to_json function

create table emp_json_tbl as  select to_json(e) emp_json_col from employee e;

select * from emp_json_tbl ;

emp_json_col 
-------------
"{"emp_id":1,"last_name":"bisso","dep_id":100,"salary":1000,"department":null}"
"{"emp_id":2,"last_name":"lopez","dep_id":100,"salary":900,"department":null}"

select emp_json->'last_name' as last_name_json from emp_json_tbl ;
last_name_json
bisso
lopez 
select emp_json->>'last_name' as last_name_text from emp_json_tbl ;
last_name_text
last_name_text
bisso
lopez

The short arrow -> keeps the type as JSON, and the long arrow ->> returns text

IN THIS PAGE