JSON in Oracle: The Ultimate Guide

Oracle has quite a few features for storing and working with JSON data.

In this guide, you’ll learn:

• What JSON is and why you might want to use it
• Creating a table to store JSON data
• How to add, read, update, and delete JSON data
• Tips for performance, validating and working with JSON

Let’s get into the guide.

If you want to download a PDF version of this guide, enter your email below and you’ll receive it shortly.

 

What is JSON and Why Should I Use It?

JSON stands for JavaScript Object Notation, and it’s a way to format and store data.

Data can be represented in a JSON format in Oracle so it can be read and understood by other applications or parts of an application.

It’s similar to HTML or XML – it represents your data in a certain format that is readable by people but designed to be readable by applications.

 

Why Use JSON In Your Database?

So why would you use JSON data in your database?

If you need a structure that’s flexible.

A normalised database structure, one with tables and columns and relationships, works well for most cases. Recent improvements in development practices also mean that altering a table is not as major as it was in the past, so adjusting your database once it’s in production is possible.

However, if your requirements mean that your data structure needs to be flexible, then a JSON field may be good for your database.

One example may be where a user can add custom attributes. If it was done using a normalised database, this may involve altering tables, or creating an Entity Attribute Value design, or some other method.

If a JSON field was used for this, it would be much easier to add and maintain these custom attributes.

The JSON data can also be stored in your database and processed by an ORM (Object Relational Mapper) or your application code, so your database may not need to do any extra work.

 

What Does JSON Data Look Like?

Here’s a simple example of JSON data:

{
  "id": "1",
  "username": "sstorm",
  "location": "United States"
}

It uses a combination of different brackets, colons, and quotes to represent your data.

Let’s take a look at some more examples.

 

Name/Value Pair

JSON data is written as name/value pairs. A name/value pair is two values enclosed in quotes.

This is an example of a name/value pair:

"username": "sstorm"

The name is “username” and the value is “sstorm”. They are separated by a colon “:”.

This means for the attribute of username, the value is sstorm. Names in JSON need to be enclosed in double quotes.

 

Objects

JSON data can be enclosed in curly brackets which indicate it’s an object.

{"username": "sstorm"}

This is the same data as the earlier example, but it’s now an object. This means it can be treated as a single unit by other areas of the application.

How does this help? It’s good for when there are multiple attributes:

{
  "username": "sstorm",
  "location": "United States"
}

Additional attributes, or name/value pairs, can be added by using a comma to separate them.

You’ll also notice in this example the curly brackets are on their own lines and the data is indented. This is optional: it’s just done to make it more readable.

 

Arrays

JSON also supports arrays, which is a collection of records within an object. Arrays in JSON are included in square brackets and have a name:

{
  "username": "sstorm",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

 

In this example, this object has an attribute called “posts”. The value of posts is an array, which we can see by the opening square bracket “[“.

Inside the square bracket, we have a set of curly brackets, indicating an object, and inside those, we have an id of 1 and a title of Welcome. We have another set of curly brackets indicating another object.

These two objects are posts and they are contained in an array.

And that covers the basics of what JSON is.

If JSON is new to you, don’t worry, it gets easier as you work with it more.

If you’re experienced with JSON, you’ll find the rest of the guide more useful as we go into the details of working with JSON in Oracle.

 

How to Create and Populate a JSON Field in Oracle

So you’ve learned a bit about JSON data and why you might want to use it.

How do we create a field in Oracle?

 

Creating a JSON Field

We create a new field to store our JSON data.

If you’re using Oracle 21c, a JSON data type was introduced.

In versions older than Oracle 21c, there is no JSON data type. You can store JSON data in VARCHAR2, CLOB, or BLOB data types. Oracle recommends using a BLOB data type due to the size of the field and that there is no need to perform character-set conversion.

We can also add a constraint to ensure it is valid JSON.

Here’s an example using a BLOB data type. We’ll see a version using the JSON data type shortly.

CREATE TABLE product (
  id INT,
  product_name VARCHAR2(200),
  attributes BLOB
);

We have created a table called product. It has an id and a product name. There’s also an attributes column, which has the data type of BLOB.

Adding a JSON column is as easy as that.

What about validation? How do we know the text we add to this field is valid JSON and not just a text string?

We can add a Check Constraint to the table to ensure the data is valid. Check Constraints are inspected whenever data is added or updated in the table.

Here’s what a check constraint would look like at the time we create the table:

CREATE TABLE product (
  id INT,
  product_name VARCHAR2(200),
  attributes BLOB,
  CONSTRAINT ck_product_json CHECK(attributes IS JSON)
);

The IS JSON check returns TRUE if the specified field is JSON-compliant. In this case, whenever we make changes to the attributes field, it must be a valid JSON document so that this check constraint passes.

With this JSON validation, the data is automatically validated for us. We won’t be able to store invalid data in the table.

Also, we get to use the various Oracle JSON functions on the JSON data to make working with it easier.

 

Creating a Table with a JSON Field (Oracle 21c)

In Oracle 21c, a dedicated JSON data type was added. This is essentially a text field that includes validation for JSON data.

Here’s how to create a table with this data type.

CREATE TABLE product (
  id INT,
  product_name VARCHAR2(200),
  attributes JSON
);

The attributes column here has a data type of JSON. No constraint is needed.

 

Adding Data to a JSON Field

Now we’ve got our field for storing JSON data, how do we add data to it?

We simply insert a record into our table as though it’s a text value.

With the check constraint that uses the IS JSON keyword, the data will be validated before it is inserted, and only valid data will be inserted.

We also need to use the UTL_RAW.CAST_TO_RAW function, to convert the readable text into a data type that can be stored in a BLOB field.

We can add our first product like this:

INSERT INTO product (id, product_name, attributes)
VALUES (
  1, 'Chair', 
  UTL_RAW.CAST_TO_RAW(
    '{"color":"brown", "material":"wood", "height":"60cm"}'
  )
);

We can run this statement and the record is inserted.

If we try to insert a value without this CAST_TO_RAW function, we’ll get an error.

Error report -
ORA-01465: invalid hex number

If we try to insert an invalid JSON field, we’ll get an error. Here’s the INSERT statement:

INSERT INTO product (id, product_name, attributes)
VALUES (100, 'Bad Chair', '"color":"brown:height}');

This is what we’ll see:

Error report -
ORA-02290: check constraint (SYSTEM.CK_PRODUCT_JSON) violated

Using the method above, we needed to enter in the data in exactly the right format.

 

Inserting Arrays

If you want to insert JSON data that contains arrays, you can enter it using text in a JSON format.

Here’s how to insert an array by just specifying it in a JSON format.

INSERT INTO product (id, product_name, attributes)
VALUES (
  2, 'Desk', 
  UTL_RAW.CAST_TO_RAW(
    '{"color":"black", "material":"metal", "drawers":[{"side":"left", "height":"30cm"}, {"side":"left", "height":"40cm"}]}'
  )
);

This will insert a new product that has an array of drawers. As you can probably see by this statement, reading it (and writing it) is a bit tricky.

You can insert simpler arrays using this method too.

INSERT INTO product (id, product_name, attributes)
VALUES (
  3, 'Side Table',
  UTL_RAW.CAST_TO_RAW(
    '{"color":"brown", "material":["metal", "wood"]}'
  )
);

The INSERT statements will work.

 

How to Read and Filter JSON Data in Oracle

Once you’ve got some JSON data in a table, the next step is to read it.

How do we do that?

Selecting a JSON field is a little harder than other databases.

If we just run a SELECT with the columns, we’ll get just the BLOB field.

SELECT
id,
product_name,
attributes
FROM product;

id	product_name	attributes
1	Chair	(BLOB)

There’s a function called UTL_RAW.CAST_TO_VARCHAR2 which is the opposite of the CAST_TO_RAW we used when inserting the data. We’ll need to use this when we select the data.

Here’s our updated query. It also helps to use a table alias, as we’ll need those later to select specific attributes.

SELECT
p.id, 
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

Here are the results:

id	product_name	attributes
1	Chair	{“color”: “brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	{“color”: “brown”, “material”: [“metal”, “wood”]}

This shows us the data in the JSON column, and it looks just like a text value.

The good thing with this is that any application can easily read this field and work with it how they want (display it, filter it, and so on).

What if we wanted to do more in our database?

 

Selecting Individual Attributes

The JSON data is stored in something that looks like a text field. However, it’s quite easy to get attributes and values out of this text field and display them.

We can extract a value from the JSON field and display it in a separate column. We do this using a few different methods. One method uses a function called JSON_VALUE.

We need to use the JSON_VALUE function to search for a particular attribute in the JSON value. And we need to use a “path expression” to specify the attribute.

How do we do this?

First, let’s write the path expression, and then put that into our JSON_VALUE function.

The path expression lets us specify the attribute we want to search for. It starts with a $ symbol, and we specify a dot then the name of the attribute we’re looking for.

For example, to specify the “color” attribute, out path expression would look like this:

'$.color'

To specify the “material” attribute, we can use this path expression:

'$.material'

If we had a height attribute enclosed in a dimensions attribute, our path expression would look like this:

'$.dimensions.height'

We use the dot to specify the next level in the hierarchy of attributes.

How do we use this to filter our data? We combine this path expression with the JSON_VALUE function.

The JSON_VALUE function takes two parameters:

JSON_VALUE (column, path_expression)

We can use this in an example.

 

Displaying a Field using JSON_VALUE

Let’s say we want to display the color attribute in a separate column in our results.

First, we write the path expression for the color attribute:

'$.color'

Then, we add this to our JSON_VALUE function:

JSON_VALUE(attributes, '$.color')

Finally, we add this to our SELECT clause to show it as a separate column.

SELECT
p.id,
p.product_name,
JSON_VALUE(p.attributes, '$.color') AS color,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

This query will show all records, and show the color attribute as a separate column.

id	product_name	color	attributes
1	Chair	brown	{“color”: “brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	black	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	brown	{“color”: “brown”, “material”: [“metal”, “wood”]}

We can see the separate column here.

 

Path Expression Examples

We can see another field using JSON_VALUE by specifying the attribute name:

SELECT
p.id,
p.product_name,
JSON_VALUE(p.attributes, '$.height') AS height,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

Here we are extracting the attribute called height. This is available in some records but not others.

id	product_name	height	attributes
1	Chair	60cm	{“color”: “brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	null	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	null	{“color”: “brown”, “material”: [“metal”, “wood”]}

A null value is shown for records that don’t have this attribute.

What about attributes that are arrays, such as “material” in this example?

SELECT
p.id,
p.product_name,
JSON_VALUE(p.attributes, '$.material') AS material,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

id	product_name	material	attributes
1	Chair	wood	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	metal	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	null	{“color”: “brown”, “material”: [“metal”, “wood”]}

 

Using Dot Notation

Another method for selecting data is using “dot notation”.

Instead of using a “path expression”, which is the dollar sign inside quotes, we can use a dot after the column name and then the attribute, as though it’s something that’s a property of the table.

For example, to select the color of the attributes column, we can do this:

attributes.color

The full Select statement looks like this:

SELECT
p.id,
p.product_name,
p.attributes.color AS color,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

Here are the results:

id	product_name	color	attributes
1	Chair	brown	{“color”: “brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	black	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	brown	{“color”: “brown”, “material”: [“metal”, “wood”]}

Other attributes, such as material and height, can be used in this way as well.

 

Selecting Array Values

What if we want to see an attribute that’s inside another attribute? For example, the first of the “drawer” attributes?

Because “drawer” is an array, we can’t use the dot notation to get the attribute like this:

JSON_VALUE(attributes, '$.drawers.side')

This will return a null value as there is no attribute called side: it’s part of an array.

However, we can use a number to reference the position in the array.

You can return the first object using [0]:

JSON_VALUE(attributes, '$.drawers[0]')

The second object can be found using [1], the third object using [2], and so on.

So, our query to extract the first item in the array is:

SELECT
p.id,
p.product_name,
JSON_VALUE(p.attributes, '$.drawers[0]') AS drawer,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

The results are:

id	product_name	drawer	attributes
1	Chair	null	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	null	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	null	{“color”: “brown”, “material”: [“metal”, “wood”]}

The only record that has an attribute of “drawer” is id 2, the Chair. But why isn’t it showing in the results?

It’s because the drawers attribute is an array. It contains a series of other attributes, unlike color and height and material which are single values.

The JSON_VALUE function only returns single values, or “scalar values” as they are called in the documentation.

To return objects or arrays, we need to use the JSON_QUERY function.

It works in the same way. We provide a JSON field and a path expression.

Here’s what it looks like.

SELECT
p.id,
p.product_name,
JSON_QUERY(p.attributes, '$.drawers[0]') AS drawer,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p;

Here are the results:

id	product_name	drawer	attributes
1	Chair	null	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}
2	Desk	{“side”:”left”, “height”:”30cm”}	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}
3	Side Table	null	{“color”: “brown”, “material”: [“metal”, “wood”]}

We can see the differences in the results using the JSON_QUERY function. It shows the drawer attribute.

So, as you can see, there is a range of ways you can use the JSON functions with a path expression to get the attribute you want.

 

Filtering on JSON Data in Oracle

Let’s say we wanted to see our Chair product, which has a brown color, wood material, and a height of 60cm. But we want to filter on the JSON attributes for this example.

Let’s try this query.

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE UTL_RAW.CAST_TO_VARCHAR2(p.attributes) = '{"color":"brown", "material":"wood", "height":"60cm"}';

We can run this query. Here’s what we see:

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}

This works because the text value stored in the attributes column can be converted with the CAST_TO_VARCHAR2 function,, and as long as we provide the exact full string, it will work.

Often we need to filter on one of the keys or values. How can we do that?

What if we try using the LIKE keyword with a partial match?

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE UTL_RAW.CAST_TO_VARCHAR2(p.attributes) LIKE '%"color":"brown"%';

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}
3	Side Table	{“color”: “brown”, “material”: [“metal”, “wood”]}

This does give us the result we want. However, using wildcard searches can be quite slow if there is a lot of data in the table.

There are several features in Oracle that make it possible to filter on JSON data.

 

Using JSON_VALUE to Filter Data

Let’s say we want to find all products where the color is brown. The color is a part of the attributes JSON column in our table.

Our path expression would look like this:

‘$.color’

We can write the JSON_VALUE function like this:

JSON_VALUE(attributes, '$.color')

We then add this to our SELECT statement

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE JSON_VALUE(p.attributes, '$.color') = 'brown';

We’ve added this JSON_VALUE function to our WHERE clause, and added a condition where the color is equal to “brown”.

The results of this query are shown below.

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}
3	Side Table	{“color”: “brown”, “material”: [“metal”, “wood”]}

We can see that the results only show records where the color attribute is brown.

 

Viewing JSON Data as a Table

So far we’ve seen examples of using different functions to read and filter JSON data.

The result has been that all of the JSON data has been displayed in a single column.

Oracle includes a function that lets you expand the JSON fields and display them in a table. This table shows a few columns: key, value, and type.

This can be useful for data in your tables, or if you’re loading JSON from an external source and want to translate it into your database tables.

The function is called JSON_TABLE:

JSON_TABLE(json_field, '$' COLUMNS (column_list))

The json_doc parameter is the JSON value that will be translated.

Here’s an example using our table:

SELECT
p.id,
p.product_name,
j.*
FROM product p,
JSON_TABLE(
    p.attributes, '$' COLUMNS (
        color PATH '$.color',
        material PATH '$.material',
        height PATH '$.height',
        drawers PATH '$.drawers'
    )
) j;

We are selecting a couple of fields from the product table, then everything from the “j” table. This “j” table is the result of the JSON_TABLE function, which has been used in the From clause and returns a view of the data from the attributes column.

Inside the COLUMNS brackets, we specify the attributes to use, along with the path expression to find each attribute.

This is what the results show:

id	product_name	color	material	height	drawers
1	Chair	brown	wood	60cm	(null)
2	Desk	black	metal	(null)	(null)
3	Side Table	brown	(null)	(null)	(null)

The JSON_TABLE function is quite useful if you need to extract or transpose your JSON data into columns.

 

Looking Inside a JSON Value using JSON_EXISTS

Oracle has a function called JSON_EXISTS, which lets you find records that contain a certain JSON attribute.

The JSON_EXISTS function takes a JSON field and an attribute, and returns true if the attribute is found in the JSON field, and false if it is not. It’s often used in the WHERE clause to filter data.

Let’s see an example. This query finds records that have the attribute of “drawers”.

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE JSON_EXISTS(p.attributes, '$.drawers');

The results are:

id	product_name	attributes
2	Desk	{“color”:”black”, “material”:”metal”, “drawers”:[{“side”:”left”, “height”:”30cm”}, {“side”:”left”, “height”:”40cm”}]}

The row with id 2 is shown as it’s the only one with the attribute of drawers.

You can also use it to see which rows have multiple values, or in an array, by using a number inside square brackets to find the element. This example will look for element 1, which is the second element, inside the material attribute.

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE JSON_EXISTS(p.attributes, '$.material[1]');

id	product_name	attributes
3	Side Table	{“color”:”brown”, “material”:[“metal”, “wood”]}

 

How to Update JSON Data in Oracle

Reading JSON is one thing. What if you need to update JSON data?

This depends on the version of Oracle you’re working on:

• Prior to 19c, we have to update the entire field at once.
• As of 19c, you can update parts of a JSON value using JSON_MERGEPATCH.
• As of 21c, you can be more specific with changes using JSON_TRANSFORM.

Let’s see how we can perform different operations using these methods. We’ll use the prior to 19c method and the 19c method. I’ll exclude JSON_TRANSFORM as it’s fairly new (and I don’t have access to a 21c database to test!).

 

Insert a New Item – Pre-19c

One way to update JSON data is to add a new item to an existing JSON value.

To do this prior to 19c, we have to run an UPDATE statement to update the entire field with the new field’s value.

Here’s the first item in our table:

SELECT
id,
product_name,
attributes
FROM product
WHERE id = 1;

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}

Let’s say we want to add a new attribute name and value, in addition to the color, height, and material that already exist. This new attribute would be “width”, and the value is 100cm.

To do this in versions earlier than 19c, we need to run an Update statement with the entire new value.

We also need to use the CAST_TO_RAW function as the column is a BLOB data type.

UPDATE product
SET attributes = UTL_RAW.CAST_TO_RAW(
    '{"color":"brown", "material":"wood", "height":"60cm", "width":"100cm"}'
)
WHERE id = 1;

We can run the same SELECT statement as above to see the updated value.

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”, “width”:”100cm”}

We can see that “width” has been added to the list of attributes.

There are several problems with this method:

• You need to have the entire JSON value before updating, which can be hard for large values
• Your resulting JSON needs to be compliant. If you misplace a comma or other character, the JSON is invalid and won’t be updated.

 

Insert a New Item – 19c with JSON_MERGEPATCH

If you’re running Oracle 19c or later, you can use the JSON_MERGEPATCH function. This function will allow you to replace a certain section of the JSON field or document.

This makes it easier to write queries.

To add a new attribute, we can use this code:

UPDATE product
SET attributes = JSON_MERGEPATCH (
    attributes,
    '{"depth":"20cm"}'
    )
WHERE id = 1;

This query adds a new attribute called “depth” with a value of 20cm.

The results are:

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”, “width”:”100cm”, “depth”:”200cm”}

 

Updating an Existing Value

If you want to replace a value of an attribute inside a JSON field with another value, you’ll need to replace the entire value in pre-19c versions, or use the JSON_MERGEPATCH function in 19c and later.

The path that you provide must refer to an existing attribute key. If it does not exist, then the attribute will be added, as we saw in the earlier example.

Let’s see some examples.

Here’s our first product ID:

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”}

We can update the value of color to white by using this JSON_MERGEPATCH function. Because we are updating an existing value, we use the UPDATE statement.

UPDATE product
SET attributes = JSON_MERGEPATCH (
    attributes,
    '{"color":"white"}'
    )
WHERE id = 1;

We can select from this table and see the record is updated:

id	product_name	attributes
1	Chair	{“color”:”white”, “material”:”wood”, “height”:”60cm”}

 

How to Delete from a JSON Field in Oracle

There are two DELETE operations you can do when working with JSON fields:

• delete an attribute from a JSON field
• delete a row from your table

 

Deleting a Row using JSON_VALUE

Deleting a row from your table is done in the same way as regular SQL. You can write an SQL statement to delete the row that matches your ID, or using JSON_VALUE.

For example, to delete all rows where the color attribute is brown:

DELETE FROM product
WHERE JSON_VALUE(attributes, '$.color') = 'brown';

This will remove the matching records from the table.

 

Removing an Attribute from a JSON Field

The other way to delete JSON data is to remove an attribute from a JSON field.

This is different from updating, as you’re removing the attribute entirely rather than just updating its value to something else.

We can do this with an UPDATE statement and the JSON_MERGEPATCH function. We specify a new value of NULL.

Let’s see an example by removing an attribute from product ID 1.

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE p.id = 1;

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”, “width”:”100cm”}

We can run an UPDATE statement with JSON_MERGEPATCH to remove the “height” attribute.

UPDATE product
SET attributes = JSON_MERGEPATCH (
    attributes,
    '{"height":null}'
    )
WHERE id = 1;

We can then select the data from the table again to see that it has been removed:

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “width”:”100cm”}

The attribute of height is no longer in the JSON field.

 

Validating JSON Data

We’ve seen in the examples so far in this guide that using a BLOB column with a check constraint in Oracle automatically validates data for you. It ensures you can only insert valid JSON data into the field. You can also use a JSON data type in recent versions.

The keyword IS JSON can only be applied to a check constraint. It can’t be used as a function in regular SQL.

 

Improve the Performance of JSON Queries

The JSON support and features in Oracle are pretty good, and each version includes more features.

So, given that you can add JSON columns to tables, extract fields, and get all the flexibility of JSON fields with validation, wouldn’t it be better to just store all of your data in JSON fields rather than normalised tables?

Well, sometimes that might be a good idea. But then you may be better off using a NoSQL database rather than Oracle.

Another reason why using primarily JSON fields to store your data is not a good idea is that it can struggle with performance.

 

Select Performance

For example, let’s say we want to select all products where the color is brown. We can use the JSON_VALUE function in the WHERE clause that we saw earlier in this guide:

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE JSON_VALUE(p.attributes, '$.color') = 'brown';

Let’s see the execution plan for this:

operation	object_name	options	cardinality	cost
SELECT STATEMENT			2	2
TABLE ACCESS	PRODUCT	FULL	2	2

The execution plan shows a Full Table Access step, which is a slow type of access. This might be OK for our table, which only has a few records, but once you start working with larger tables it can be quite slow.

So, using the JSON_VALUE function in the WHERE clause will mean a full table scan is used.

What can we do?

 

Function-Based Indexes

Oracle allows you to create a function-based index on the field that contains the JSON value. This can then be used in certain situations to speed up your queries.

Let’s see how we can do that.

We create an index based on the JSON_VALUE in the WHERE clause we want to run.

CREATE INDEX idx_json_color
ON product (
  JSON_VALUE(
    attributes,
    '$.color'
    ERROR ON ERROR
  )
);

The ERROR ON ERROR clause means that any errors found when processing this index are shown to the user.

Now let’s run the Select query again and see the explain plan.

operation	object_name	options	cardinality	cost
SELECT STATEMENT			2	2
TABLE ACCESS	PRODUCT	BY INDEX ROWID BATCHED	2	2
INDEX	IDX_JSON_COLOR	RANGE SCAN	2	1

The cost and cardinality are the same, which is expected on such a small table.

However the index is being used. A Range Scan is used, instead of a Full Table Scan, which shows this concept can be applied to small and large tables.

 

Search Indexes

Function-based indexes work well if you know what attributes you’re looking for.

They don’t work as well if you’re searching the entire JSON field.

Oracle has a feature called a Search Index which can help with that. The search index allows you to efficiently search the JSON field.

Here’s how to create one:

CREATE SEARCH INDEX idx_json_search
ON product(attributes) FOR JSON;

This will create an Oracle Text index on the attributes column.

Now we have created this index, we can use the JSON_TEXTCONTAINS function to search for records that match a JSON value.

For example, to find all records that contain the word “wood”, you can use this query.

SELECT
p.id,
p.product_name,
UTL_RAW.CAST_TO_VARCHAR2(p.attributes) AS attr
FROM product p
WHERE JSON_TEXTCONTAINS(p.attributes, '$', 'wood');

Here’s what the results show.

id	product_name	attributes
1	Chair	{“color”:”brown”, “material”:”wood”, “height”:”60cm”, “width”:”100cm”}
3	Side Table	{“color”:”brown”, “material”:[“metal”, “wood”]}

The Explain Plan shows the index being used:

operation	object_name	options	cardinality	cost
SELECT STATEMENT			1	4
TABLE ACCESS	PRODUCT	BY INDEX ROWID	1	4
DOMAIN INDEX	IDX_JSON_SEARCH		1	4

A search index can be useful if you are looking for specific values but don’t want to create a function-based index on each possible value.

 

Tips for Working with JSON in Oracle

In this guide, we’ve looked at what JSON is, seen how to create JSON fields in Oracle, and seen a range of ways we can work with them.

So, what’s the best way to work with JSON fields in Oracle?

Here are some tips I can offer for using JSON in Oracle. They may not apply to your application or database but they are things to consider.

 

Just because you can, doesn’t mean you should

JSON is flexible and quite powerful, but just because you can store data in a JSON field, doesn’t mean you should. Consider using the advantages of Oracle relational database and using JSON where appropriate.

 

Treat the JSON field like a black box

The JSON field can be used to store valid JSON data sent or received by your application. While there are functions for reading from and working with the JSON field, it might be better to just store the JSON data in the field, retrieve it from your application, and process it there.

This is the concept of a black box. The application puts data in and reads data from it, and the database doesn’t care about what’s inside the field.

It may or may not work for your situation, but consider taking this approach.

 

Search by the Primary Key and other fields

We’ve seen that it can be slow to search by attributes inside the JSON field. Consider filtering by the primary key and other fields in the table, rather than attributes inside the JSON field. This will help with performance.

 

Conclusion

I hope you found this guide useful. Have you used JSON fields in Oracle? What has your experience been like? Do you have any questions? Feel free to use the comments section on the post.