Skip to Main Content
  • Questions
  • Obtain all JSON keys from JSON columns

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, gabriela.

Asked: January 30, 2018 - 2:03 pm UTC

Answered by: Chris Saxon - Last updated: August 12, 2020 - 7:56 am UTC

Category: SQL Developer - Version: 12

Viewed 10K+ times! This question is

You Asked

Hello,

Is there a way to get all the keys that exist in a JSON column? JSON_VALUE function provides an easy way to access a value for a given key, but is there a way to obtain all the key names?

Thanks,
Gabi

and we said...

On 12.2 this is easy.

PL/SQL comes with JSON object types which include a get_keys method. Which, as you may guess, returns the keys!

But this only returns the top-level attributes. If you have objects nested within arrays or other objects you can pull the attributes out. But the code is a little kludgy!

For example:

create table t (
  dept_json varchar2(1000) check (dept_json is json)
);

insert into t values ('{
  "department": "Accounting",
  "employees": [
    {
      "name": "Shelley,Higgins",
      "job": "Accounting Manager"
    },
    {
      "name": "William,Gietz",
      "job": "Public Accountant"
    }
  ]
}
');
commit;

declare
  jdoc t.dept_json%type;
  jobj json_object_t;
  keys json_key_list;
  jarr json_array_t;
  arrkeys json_key_list;
  elem json_element_t;
begin

  select dept_json 
  into   jdoc
  from   t;
  
  jobj := json_object_t ( jdoc );
    
  keys := jobj.get_keys;
  
  for i in 1 .. keys.count loop
    dbms_output.put_line(keys(i));
    if jobj.get(keys(i)).is_array then
      jarr := jobj.get_array(keys(i));
      elem := jarr.get(1);
      arrkeys := treat ( elem as json_object_t ).get_keys;
      for j in 1 .. arrkeys.count loop
        dbms_output.put_line('  ' || arrkeys(j));
      end loop;
    end if;
  end loop;
end;
/

department
employees
  name
  job


Luckily there is another way: JSON Data Guide.

There's a new function, json_dataguide, which returns the "schema" of a JSON document. Just pass your JSON document to this to get it:

set long 100000
select json_dataguide(dept_json) jschema
from   t;

[
  {
    "o:path": "$.employees",
    "type": "array",
    "o:length": 128
  },
  {
    "o:path": "$.employees.job",
    "type": "string",
    "o:length": 32
  },
  {
    "o:path": "$.employees.name",
    "type": "string",
    "o:length": 16
  },
  {
    "o:path": "$.department",
    "type": "string",
    "o:length": 16
  }
]


Even better, you can use the data guide to expose non-array values as virtual columns in the source table. Or generate views over the arrays. So you can see the attributes and their values in regular SQL :)

Read more about this at:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-dataguide.html#GUID-219FC30E-89A7-4189-BC36-7B961A24067C

But as I say, this is all 12.2 functionality. If you're still on 12.1 you'll have to roll your own...

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

January 30, 2018 - 3:28 pm UTC

Reviewer: gabriela visinari

Thank you, works perfectly.

json search index rebuild.

February 20, 2018 - 1:35 am UTC

Reviewer: Rajeshwaran Jeyabal

demo@ORA12C> create table t(x int,y varchar2(50) );

Table created.

demo@ORA12C> insert into t values(1,'{a:55,b:42}');

1 row created.

demo@ORA12C>
demo@ORA12C> create search index t_idx on t(y)
  2  for json
  3  parameters( 'search_on none' );
create search index t_idx on t(y)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10720: syntax only supported with constraint: IS JSON
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 316


demo@ORA12C> select index_name, status,domidx_status,domidx_opstatus
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME STATUS   DOMIDX_STATU DOMIDX
---------- -------- ------------ ------
T_IDX      VALID    VALID        FAILED


without the IS JSON check constraint, json search index creation got failed.

demo@ORA12C> alter table t add constraint t_chk check( y is json);

Table altered.

demo@ORA12C> alter index t_idx rebuild ;
alter index t_idx rebuild
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20000: Oracle Text error:
DRG-10561: index T_IDX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 691


demo@ORA12C> select index_name, status,domidx_status,domidx_opstatus
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME STATUS   DOMIDX_STATU DOMIDX
---------- -------- ------------ ------
T_IDX      VALID    VALID        FAILED

demo@ORA12C>


Can't we rebuild the index once the constraint got added ?
Chris Saxon

Followup  

February 20, 2018 - 1:30 pm UTC

Not as far as I know. You have to drop-recreate it.

estimate_percent=>99.

August 21, 2019 - 2:26 pm UTC

Reviewer: Rajeshwaran Jeyabal

Team,

the below testcase was from 12.2 database and the question is about

when estimate_percent=99 the DATAGUIDE got more information (like frequency,low_value,high_value,num_nulls etc) about the attributes.

however that is not possible with estimate_percent set to its default values (auto_sample_size) - who so? Kindly advice.

demo@PDB1> create table t(x varchar2(40) constraint t_chk
  2     check(x is json) );

Table created.

demo@PDB1> insert into t(x) values('{a:55,b:"Hello",c:"World"}');

1 row created.

demo@PDB1> commit;

Commit complete.

demo@PDB1>
demo@PDB1> create search index t_idx on t(x)
  2  for json ;

Index created.

demo@PDB1> select json_query( dataguide ,'$' pretty )
  2  from user_json_dataguides
  3  where table_name ='T';

JSON_QUERY(DATAGUIDE,'$'PRETTY)
-------------------------------------------------------------------------------------------------
[
  {
    "o:path" : "$.a",
    "type" : "number",
    "o:length" : 2,
    "o:preferred_column_name" : "X$a"
  },
  {
    "o:path" : "$.b",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$b"
  },
  {
    "o:path" : "$.c",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$c"
  }
]


demo@PDB1> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

demo@PDB1> select json_query( dataguide ,'$' pretty )
  2  from user_json_dataguides
  3  where table_name ='T';

JSON_QUERY(DATAGUIDE,'$'PRETTY)
-------------------------------------------------------------------------------------------------
[
  {
    "o:path" : "$.a",
    "type" : "number",
    "o:length" : 2,
    "o:preferred_column_name" : "X$a"
  },
  {
    "o:path" : "$.b",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$b"
  },
  {
    "o:path" : "$.c",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$c"
  }
]


demo@PDB1> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,estimate_percent=>99);

PL/SQL procedure successfully completed.

demo@PDB1> select json_query( dataguide ,'$' pretty )
  2  from user_json_dataguides
  3  where table_name ='T';

JSON_QUERY(DATAGUIDE,'$'PRETTY)
-------------------------------------------------------------------------------------------------
[
  {
    "o:path" : "$.a",
    "type" : "number",
    "o:length" : 2,
    "o:preferred_column_name" : "X$a",
    "o:frequency" : 100,
    "o:low_value" : "55",
    "o:high_value" : "55",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-21T19:50:08"
  },
  {
    "o:path" : "$.b",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$b",
    "o:frequency" : 100,
    "o:low_value" : "Hello",
    "o:high_value" : "Hello",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-21T19:50:08"
  },
  {
    "o:path" : "$.c",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$c",
    "o:frequency" : 100,
    "o:low_value" : "World",
    "o:high_value" : "World",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-21T19:50:08"
  }
]


demo@PDB1>

Chris Saxon

Followup  

August 22, 2019 - 4:39 pm UTC

I think what you're seeing here is a problem of a small data set. When sampling from a few rows, there's always the chance you'll get nothing.

When loading it with more data on 19c I see:

create table t(x varchar2(40) constraint t_chk
  check(x is json) 
);

insert into t(x) values('{a:1,b:"Hello",c:"World"}');
insert into t(x) values('{a:5,b:"Hello",c:"World"}');
insert into t(x) values('{a:50,b:"Hello",c:"World"}');
insert into t(x) values('{a:500,b:"Hello",c:"World"}');
insert into t(x) values('{a:5000,b:"Hello",c:"World"}');

insert into t 
  select * from t;
  
insert into t 
  select * from t;
  
insert into t 
  select * from t;

commit;

create search index t_idx on t(x)
 for json ;

exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

select json_query( dataguide ,'$' pretty )
from   user_json_dataguides
where  table_name ='T';

JSON_QUERY(DATAGUIDE,'$'PRETTY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
[
  {
    "o:path" : "$.a",
    "type" : "number",
    "o:length" : 4,
    "o:preferred_column_name" : "X$a",
    "o:frequency" : 100,
    "o:low_value" : "5",
    "o:high_value" : "5",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-22T12:37:49"
  },
  {
    "o:path" : "$.b",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$b",
    "o:frequency" : 100,
    "o:low_value" : "Hello",
    "o:high_value" : "Hello",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-22T12:37:49"
  },
  {
    "o:path" : "$.c",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$c",
    "o:frequency" : 100,
    "o:low_value" : "World",
    "o:high_value" : "World",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-22T12:37:49"
  }
]    


What happens when you do this?

estimate_percent=>99.

August 23, 2019 - 2:03 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Thanks. it works with some decent amount of data in place.

demo@PDB1> create table t(x varchar2(40) constraint t_chk
  2     check(x is json) );

Table created.

demo@PDB1> insert into t(x)
  2  select '{a:55,b:"Hello",c:"World"}'
  3  from all_objects;

60784 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> create search index t_idx on t(x)
  2  for json ;

Index created.

demo@PDB1>
demo@PDB1> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

demo@PDB1> select json_query( dataguide,'$' pretty )
  2  from user_json_dataguides
  3  where table_name ='T' ;

JSON_QUERY(DATAGUIDE,'$'PRETTY)
------------------------------------------------------------------------
[
  {
    "o:path" : "$.a",
    "type" : "number",
    "o:length" : 2,
    "o:preferred_column_name" : "X$a",
    "o:frequency" : 100,
    "o:low_value" : "55",
    "o:high_value" : "55",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-23T19:31:26"
  },
  {
    "o:path" : "$.b",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$b",
    "o:frequency" : 100,
    "o:low_value" : "Hello",
    "o:high_value" : "Hello",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-23T19:31:26"
  },
  {
    "o:path" : "$.c",
    "type" : "string",
    "o:length" : 8,
    "o:preferred_column_name" : "X$c",
    "o:frequency" : 100,
    "o:low_value" : "World",
    "o:high_value" : "World",
    "o:num_nulls" : 0,
    "o:last_analyzed" : "2019-08-23T19:31:26"
  }
]


demo@PDB1>

questions about array unwrapping in dataguide.

August 12, 2020 - 4:08 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

was reading this docs
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/json-dataguide.html#GUID-394009D2-F829-49B0-B6EF-DB24606FEF75

<quote>
If you use procedure create_view_on_path then the PATH argument you provide must be a simple SQL/JSON path expression (no filter expression), possibly with relaxation (implicit array wrapping and unwrapping), but with no array steps and no function step.
<quote>

Below is my test case (from Oracle 12.2), when i say " $.ContactDetails.doorno " the array element are not getting unwrapped, but when i say " $.Contact_no" array are getting unwrapped correctly. why so - kindly explain.

demo@PDB1> CREATE TABLE json_documents (
  2    id    number primary key,
  3    doc  CLOB check( doc is json)
  4  );

Table created.

demo@PDB1> insert into json_documents( id, doc) values(1, '{
  2            "FirstName"      : "John",
  3            "LastName"       : "Doe",
  4            "Job"            : "Clerk",
  5            "Address"        : {
  6                                "Street"   : "99 My Street",
  7                                "City"     : "My City",
  8                                "Country"  : "UK",
  9                                "Postcode" : "A12 34B"
 10                               },
 11               "Contact_no" : [ { "type" : "Office", "number" : "909-555-7307" },
 12                                      { "type" : "Mobile", "number" : "415-555-1234" } ],
 13            "ContactDetails" : {
 14                                "Email"    : "john.doe@example.com",
 15                                "Phone"    : "44 123 123456",
 16                                "Twitter"  : "@johndoe",
 17                                                       "doorno"   : [55,75]
 18                               },
 19            "DateOfBirth"    : "01-JAN-1980",
 20            "Active"         : true
 21           }' );

1 row created.

demo@PDB1>
demo@PDB1> create search index json_documents_idx on json_documents(doc)
  2  for json
  3  parameters( 'dataguide on' );

Index created.

demo@PDB1> begin
  2      dbms_json.create_view_on_path('json_doc_vw','JSON_DOCUMENTS','DOC','$.ContactDetails.doorno');
  3  end;
  4  /

PL/SQL procedure successfully completed.

demo@PDB1> desc json_doc_vw
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 ID                                  NOT NULL NUMBER
 DOC$Job                                      VARCHAR2(8)
 DOC$Active                                   VARCHAR2(4)
 DOC$City                                     VARCHAR2(8)
 DOC$Street                                   VARCHAR2(16)
 DOC$Country                                  VARCHAR2(2)
 DOC$Postcode                                 VARCHAR2(8)
 DOC$LastName                                 VARCHAR2(4)
 DOC$FirstName                                VARCHAR2(4)
 DOC$DateOfBirth                              VARCHAR2(16)
 DOC$Email                                    VARCHAR2(32)
 DOC$Phone                                    VARCHAR2(16)
 DOC$doorno                                   VARCHAR2(4000)
 DOC$Twitter                                  VARCHAR2(8)

demo@PDB1> col  "DOC$doorno" for a10
demo@PDB1> select "DOC$doorno"
  2  from json_doc_vw ;

DOC$doorno
----------
[55,75]

demo@PDB1> begin
  2      dbms_json.create_view_on_path('json_doc_vw','JSON_DOCUMENTS','DOC','$.Contact_no');
  3  end;
  4  /

PL/SQL procedure successfully completed.

demo@PDB1> select count(*)
  2  from json_doc_vw;

  COUNT(*)
----------
         2

demo@PDB1>

Chris Saxon

Followup  

August 12, 2020 - 7:56 am UTC

The issue isn't present in 19c:

create table json_documents (
  id    number primary key,
  doc  clob check( doc is json)
);

insert into json_documents( id, doc) values(1, '{
    "FirstName"      : "John",
    "LastName"       : "Doe",
    "Job"            : "Clerk",
    "Address"        : {
                        "Street"   : "99 My Street",
                        "City"     : "My City",
                        "Country"  : "UK",
                        "Postcode" : "A12 34B"
                       },
       "Contact_no" : [ { "type" : "Office", "number" : "909-555-7307" },
                        { "type" : "Mobile", "number" : "415-555-1234" } ],
    "ContactDetails" : {
                        "Email"    : "john.doe@example.com",
                        "Phone"    : "44 123 123456",
                        "Twitter"  : "@johndoe",
                        "doorno"   : [55,75]
                       },
    "DateOfBirth"    : "01-JAN-1980",
    "Active"         : true
   }' );

create search index json_documents_idx on json_documents(doc)
  for json
  parameters( 'dataguide on' );

begin
  dbms_json.create_view_on_path('json_doc_vw','JSON_DOCUMENTS','DOC','$.ContactDetails.doorno');
end;
/

select id, "DOC$number_1"
from   json_doc_vw ;

ID    DOC$number_1   
    1              55 
    1              75 


So looks like a bug that's been fixed.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.