Oracle 23ai: Comparing and Sorting JSON Datatypes

 

- Overview:

• Oracle first introduced the JSON datatype in Oracle database 21c.
• Oracle Database 23ai introduces the option to compare and sort JSON datatypes.
• It allows equality comparison and sorting of JSON values.
• It is supported in WHERE, ORDER BY, and GROUP BY clauses.
• It makes for more powerful SQL/JSON programs.
• Avoids unexpected datatype conversion problems.

In this blog, I'll show different demos demonstrating JSON datatype comparing and sorting.

- Prerequisites:

• Oracle Database 23ai.
• SQL Developer.

Demo #1: Compare JSON Documents in Two Tables

1. Create two tables with a column, which has a JSON datatype. 

create table json_data1 (col1 json);

create table json_data2 (col2 json);

2. Insert JSON documents into both tables with a name object has name and address fields, and address field has street and city individual fields.

  

INSERT into json_data1 

values (' {"name":"Scott", "address": {"street":"123 Bay St", "City":"Toronto"} }');

INSERT into json_data2 

values (' {"name":"Adam", "address": {"City":"Toronto", "street":"123 Bay St"} }');

    - You notice that the city and street in above JSON documents are in a different order across the two different tables.

3. Join the two tables on address field.

    - Prior to 23ai, we would need to know the fields within the address to compare individual fields.

    - However in 23ai, this becomes much more easier, where we join two tables with address fields without pointing to the individual fields within the field address as shown below.

select * from json_data1 t1, json_data2 t2 where t1.col1.address = t2.col2.address;

Demo #2: JSON Type Comparison

1. Create a table with a column, which has a JSON datatype.

create table json_tab (col1 json);

2. Insert JSON documents with a name object has fname and lname fields.

insert into json_tab values ('{"name": {"fname":"Scott", "lname":"Tiger"} }');

insert into json_tab values ('{"name": {"fname":"Adam", "lname":"Smith"} }');

insert into json_tab values ('{"name": {"lname":"Tiger", "fname":"Scott"} }');

3. Query the table by trying to match name object where fname/lname fields are in a different order. 

    - Notice that, the query will return the first 2 documents where fname/lname fields are in a different order and fname/lname fields are in the same order.

select * from json_tab t

where t.col1.name = json('{"lname":"Tiger", "fname":"Scott"}');

Demo #3: Sorting Mixed JSON Type Values

1. Create a table with a column, which has a JSON datatype.

create table json_sort (col1 json);

2. Insert different documents with a variety of  array, numeric, and string values.

insert into json_sort values ('{"jdoc": {"a":"b", "c": 1} }'); --- object 

insert into json_sort values ('{"jdoc": {"a":"z", "c": 10} }'); --- object

insert into json_sort values ('{"jdoc": [100, 500, 1000] }'); --- Array

insert into json_sort values ('{"jdoc": 20 }'); --- numeric  

insert into json_sort values ('{"jdoc": 60 }'); --- numeric 

insert into json_sort values ('{"jdoc": "10" }'); --- string

3. Query the rows using string ordering (using JSON_SERIALIZE function).

select t.col1.jdoc from json_sort t order by JSON_SERIALIZE(t.col1.jdoc);

4. Query the rows in JSON type default order, which is number, string, object, array.

select t.col1.jdoc from json_sort t order by t.col1.jdoc;