JSON dot-notation syntax exploration

View All Scripts Login to Run Script

Script Name JSON dot-notation syntax exploration
Description JSON dot-notation syntax exploration. Ref: https://docs.oracle.com/database/121/ADXDB/json.htm#GUID-7249417B-A337-4854-8040-192D5CEFD576
Area SQL General / JSON
Contributor rmoff
Created Monday June 18, 2018

Statement 1

Create table to hold JSON

CREATE TABLE X (X CLOB     CONSTRAINT ensure_X_json CHECK (X IS JSON))

Table created.

Statement 2

Load some JSON

INSERT INTO X VALUES ('{"X":1,"Y":2,"Z":{"A":1,"B":2}}')

1 row(s) inserted.

Statement 3

Returns the full JSON object

SELECT X FROM X


X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

Statement 6

Returns the full JSON object

SELECT FOO.X FROM X FOO


X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
1

Statement 8

SELECT X FROM X X


X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

Statement 9

SELECT X.X FROM X X


X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
{"X":1,"Y":2,"Z":{"A":1,"B":2}}

X
1

Statement 11

CREATE TABLE FOO (BAR CLOB CONSTRAINT ensure_BAR_json CHECK (BAR IS JSON))

Table created.

Statement 12

INSERT INTO FOO VALUES ('{"X":42}')

1 row(s) inserted.

Statement 13

CREATE TABLE BAR (X NUMBER(9))

Table created.

Statement 14

INSERT INTO BAR VALUES (1)

1 row(s) inserted.

X	X
1	1

Statement 17

SELECT BAR.X, FOO.BAR.X FROM FOO FOO, BAR


X X
1 42

X	X
1	42

BAR
{"X":42}

X
42

Additional Information