Oracle: JSON

Selecting scalar variables from a 'dictionary'

select
   t.num,
   t.txt
from
   json_table (
   '{
     "num":  42,
     "txt": "Hello world",
     "ary": ["foo", "bar", "baz"],
     "dct": {"val_1": "one", "val_2": "two"}
  }',
  '$'
  columns (
     num number,
     txt varchar2(20)
  )
) t;

Note: the columns clause does not require a path clause because the selected column names correspond to the JSON's key names and are located in top-level position.

Selecting values from a 'sub-hash'

This statement uses the same JSON document as above and selects from a «nested» dictionary (note the path clause in the column clause):

select
   t.num,
   t.txt,
   t.dct_val_1,
   t.dct_val_2
from
   json_table (
   '{
     "num":  42,
     "txt": "Hello world",
     "ary": ["foo", "bar", "baz"],
     "dct": {"val_1": "one", "val_2": "two"}
   }',
   '$'
   columns (
      num       number,
      txt       varchar2(20),
      dct_val_1 varchar2(10) path dct.val_1,
      dct_val_2 varchar2(10) path dct.val_2
   )
) t;

Accessing array elements

Again the same JSON document, but this time selecting from a nested array.

Note the nested columns clause.

select
   t.num,
   t.txt,
   t.ary_elem
from
   json_table (
   '{
     "num":  42,
     "txt": "Hello world",
     "ary": ["foo", "bar", "baz"],
     "dct": {"val_1": "one", "val_2": "two"}
  }',
  '$'
  columns (
     num       number,
     txt       varchar2(20),
     nested path '$.ary[*]'
     columns (
        ary_elem varchar2(50) path '$'
     )
  )
) t;
--
--  NUM TEXT         ARY_ELEM
--  --- -----------  --------
--  42  Hello World  foo
--  42  Hello World  bar
--  42  Hello World  baz

Selecting all records from a JSON array of arrays

The following example selects from a JSON array of arrays which returns a table with multiple rows and columns:

select * from
   json_table ('[
     [0, "zero" , "A"],
     [1, "one"  , "B"],
     [2, "two"  , "C"],
     [3, "three", "D"],
     [4, "four" , "E"],
     [5, "five" , "F"]
    ]',
   '$[*]'                                 -- select all records!
    columns
       id    number  ( 1) path '$[0]' ,
       num   varchar2(10) path '$[1]' ,
       meta  varchar2( 3) path '$[2]'
    );

--      ID NUM        MET
---------- ---------- ---
--       0 zero       A
--       1 one        B
--       2 two        C
--       3 three      D
--       4 four       E
--       5 five       F

Restrict records

Setting the second parameter of json_table to $[1,2,5] only passes the corresponding records further down the pipeline:

select * from
   json_table ('[
     [0, "zero" , "A"],
     [1, "one"  , "B"],
     [2, "two"  , "C"],
     [3, "three", "D"],
     [4, "four" , "E"],
     [5, "five" , "F"]
    ]'     ,
   '$[1,2,5]'     -- Select (zero-based) records 1, 2 and 5.
    columns
       id    number  ( 1) path '$[0]' ,
       num   varchar2(10) path '$[1]' ,
       meta  varchar2( 3) path '$[2]'
    );
--
--      ID NUM        MET
---------- ---------- ---
--       1 one        B
--       2 two        C
--       5 five       F

Selecting from an array

The following example selects from an array rather than from an array of arrays.

Each item in the array is treated as a record in the result:

select * from
   json_table (
     '[0, "one", 2, 3, "four", "five"]',
     '$[1,2,5]'  -- Treat items in JSON array as records
      columns
        element varchar2(10) path '$[0]'
      );
--
--  ELEMENT
--  ----------
--  one
--  2
--  five

Treating an array as a record

The following example uses the same JSON-array, but this time, it is treated as a record:

select * from
   json_table ('[0, "one", 2, 3, "four", "five"]'
--
-- Note the second argument to json_table is not present here.
--
   columns
       item_0 varchar2(10) path '$[0]',
       item_1 varchar2(10) path '$[1]',
       item_2 varchar2(10) path '$[2]',
       item_3 varchar2(10) path '$[3]',
       item_4 varchar2(10) path '$[4]',
       item_5 varchar2(10) path '$[5]'
    );
--
-- ITEM_0     ITEM_1     ITEM_2     ITEM_3     ITEM_4     ITEM_5
-- ---------- ---------- ---------- ---------- ---------- ----------
-- 0          one        2          3          four       five

Joining tables (LEFT JOIN and NESTED)

The following example tries to demonstrate how a «real» table can be joined to a virtual table produced by json_table.

First, we create the «real» table and insert JSON data into it:

create table tq84_n (
   i number,
   j varchar2(4000)
);

insert into tq84_n values (1, '[{"id": 1, "txt": "one"   },
                                {"id": 2, "txt": "two"   },
                                {"id": 3, "txt": "three" }]'
                          );
insert into tq84_n values (2, '[{"id": 2, "txt": "zwei"  },
                                {"id": 4, "txt": "vier"  },
                                {"id": 6, "txt": "sechs" }]'
                          );
insert into tq84_n values (3, '[{"id": 3, "txt": "tres"  },
                                {"id": 6, "txt": "seis"  },
                                {"id": 9, "txt": "nueve" }]'
                          );

We're then ready to select from the table.

The following two select statements are equivalent, but using nested is arguably more friendly to read.

First select: join the tables with a left outer join.

select
   n.i,
   t.txt
from
   tq84_n n left outer join
   json_table(n.j, '$[*]'
      columns (
       id  number   path '$.id',
       txt varchar2 path '$.txt'
      )
   ) t on n.i = t.id;

Second select: using the nested clause instead.

select
   i,
   txt
from
   tq84_n nested
   j, '$[*]'
      columns (
         id  number   path '$.id',
         txt varchar2 path '$.txt'
      )
where
   i = id;

Oracle: JSON_TABLE