Oracle SQL/JSON

Creating a table for JSON documents

create table tq84_json_documents (
   id   number generated always as identity,
   doc  clob,
   --
   constraint json_doc_pk primary key (id),
   constraint doc_is_json check(doc is json)
);

insert into tq84_json_documents (doc) values ('{num: 1}');
 
-- check constraint violation
-- insert into tq84_json_documents (doc) values ('{num: }');

Querying JSON data

select
   *
from
   json_table('
{
   "num"    :     42,
   "txt"    :     "Hello world",
   "ary"    :    ["elem_0", 1]
}',
'$'
   columns (
      num             number                   path '$.num'   ,
      txt             varchar2(20)             path '$.txt'   ,
      ary             varchar2(20) FORMAT JSON path '$.ary'   ,
      ary_1st_elem    varchar2(20)             path '$.ary[0]',
      ary_2nd_elem    number                   path '$.ary[1]'
   )
);

select
   *
from
   json_table('
[
   {
      "num"    :     42,
      "txt"    :    "Hello world",
      "ary"    :   ["elem_0", 1]
   },
   {
      "num"    :     99,
      "txt"    :    "ninety-nine",
      "ary"    :   ["red balllons", 55.5]
   },
   {
      "num"    :     17,
      "txt"    :    "xyz",
      "ary"    :   ["abc", 1.7]
   }
]',
  '$[*]'  -- Note the [*]
   columns (
      num             number                   path '$.num'   ,
      txt             varchar2(20)             path '$.txt'   ,
  --  ary             varchar2(20) format json path '$.ary'   ,
      ary_1st_elem    varchar2(20)             path '$.ary[0]',
      ary_2nd_elem    number                   path '$.ary[1]'
   )
);

OSON

Misc / TODO

create table tq84_json( j json ); 

create table tq84_json( j blob, check (j is json format oson) ); 

See also