Query Table with JSON Data Type

View All Scripts Login to Run Script

Script Name Query Table with JSON Data Type
Description This script created a table with a CLOB attribute to save JSON documents. A query shows how to Query the collection and extract different nodes.
Area SQL General / JSON
Contributor FernandoPonte
Created Friday June 22, 2018

Statement 1

10 - Drop Table

DROP  TABLE XTR_PONTE_JSON_CONTENT

Table dropped.

Statement 2

20 - Create Demo Table

CREATE TABLE XTR_PONTE_JSON_CONTENT 
  ( MYID RAW(32), 
    JSON_DATA CLOB, 
    CONSTRAINT CONTACTS_JSON_CK CHECK (JSON_DATA IS JSON) ENABLE )

Table created.

Statement 3
Truncate the table just created in case of using the Script within a worksheet
30 - Insert Sample Data
```
TRUNCATE TABLE XTR_PONTE_JSON_CONTENT
```
```
Table truncated.
```

Statement 4

Truncate the table just created in case of using the Script within a worksheet

30 - Insert Sample Data

insert into XTR_PONTE_JSON_CONTENT (MYID, JSON_DATA) VALUES (SYS_GUID(),'{"_id":{"$oid":"5b203675fb6fc033f8831a09"},"Accountid":"123","AccountName":"Fernando","SourceSystem":"Demo"}')

1 row(s) inserted.

Statement 5

Truncate the table just created in case of using the Script within a worksheet

30 - Insert Sample Data

insert into XTR_PONTE_JSON_CONTENT (MYID, JSON_DATA) VALUES (SYS_GUID(),'{"_id":{"$oid":"5b205ac4fb6fc033f88330d8"},"Accountid":{"$numberInt":"2"},"Address":"Somewhere in the World"}')

1 row(s) inserted.

Statement 6

Truncate the table just created in case of using the Script within a worksheet

30 - Insert Sample Data

insert into XTR_PONTE_JSON_CONTENT (MYID, JSON_DATA) VALUES (SYS_GUID(),'{"_id":{"$oid":"5b218acafb6fc033f884064e"},"Accountid":{"$numberInt":"321"},"Browser":{"BrowserVersion":"58.0","BrowserMajorName":"Chrome","BrowserMinorName":"58.0"}}')

1 row(s) inserted.

Statement 7
Truncate the table just created in case of using the Script within a worksheet
30 - Insert Sample Data
```
commit
```
```
Statement processed.
```

Statement 8

40 - Query Raw Data

SELECT * FROM XTR_PONTE_JSON_CONTENT


MYID JSON_DATA
6F743C137783BDDAE0534C37548C923A {"_id":{"$oid":"5b203675fb6fc033f8831a09"},"Accountid":"123","AccountName":"Fernando","SourceSystem":"Demo"}
6F743C137785BDDAE0534C37548C923A {"_id":{"$oid":"5b205ac4fb6fc033f88330d8"},"Accountid":{"$numberInt":"2"},"Address":"Somewhere in the World"}
6F743C137787BDDAE0534C37548C923A {"_id":{"$oid":"5b218acafb6fc033f884064e"},"Accountid":{"$numberInt":"321"},"Browser":{"BrowserVersion":"58.0","BrowserMajorName":"Chrome","BrowserMinorName":"58.0"}}

3 rows selected.

MYID	JSON_DATA
6F743C137783BDDAE0534C37548C923A	{"_id":{"$oid":"5b203675fb6fc033f8831a09"},"Accountid":"123","AccountName":"Fernando","SourceSystem":"Demo"}
6F743C137785BDDAE0534C37548C923A	{"_id":{"$oid":"5b205ac4fb6fc033f88330d8"},"Accountid":{"$numberInt":"2"},"Address":"Somewhere in the World"}
6F743C137787BDDAE0534C37548C923A	{"_id":{"$oid":"5b218acafb6fc033f884064e"},"Accountid":{"$numberInt":"321"},"Browser":{"BrowserVersion":"58.0","BrowserMajorName":"Chrome","BrowserMinorName":"58.0"}}

Statement 9

Note that Querying the JSON structure is similar of navigating through an XML file, if you are used to that one too.

50 - Break Down Query on JSON structure

SELECT  
    J.MYID, 
    J.JSON_DATA."_id"."$oid" JSON_DOCID, 
    J.JSON_DATA.Accountid, 
    J.JSON_DATA.AccountName, 
    J.JSON_DATA.Browser.BrowserVersion, 
    J.JSON_DATA 
FROM XTR_PONTE_JSON_CONTENT J


MYID JSON_DOCID ACCOUNTID ACCOUNTNAME BROWSER JSON_DATA
6F743C137783BDDAE0534C37548C923A 5b203675fb6fc033f8831a09 123 Fernando  - {"_id":{"$oid":"5b203675fb6fc033f8831a09"},"Accountid":"123","AccountName":"Fernando","SourceSystem":"Demo"}
6F743C137785BDDAE0534C37548C923A 5b205ac4fb6fc033f88330d8 {"$numberInt":"2"}  -  - {"_id":{"$oid":"5b205ac4fb6fc033f88330d8"},"Accountid":{"$numberInt":"2"},"Address":"Somewhere in the World"}
6F743C137787BDDAE0534C37548C923A 5b218acafb6fc033f884064e {"$numberInt":"321"}  - 58.0 {"_id":{"$oid":"5b218acafb6fc033f884064e"},"Accountid":{"$numberInt":"321"},"Browser":{"BrowserVersion":"58.0","BrowserMajorName":"Chrome","BrowserMinorName":"58.0"}}

3 rows selected.

MYID	JSON_DOCID	ACCOUNTID	ACCOUNTNAME	BROWSER	JSON_DATA
6F743C137783BDDAE0534C37548C923A	5b203675fb6fc033f8831a09	123	Fernando	-	{"_id":{"$oid":"5b203675fb6fc033f8831a09"},"Accountid":"123","AccountName":"Fernando","SourceSystem":"Demo"}
6F743C137785BDDAE0534C37548C923A	5b205ac4fb6fc033f88330d8	{"$numberInt":"2"}	-	-	{"_id":{"$oid":"5b205ac4fb6fc033f88330d8"},"Accountid":{"$numberInt":"2"},"Address":"Somewhere in the World"}
6F743C137787BDDAE0534C37548C923A	5b218acafb6fc033f884064e	{"$numberInt":"321"}	-	58.0	{"_id":{"$oid":"5b218acafb6fc033f884064e"},"Accountid":{"$numberInt":"321"},"Browser":{"BrowserVersion":"58.0","BrowserMajorName":"Chrome","BrowserMinorName":"58.0"}}

Additional Information

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