How to Query a JSON Column

SQL Tutorial Home

Oracle 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 unstructured 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 (
    emp_json CLOB
    CONSTRAINT enforce_json CHECK (emp_json IS JSON)
);

insert into emp_json_tbl values ('{"emp_id":1,"last_name":"bisso","dep_id":100,"salary":1000,"department":null}');

insert into emp_json_tbl values ('{"emp_id":2,"last_name":"lopez","dep_id":100,"salary":900,"department":null}');

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 JSON_VALUE(emp_json, '$.last_name') AS last_name_json from emp_json_tbl;

last_name_json 
--------------
bisso
lopez

IN THIS PAGE