JSON Path expressions and simplified syntax

View All Scripts Login to Run Script

Script Name JSON Path expressions and simplified syntax
Description Trailing functions, syntax simplification, etc
Area SQL General / JSON
Contributor Beda Hammerschmidt (Oracle)
Created Monday October 28, 2019

Statement 1
```
drop table t
```

Statement 2

create table t (id number, col clob check (col IS JSON))

Statement 3

TheJSON data has an array which contains possible JSON values, scalar and complex values.

insert into t values(1, '{a:[1, "1", "cat", true, null, false, {}, ["dog","mouse"]], b:"json is fun"}')

Statement 4
```
commit
```
Statement 5
The so-called JSON simplified syntax or 'dot notation' allows to user dots and square brackets to navigate into a SQL expression if it is known to be JSON.
```
select t.col.a
from t t
```
Statement 6
[0] selects the first item of the array
```
select t.col.a[0]
from t t
```
Statement 7
The wildcard selects all items in the array. The result is than serialized using an array wrapper.
```
select t.col.a[*]
from t t
```
Statement 8
[2 to 4] is an array subscript selecting 3 items of the array.
```
select t.col.a[2 to 4]
from t t
```
Statement 9
trailing (item) functions can be used to perform an operation on the selected values.
```
select t.col.a.size()
from t t
```
Statement 10
number() is a conversion function
```
select t.col.a[0].number()
from t t
```
Statement 11
```
select t.col.a[1].number()
from t t
```
Statement 12
numberOnly() is a filter function that only allows JSON numbers to be used in comparisons with numbers.
```
select t.col.a[1].numberOnly()
from t t
```
Statement 13
```
select t.col.a[1].type()
from t t
```
Statement 14
```
select t.col.a[0].type()
from t t
```
Statement 15
```
select t.col.a.type()
from t t
```
Statement 16
in the simplified syntax the type() and other trailing function can only be applied on one selected value.
```
select t.col.a[*].type()
from t t
```
Statement 17
```
drop table emp
```
Statement 18
For the next example we want to use JSON generation over relational tables.
```
create table emp (empno number, ename varchar2(10), job varchar2(9))
```

Statement 19

insert into emp values('7844', 'Turner', 'Clerk')

Statement 20

insert into emp values('7934', 'Ford', 'Analyst')

Statement 21
```
commit
```
Statement 22
In this example the simplified syntax (dot notation) is allowed because the output of JSON_OBJECT always contain JSON data.
```
with empjson as (select JSON_OBJECT('name' VALUE ename, 'empno' VALUE empno) "val" from emp)
select ej."val"."empno" from empjson ej
```
Statement 23
Once the expression is modified to be a generic SQL expression (here by using string concatenation) the simplified syntax stops working because the input to it is not known to be JSON anymore.
```
with empjson as (select (JSON_OBJECT('name' VALUE ename, 'empno' VALUE empno) || ' ') "val" from emp)
select ej."val"."empno" from empjson ej
```
Statement 24
This is an arbitrary function that returns JSON data. It is not important what's done inside the function - all that matters is that JSON is always being returned when calling this function.
```
create or replace function foo return varchar as
begin
  return JSON_OBJECT('x' value  '123');
end;
```
Statement 25
The same is true if a function returns JSON (inside a varchar or clob). When parsing the simplified syntax it is not known that the function returns JSON.
```
with plsqlFun as (select foo "foo" from dual)
select p."foo".x 
from plsqlFun p 
```

Statement 26

TREAT AS allows to mark a SQL expression as JSON to enable the simplified syntax.

with plsqlFun as (select TREAT(foo AS JSON) "foo" from dual)
select p."foo".x 
from plsqlFun p

Statement 27

with empjson as (select TREAT ((JSON_OBJECT('name' VALUE ename, 'empno' VALUE empno) || ' ') AS JSON) "val" from emp)
select ej."val"."empno" 
from empjson ej

Statement 28
TREAT AS is just a markup mechanism - it does not check that the expression is actually valid JSON. use IS JSON instead.
```
with empjson as (select TREAT ('{[/' AS JSON) "val" from dual)
select ej."val"."empno" 
from empjson ej
```

Statement 29

with empjson as (select TREAT ('{[/' AS JSON) "val" from dual)
select ej."val"."empno" 
from empjson ej
where ej."val" IS JSON

Statement 30
The trailing (item) functions are also supported in the SQL/JSON functions.
```
select JSON_VALUE(col, '$.a.type()')
from t
```

Statement 31

select JSON_VALUE(col, '$.a.size()')
from t

Statement 32

select JSON_VALUE(col, '$.a[*].type()')
from t

Statement 33

select JSON_VALUE(col, '$.a[*].type()' ERROR ON ERROR)
from t

Statement 34

select JSON_QUERY(col, '$.a[*].size()' with wrapper)
from t

Statement 35

only one trailing function is supported.

select JSON_QUERY(col, '$.a[*].type().size()' with wrapper)
from t

Statement 36

select JSON_QUERY(col, '$.a[*].number()' with wrapper)
from t

Statement 37

select JSON_VALUE(col, '$.a[0]' returning NUMBER)
from t

Statement 38
JSON_VALUE with number() function is equivalent to using RETURNING NUMBER in returning clause.
```
select JSON_VALUE(col, '$.a[0].number()' )
from t
```

Statement 39

select JSON_VALUE(col, '$.a[1].number()' )
from t

Statement 40

select JSON_VALUE(col, '$.a[1].numberOnly()')
from t

Statement 41

select JSON_VALUE(col, '$.a[1].numberOnly()' ERROR ON ERROR )
from t

Statement 42
```
truncate table t
```
Statement 43
In the next example we use JSON data with an array of objects. More specifically, each person can have list of skills and we want to find properties based on the skill level (proficiency).
```
insert into t values(1,'{
	"name": "Geeta",
	"skills": [{
		"skill": "testing",
		"proficiency": 30
	}, {
		"skill": "java",
		"proficiency": 85
	}, {
		"skill": "c",
		"proficiency": 90
	}]
}')
```

Statement 44

insert into t values(2,'{
	"name": "Jonathan",
	"skills": [{
		"skill": "javascript",
		"proficiency": 70
	}, {
		"skill": "python",
		"proficiency": 100
	}]
}')

Statement 45
```
insert into t values(3,'{}')
```
Statement 46
```
commit
```
Statement 47
A normal sql comparison cannot be used in this context because the path expression does not select a single value.
```
select col from t t where t.col.skills.proficiency > 80
```

Statement 48

select t.col.skills.proficiency
from t t

Statement 49
Instead, a path predicate needs to be used. Path predicates are part of the SQL/JSON path language.
```
select t.col.skills from t t
where JSON_EXISTS(col, '$.skills?(@.proficiency > 80)')
```

Statement 50

Predicates are now also supported in JSON_QUERY, etc.

select JSON_QUERY(col,'$.skills?(@.proficiency > 80)' with conditional wrapper) from t

Statement 51

select JSON_QUERY(col,'$.skills[*]?(@.proficiency > 80)' with conditional wrapper) from t

Statement 52

select JSON_QUERY(col,'$.skills[*]?(@.proficiency > 80).skill' with conditional wrapper) from t

Statement 53

select JSON_QUERY(col,'$.skills[*]?(@.proficiency > 80).skill' with wrapper) from t
where JSON_EXISTS(col, '$.skills?(@.proficiency > 80)')

Statement 54

This is the full syntax of a JSON_TABLE statement.

SELECT jt.* 
FROM t, 
     JSON_TABLE(col, '$' 
                      columns ("name" varchar2(30) path '$.name',
                               NESTED PATH '$.skills[*]' 
                              columns ("skill_name" varchar2(20) path '$.skill',
                                       "proficiency" NUMBER path '$.proficiency'))) jt

Statement 55

and it's version using simplified syntax.

SELECT jt.* 
FROM t NESTED col columns (name,
                             NESTED '$.skills[*]'columns (skill, proficiency)) jt

Statement 56

NUMBER overrides the default column type of VARCHAR2(4000)

SELECT jt.* 
FROM t NESTED col columns (name,
                             NESTED '$.skills[*]'columns (skill, proficiency NUMBER)) jt

Statement 57

As an optimization multiple SQL/SJON expressions are executed as one JSON_TABLE.

explain plan for
select JSON_QUERY(col,'$.skills'), 
       JSON_QUERY(col,'$.skills[*]?(@.proficiency > 80).skill' with wrapper), 
       JSON_VALUE(col,'$.skills[*]?(@.proficiency > 80).skill[0]') 
from t
where JSON_EXISTS(col, '$.skills?(@.proficiency > 80)')

Statement 58
Explain plan shows JSON_TABLE
```
select * from table(dbms_xplan.display)
```
Statement 59
```
desc emp
```
Statement 60
Similarly, the syntax for the JSON generation function got simplified.
```
select JSON_OBJECT('name' value ename, 'position' value job) from emp
```

Statement 61

select JSON_OBJECT('name' : ename, 'position' : job) from emp

Statement 62

select JSON_OBJECT('name' : ename, job) from emp

Statement 63
```
select JSON_OBJECT(ename, job) from emp
```
Statement 64
```
select JSON_OBJECT(*) from emp
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database