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

TuGordoBello

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')

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

Wernfried Domscheit

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';

Collected from the Internet

Please contact [email protected] to delete if infringement.