select * from json_dump_file_contents;
All the data placed in the file is available for the DB engine to read now
You can also read the JSON elements easily using the "simple dot notation". E.g.
select t.json_document.name, t.json_document.database_id, t.* from json_dump_file_contents t;
The fun doesn’t end there. Lets use Oracle JSON features to parse it and make sense of the data
JSON Data Guide to "describe" the data ````
SELECT JSON_DATAGUIDE(json_document,DBMS_JSON.pretty) dg_doc
FROM json_dump_file_contents; ```
Database will read the document contents and make a dictionary out of it.
Since the data is not really loaded to the DB, every time when you query the data, it reads the file directly. But for better performance if you have multiple queries that target different rows, you can load an ordinary database table from the data in the external table. This also offers other benefits such as you can index the data, and make automatic views representing the JSON document in traditional RDBMS structure
Create "normal" table. A check constraint is put to ensure document is JSON. With Oracle 21c JSON datatype this is not required. ```
CREATE TABLE t_json_dump_file_contents
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
json_document BLOB
CONSTRAINT ensure_json CHECK (json_document IS JSON))
LOB (json_document) STORE AS (CACHE);
Load the data
INSERT INTO t_json_dump_file_contents (id, date_loaded, json_document)
SELECT SYS_GUID(), SYSTIMESTAMP, json_document FROM json_dump_file_contents
WHERE json_document IS JSON;
Now you can query the data coming from the normal table.
select t.json_document.name, t.json_document.database_id, t.* from t_json_dump_file_contents t; ```
Create Search Index ```
CREATE SEARCH INDEX json_docs_search_idx1 ON t_json_dump_file_contents (json_document) FOR JSON; ```
We can now create a view - that automatically understands the data model as per JSON documents present in the table ```
BEGIN
DBMS_JSON.create_view(
viewname => 'json_documents_v1',
tablename => 't_json_dump_file_contents',
jcolname => 'json_document',
dataguide => DBMS_JSON.get_index_dataguide(
't_json_dump_file_contents',
'json_document',
DBMS_JSON.format_hierarchical));
END;
/
Select from the view
select * from json_documents_v1; ```
You would notice that the JSON data is now automatically represented in RDBMS structure, with rows and columns - and any SQL can be written for analytics purposes TIP: DBMS_JSON.create_view cannot be done directly on the external table. But if you dont want to have this "normal" table in the picture, and want the data always from external table, but in the RDMS view format... Then you can temporarily create this normal table and view like above, and then the DDL of the view to point to the external table. you can also rename the column to remove "JSON_DOCUMENT$" prefix if not needed.
E.g.
Expand source
Now the data will be directly fetched from the external file and presented to you in RDBMS format.