DBA Hub

📋Steps in this guide1/3

SQL NESTED Clause Instead of JSON_TABLE in Oracle Database 19c

The SQL NESTED clause allows us to write the equivalent of the LEFT OUTER JOIN JSON_TABLE using a simplified syntax.

oracle 19cconfigurationintermediate
by OracleDba
15 views
1

Setup

Create and populate the following table to provide some JSON data to work with. Notice the third row has no JSON data included.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
--DROP TABLE json_documents PURGE;

CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "John",
          "LastName"       : "Doe",
          "Job"            : "Clerk",
          "Address"        : {
                              "Street"   : "99 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             },
          "ContactDetails" : {
                              "Email"    : "[email protected]",
                              "Phone"    : "44 123 123456",
                              "Twitter"  : "@johndoe"
                             },
          "DateOfBirth"    : "01-JAN-1980",
          "Active"         : true
         }');

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "Jayne",
          "LastName"       : "Doe",
          "Job"            : "Manager",
          "Address"        : {
                              "Street"   : "100 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             },
          "ContactDetails" : {
                              "Email"    : "[email protected]",
                              "Phone"    : ""
                             },
          "DateOfBirth"    : "01-JAN-1982",
          "Active"         : false
         }');

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(), NULL);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'json_documents');
2

Using JSON_TABLE

A typical example of using to access the data might look like this. Notice the row without any JSON data isn't displayed. This is because we are effectively doing an inner join. The previous statement is functionally equivalent to the following. If we want to show data from columns outside the JSON data column, even when the JSON data column is null, we need to do a left outer join to , as shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SET LINESIZE 120
COLUMN first_name FORMAT A10
COLUMN last_name FORMAT A10
COLUMN job FORMAT A10
COLUMN email FORMAT A21
COLUMN phone FORMAT A14
COLUMN active FORMAT A6

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j,
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt;

ID                               FIRST_NAME LAST_NAME  JOB        EMAIL                 PHONE          ACTIVE
-------------------------------- ---------- ---------- ---------- --------------------- -------------- ------
82D1C9BC1499407EE055000000000001 John       Doe        Clerk      [email protected]  44 123 123456  true
82D1C9BC149A407EE055000000000001 Jayne      Doe        Manager    [email protected]                false

SQL>

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j JOIN
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt ON 1=1;

ID                               FIRST_NAME LAST_NAME  JOB        EMAIL                 PHONE          ACTIVE
-------------------------------- ---------- ---------- ---------- --------------------- -------------- ------
82D1C9BC1499407EE055000000000001 John       Doe        Clerk      [email protected]  44 123 123456  true
82D1C9BC149A407EE055000000000001 Jayne      Doe        Manager    [email protected]                false

SQL>

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j
LEFT OUTER JOIN
JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt ON 1=1;

ID                               FIRST_NAME LAST_NAME  JOB        EMAIL                 PHONE          ACTIVE
-------------------------------- ---------- ---------- ---------- --------------------- -------------- ------
82D1C9BC1499407EE055000000000001 John       Doe        Clerk      [email protected]  44 123 123456  true
82D1C9BC149A407EE055000000000001 Jayne      Doe        Manager    [email protected]                false
82D1C9BC149B407EE055000000000001

SQL>
3

Using SQL NESTED Clause

The SQL clause allows us to write the equivalent of the using a simplified syntax. This doesn't stop us using the clause to access nested columns or lists of columns. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j
NESTED data
COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active) jt;

ID                               FIRST_NAME LAST_NAME  JOB        EMAIL                 PHONE          ACTIVE
-------------------------------- ---------- ---------- ---------- --------------------- -------------- ------
82D1C9BC1499407EE055000000000001 John       Doe        Clerk      [email protected]  44 123 123456  true
82D1C9BC149A407EE055000000000001 Jayne      Doe        Manager    [email protected]                false
82D1C9BC149B407EE055000000000001

SQL>

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j
NESTED data
COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  active        VARCHAR2(5 CHAR)   PATH Active,
NESTED PATH ContactDetails COLUMNS
(
                    email       VARCHAR2(100 CHAR) PATH Email,
                    phone       VARCHAR2(50 CHAR)  PATH Phone
                  )) jt;

ID                               FIRST_NAME LAST_NAME  JOB        EMAIL                 PHONE          ACTIVE
-------------------------------- ---------- ---------- ---------- --------------------- -------------- ------
82D1C9BC1499407EE055000000000001 John       Doe        Clerk      [email protected]  44 123 123456  true
82D1C9BC149A407EE055000000000001 Jayne      Doe        Manager    [email protected]                false
82D1C9BC149B407EE055000000000001

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!