What Data Type Is Used to Store JSON Data in Oracle Table?

JSON (JavaScript Object Notation) is a popular data format used for storing and exchanging data between a server and a client. With the increasing popularity of JSON, it has become essential to understand how to store JSON data in an Oracle table.

What Data Type Is Used to Store JSON Data?

In Oracle, starting from version 12.1.0.2, a new data type called JSON is introduced specifically for storing JSON data in a table. The JSON data type can be used as a column type, providing efficient storage and retrieval of JSON documents.

How to Create a Table with JSON Data Type?

To create a table with the JSON data type, you can use the following syntax:

CREATE TABLE your_table_name (
  json_column_name JSON
);

You can replace “your_table_name” with the desired name for your table and “json_column_name” with the desired name for your JSON column.

Example:

CREATE TABLE employees (
  emp_id NUMBER,
  emp_details JSON
);

The above example creates a table named “employees” with two columns – “emp_id” of type NUMBER and “emp_details” of type JSON.

Inserting JSON Data into an Oracle Table

To insert JSON data into an Oracle table, you can use the INSERT INTO statement along with the JSON_VALUE or JSON_OBJECT function to specify the values for the JSON column.

Syntax using INSERT INTO .. SELECT:

INSERT INTO your_table_name (json_column_name)
SELECT JSON_OBJECT('key1' VALUE 'value1', 'key2' VALUE 'value2') 
FROM dual;

You can replace “your_table_name” with the name of your table and “json_column_name” with the name of your JSON column. The JSON_OBJECT function is used to specify the key-value pairs for the JSON object. VALUES:

INSERT INTO your_table_name (json_column_name)
VALUES (JSON_OBJECT('key1' VALUE 'value1', 'key2' VALUE 'value2'));

The above syntax inserts a JSON object with key-value pairs into the specified JSON column.

Retrieving JSON Data from an Oracle Table

To retrieve JSON data from an Oracle table, you can use the SELECT statement along with the JSON_VALUE or JSON_QUERY function to extract specific values from the JSON column.

Syntax using SELECT:

SELECT json_value(json_column_name, '$.key1') AS key1_value,
       json_value(json_column_name, '$.key2') AS key2_value
FROM your_table_name;

The above syntax selects the values of “key1” and “key2” from the specified JSON column using the JSON_VALUE function.

Syntax using SELECT with WHERE clause:

SELECT *
FROM your_table_name
WHERE json_exists(json_column_name, '$.key1');

The above syntax selects all rows from the table where a specific key exists in the JSON column using the JSON_EXISTS function.

Conclusion

In conclusion, Oracle provides the JSON data type for efficient storage and retrieval of JSON data in a table. By using the appropriate syntax for creating tables and manipulating JSON data, you can effectively work with JSON in Oracle databases.

By using the JSON data type, you can take advantage of Oracle’s powerful querying capabilities to extract specific values from JSON documents stored in your tables.

With the ability to store and process JSON data directly in an Oracle table, you can seamlessly integrate JSON-based applications and services into your enterprise systems.

What Data Type Is Used to Store JSON Data in Oracle Table?