How to Store JSON in Oracle Table Using JSON Column（oracle json列）
With the increasing popularity of JSON (JavaScript Object Notation) as a data interchange format, many developers are looking for ways to store JSON data in relational databases like Oracle. Fortunately, Oracle Database provides support for storing and manipulating JSON data using JSON columns. In this article, we’ll explore how to store JSON data in an Oracle table using a JSON column.
What is a JSON column?
A JSON column is a special type of column that can store JSON data in a relational database. It’s similar to a traditional column, but it allows you to store a JSON object or an array in a single cell. This makes it easy to store and retrieve JSON data without having to parse or serialize it.
Creating a table with a JSON column
To create a table with a JSON column in Oracle, you need to use the CREATE TABLE statement and specify the data type of the JSON column as JSON. Here’s an example:
CREATE TABLE users (
id NUMBER,
name VARCHAR2(50),
address JSON
);
In this example, we’re creating a table called users with three columns: id, name, and address. The data type of the address column is JSON, which means it can store JSON objects or arrays.
Inserting JSON data
To insert JSON data into a JSON column, you can use the INSERT statement and provide the JSON data as a string literal. Here’s an example:
INSERT INTO users VALUES (
1,
‘John’,
‘{
“street”: “123 Mn St”,
“city”: “New York”,
“state”: “NY”,
“zip”: “12345”
}’
);
In this example, we’re inserting a record into the users table with an id of 1, a name of John, and an address of {“street”: “123 Mn St”, “city”: “New York”, “state”: “NY”, “zip”: “12345”}.
Querying JSON data
To query JSON data in a JSON column, you can use the JSON_VALUE function. Here’s an example:
SELECT id, name, JSON_VALUE(address, ‘$.city’) as city
FROM users;
In this example, we’re selecting the id, name, and city from the users table. We’re using the JSON_VALUE function to extract the city value from the address column using the JSON path expression ‘$.city’.
Updating JSON data
To update JSON data in a JSON column, you can use the UPDATE statement and provide the new JSON data as a string literal. Here’s an example:
UPDATE users
SET address = ‘{
“street”: “456 Mn St”,
“city”: “San Francisco”,
“state”: “CA”,
“zip”: “54321”
}’
WHERE id = 1;
In this example, we’re updating the address of the user with an id of 1 to {“street”: “456 Mn St”, “city”: “San Francisco”, “state”: “CA”, “zip”: “54321”}.
Deleting JSON data
To delete JSON data in a JSON column, you can use the DELETE statement. Here’s an example:
DELETE FROM users
WHERE id = 1;
In this example, we’re deleting the record from the users table with an id of 1.
Conclusion
In this article, we’ve explored how to store JSON data in an Oracle table using a JSON column. We’ve covered how to create a table with a JSON column, insert JSON data, query JSON data, update JSON data, and delete JSON data. With this knowledge, you can now start using JSON columns in your Oracle databases to store and manipulate JSON data.
