Create Table (JSON_QRY)

CREATE TABLE JSON_QRY 
   (	JSON_DATA CLOB)

Insert the JSON Code into a new row

Insert into JSON_QRY (JSON_DATA) values (' 
   { 
     "dateLimits": { 
       "lowerBound": 1800, 
       "upperBound": 2100 
     }, 
     "inclusions": [{ 
       "operator": "and", 
       "searchFieldConfigid": "1", 
       "term": "mountain and lake" 
     }, { 
       "operator": "and", 
       "searchFieldConfigid": "3", 
       "term": "jokes stories" 
     }, { 
       "operator": "or", 
       "searchFieldConfigid": "8", 
       "term": "\"folklore and legends\"" 
     }], 
     "exclusions": [{ 
       "operator": "and", 
       "searchFieldConfigid": "3", 
       "term": "desert and ocean" 
     }, { 
       "operator": "and", 
       "searchFieldConfigid": "4", 
       "term": "exercise running" 
     }, { 
       "operator": "or", 
       "searchFieldConfigid": "9", 
       "term": "\"hiking and swimming\"" 
     }] 
   } 
     ')

example 1 : JSON stored in table column

 select nvl(a.upper_bound,'0'), nvl(a.lower_bound,'0') 
 from   JSON_QRY, 
        json_table(json_data,'$.dateLimits' 
         columns (row_number for ordinality 
                 ,upper_bound varchar2(20) path '$.upperBound' 
                 ,lower_bound number path '$.lowerBound')) a 
 where rownum = 1

example 2 : JSON stored in table column

 select 'INC' INC_EXC, a.* -- combine included search terms into the set 
 from   JSON_QRY, 
        json_table(json_data,'$.inclusions[*]' 
        columns (row_number for ordinality 
                ,expr_oper varchar2(20) path '$.operator' 
                ,searchfieldconfigid number path '$.searchFieldConfigid' 
                ,term varchar2(4000) path '$.term')) a

Create an Object Type similar to the JSON_TABLE output

 CREATE OR REPLACE TYPE TO_JSON as object 
 ( JSONDATA CLOB);

Create a Table Type

 CREATE OR REPLACE TYPE TT_JSON 
 as table of TO_JSON;

Create a package for the next set of exercises

CREATE OR REPLACE PACKAGE JSON_TEST as 
  -- variable of the TT_JSON type 
  g_json_test tt_JSON := tt_json(); 
  -- source JSON in PL/SQL variable 
  g_src_json clob := ' 
  { 
    "dateLimits": { 
      "lowerBound": 1800, 
      "upperBound": 2100 
    }, 
    "inclusions": [{ 
      "operator": "and", 
      "searchFieldConfigid": "1", 
      "term": "mountain and lake" 
    }, { 
      "operator": "and", 
      "searchFieldConfigid": "3", 
      "term": "jokes stories" 
    }, { 
      "operator": "or", 
      "searchFieldConfigid": "8", 
      "term": "\"folklore and legends\"" 
    }], 
    "exclusions": [{ 
      "operator": "and", 
      "searchFieldConfigid": "3", 
      "term": "desert and ocean" 
    }, { 
      "operator": "and", 
      "searchFieldConfigid": "4", 
      "term": "exercise running" 
    }, { 
      "operator": "or", 
      "searchFieldConfigid": "9", 
      "term": "\"hiking and swimming\"" 
    }] 
  } 
    '; 
-- function to load global from the source then return the g_var 
function load_n_get_json 
return tt_json; 
-- function to get the JSON in session state 
function get_json 
return tt_json; 
--function to get the JSON directly from the source 
function get_json_2 
return clob; 
 
end json_test;

Create a package for the next set of exercises

  CREATE OR REPLACE PACKAGE BODY JSON_TEST as 
-------------------------------------------------------------------------------- 
  function load_n_get_json 
  return tt_json 
  as 
    l_json_table tt_json; 
  begin 
 
    -- load the JSON into a queryable format 
    l_json_table := tt_json(); 
    l_json_table.extend (1); 
    l_json_table(1) := to_json(g_src_json); 
 
    if g_json_test.count > 0 then 
      g_json_test.delete; 
    end if; 
    g_json_test := l_json_table; 
 
    return l_json_table; 
  end load_n_get_json; 
-------------------------------------------------------------------------------- 
  function get_json 
  return tt_json 
  as 
  begin 
    return g_json_test; 
  end get_json; 
-------------------------------------------------------------------------------- 
  function get_json_2 
  return clob 
  as 
  begin 
    return g_src_json; 
  end get_json_2; 
-------------------------------------------------------------------------------- 
end json_test;

example 3 : JSON recieved from a function

select 'INC' INC_EXC, a.* -- combine included search terms into the set 
from   table(JSON_TEST.load_n_get_json()), 
       json_table(jsondata,'$.inclusions[*]' 
       columns (row_number for ordinality 
               ,expr_oper varchar2(20) path '$.operator' 
               ,searchfieldconfigid number path '$.searchFieldConfigid' 
               ,term varchar2(4000) path '$.term')) a

example 4 : emulating JSON in Session State

select 'INC' INC_EXC, a.* -- combine included search terms into the set 
from   table(JSON_TEST.get_json()), 
      json_table(jsondata,'$.inclusions[*]' 
      columns (row_number for ordinality 
              ,expr_oper varchar2(20) path '$.operator' 
              ,searchfieldconfigid number path '$.searchFieldConfigid' 
              ,term varchar2(4000) path '$.term')) a

View created from the type-constructed JSON and the SQL

CREATE OR REPLACE VIEW JSON_IN_SS (INC_EXC, ROW_NUMBER, EXPR_OPER, SEARCHFIELDCONFIGID, TERM) AS 
  select 'INC' INC_EXC, a.ROW_NUMBER,a.EXPR_OPER,a.SEARCHFIELDCONFIGID,a.TERM 
  from   table(JSON_TEST.get_json()), 
         json_table(jsondata,'$.inclusions[*]' 
         columns (row_number for ordinality 
                 ,expr_oper varchar2(20) path '$.operator' 
                 ,searchfieldconfigid number path '$.searchFieldConfigid' 
                 ,term varchar2(4000) path '$.term')) a

query the view

select * from JSON_IN_SS

example 5 : query a function returning just the JSON SRC clob

select 'INC' INC_EXC, a."ROW_NUMBER",a."EXPR_OPER",a."SEARCHFIELDCONFIGID",a."TERM" 
from   dual, -- use of dual is highly appropriate 
       json_table(JSON_TEST.get_json_2(),'$.inclusions[*]' 
       columns (row_number for ordinality 
               ,expr_oper varchar2(20) path '$.operator' 
               ,searchfieldconfigid number path '$.searchFieldConfigid' 
               ,term varchar2(4000) path '$.term')) a

create view from Example 5

CREATE OR REPLACE VIEW JSON_FROM_SOURCE (INC_EXC, ROW_NUMBER, EXPR_OPER, SEARCHFIELDCONFIGID, TERM) AS 
  select 'INC' INC_EXC, a.ROW_NUMBER,a.EXPR_OPER,a.SEARCHFIELDCONFIGID,a.TERM 
  from   dual, 
         json_table(JSON_TEST.get_json_2(),'$.inclusions[*]' 
         columns (row_number for ordinality 
                 ,expr_oper varchar2(20) path '$.operator' 
                 ,searchfieldconfigid number path '$.searchFieldConfigid' 
                 ,term varchar2(4000) path '$.term')) a

Query the View

select * from JSON_FROM_SOURCE