Json query vs SQL query using JSON in Oracle 12c (Performance)

Question

I am using oracle 12c and Sql Developer with json

For this example I have the follow JSON:

   {
        "id": "12",
        "name": "zhelon"
   }

So I have created the follow table for this:

create table persons
id number primary key,
person clob,
constraint person check(person is JSON);

The idea is persist in person column the previous JSON and use a the follow query to get that data

SELECT p.person FROM persons p WHERE json_textvalue('$name', 'zhelon')

Talking about perfonce, I am intresting to extract some json field and add new a colum to the table to improve the response time (I don't know if that is possible)

create table persons
id number primary key,
name varchar(2000),
person clob,
constraint person check(person is JSON);

To do this:

SELECT p.person FROM persons p WHERE p.name = 'zhelon';

My question is:

What's the best way to make a query to get data? I want to reduce the response time.

Which query get the data faster ?

SELECT p.person FROM persons p WHERE json_textvalue('$name', 'zhelon')

or

SELECT p.person FROM persons p WHERE p.name = 'zhelon';

Wernfried Domscheit · Accepted Answer · 2017-01-04 15:43:02Z

2

You can create a virtual column like this:

ALTER TABLE persons ADD (NAME VARCHAR2(100) 
   GENERATED ALWAYS AS (JSON_VALUE(person, '$name' returning VARCHAR2)) VIRTUAL);

I don't know the correct syntax of JSON_VALUE but I think you get an idea.

If needed you can also define a index on such columns like any other column.

However, when you run SELECT p.person FROM persons p WHERE p.name = 'zhelon'; I don't know which value takes precedence, p.person from JSON or the column.

Better use a different name in order to be on the safe side:

ALTER TABLE persons ADD (NAME_VAL VARCHAR2(100) 
   GENERATED ALWAYS AS (JSON_VALUE(person, '$name' returning VARCHAR2)) VIRTUAL);

SELECT p.person FROM persons p WHERE p.NAME_VAL= 'zhelon';

edited Jan 4, 2017 at 15:43

answered Jan 4, 2017 at 14:18

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wernfried Domscheit Over a year ago

If you create an index on column NAME the performance will be much better.

TuGordoBello Over a year ago

the index always improve the perfonamce, but for this case what happend if I index the clob collumn? @WernfriedDomscheit

Wernfried Domscheit Over a year ago

Index on CLOB does not make any sense.

Collectives™ on Stack Overflow

Json query vs SQL query using JSON in Oracle 12c (Performance)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related