Working with the JSON Data Type¶
Native support for JSON data was introduced in Oracle database 12c. You can use
the relational database to store and query JSON data and benefit from the easy
extensibility of JSON data while retaining the performance and structure of the
relational database. JSON data is stored in the database in BLOB, CLOB or
VARCHAR2 columns. For performance reasons, it is always a good idea to store
JSON data in BLOB columns. To ensure that only JSON data is stored in that
column, use a check constraint with the clause is JSON
as shown in the
following SQL to create a table containing JSON data:
create table customers (
id integer not null primary key,
json_data blob check (json_data is json)
);
The following Python code can then be used to insert some data into the database:
import json
customerData = dict(name="Rod", dept="Sales", location="Germany")
cursor.execute("insert into customers (id, json_data) values (:1, :2)",
[1, json.dumps(customerData)])
The data can be retrieved in its entirety using the following code:
import json
for blob, in cursor.execute("select json_data from customers"):
data = json.loads(blob.read())
print(data["name"]) # will print Rod
If only the department needs to be read, the following code can be used instead:
for deptName, in cursor.execute("select c.json_data.dept from customers c"):
print(deptName) # will print Sales
You can convert the data stored in relational tables into JSON data by using the JSON_OBJECT SQL operator. For example:
import json
cursor.execute("""
select json_object(
'id' value employee_id,
'name' value (first_name || ' ' || last_name))
from employees where rownum <= 3""")
for value, in cursor:
print(json.loads(value,))
The result is:
{'id': 100, 'name': 'Steven King'}
{'id': 101, 'name': 'Neena Kochhar'}
{'id': 102, 'name': 'Lex De Haan'}
See JSON Developer’s Guide for more information about using JSON in Oracle Database.