Skip to Main Content
  • Questions
  • JSON Value - Oracle PL/SQL : Multiple Fields

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: December 09, 2020 - 10:36 am UTC

Last updated: December 11, 2020 - 3:36 am UTC

Version: 12c

Viewed 1000+ times

You Asked

have a HCLOB with below sample entry

 "relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}] 


When I try to get name or flag using JSON_VALUE I am able to get it as it has single field , but I want to get the value for action.

If I try
 select JSON_VALUE(JSON_CONTENT,'$.action')JSON_CONTENT from test 
I get NULL.

I read that JSON_VALUE only supports 1 entry .

Is there any workaround to get both values of action ?

and we said...

You have a few options, depending on what you want the output to be:

Access by Array Position

You can do this using either json_table or dot-notation access:

create table t (
  c1 varchar2(100)
    check ( c1 is json )
);

insert into t values ( '{ 
  "relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}] 
}' );
commit;

select t.c1.relist.action[0] v1,
       json_value (
         c1, '$.relist.action[1]'
       ) v2
from   t t;

V1        V2                   
Manager   Specific User List   


Return the Whole Array

Use JSON_query to return the entire array:

select json_query (
c1, '$.relist.action'
) arr
from t t;

ARR
["Manager","Specific User List"]

Convert the Elements to Rows

With JSON_table, you can generate a row for each element in the array:

select j.* from t, json_table (
  c1, '$.relist.action[*]'
  columns ( 
    v path '$'
  ) 
) j;

V                    
Manager               
Specific User List  

Rating

  (5 ratings)

Comments

json index on array elements.

Rajeshwaran, Jeyabal, December 10, 2020 - 3:48 am UTC

Is it possible to index each element inside an json array ?

something mentioned in this https://stackoverflow.com/questions/35562717/how-do-you-index-an-array-inside-a-json-with-an-oracle-12c-query

what index should i need to create in the below table - such that queries of the type ' select * from v where x2 = ??' will be index friendly ?

demo@XEPDB1> create table t(x varchar2(4000) constraint t_chk check( x is json) );

Table created.

demo@XEPDB1>
demo@XEPDB1>  insert into t(x) values('
  2     [
  3         {
  4             "foo": "100083087",
  5             "bar": [
  6                 {
  7                     "let": "DIV",
  8                     "letID": "100083088"
  9                 }
 10             ]
 11         },
 12         {
 13             "foo": "100032830",
 14             "bar": [
 15                 {
 16                     "let": "DIV",
 17                     "letID": "1000832333"
 18                 },
 19                 {
 20                     "let": "TET",
 21                     "letID": "2234832333"
 22                 }
 23             ]
 24         }
 25     ]');

1 row created.

demo@XEPDB1>    commit;

Commit complete.

demo@XEPDB1> create or replace view v
  2  as
  3  select jt.*
  4  from t, json_table( x, '$' columns
  5     ( foo varchar2(20) path '$.foo' error on error ,
  6       nested path '$.bar[*]'
  7       columns(
  8             x1 varchar2(20) path '$.let' error on error ,
  9             x2 varchar2(20) path '$.letID' error on error )) ) jt
 10  /

View created.

demo@XEPDB1> select * From v;

FOO                  X1                   X2
-------------------- -------------------- --------------------
100083087            DIV                  100083088
100032830            DIV                  1000832333
100032830            TET                  2234832333

demo@XEPDB1>








mysql json array index.

Rajeshwaran, Jeyabal, December 10, 2020 - 6:33 am UTC

Something like this https://www.slideshare.net/NorvaldRyeng/json-array-indexes-in-mysql is possible in Oracle database ? (please see the slide no # 6 and #7 for more details and a working test case of it)

json index on array elements.

Rajeshwaran, Jeyabal, December 11, 2020 - 7:48 am UTC

Thanks for the details.

But why there is no "Changes in Oracle Database Release 21c for JSON development" in the above link? at the chapter#1.

so that we can easily peak into the new features/changes introduced in Oracle 21c.

https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/intro-to-json-data-and-oracle-database.html#GUID-17642E43-7D87-4590-8870-06E9FDE9A6E9

@ Rajeshwaran : Because...

Stewart Ashton, December 11, 2020 - 9:58 am UTC

Instead of answering your question as asked, I'll point you to a better starting point:

https://apex.oracle.com/database-features/

Click on "21c" and "New features only".

To cast the widest net, enter "json" in the search bar and hit enter. There are 19 entries. You can also choose the Focus Area "Application Development" and the subcategory JSON.

Every item includes a link to the documentation.

Best regards,
Stew Ashton

@stew_ashton

Rajeshwaran, Jeyabal, December 12, 2020 - 12:29 pm UTC

Thanks Stew. i understand - and that's how i navigate in "new features" guide.

Put please see this link ( Oracle 21c - MultiTenant guide) - that got separate section on " Changes in Oracle Database Release 21c for Oracle MultiTenant Administrator’s Guide " - to get those new features quickly, but that is missing in JSON guide - hence this questions is.


https://docs.oracle.com/en/database/oracle/oracle-database/21/multi/introduction-to-the-multitenant-architecture.html#GUID-267F7D12-D33F-4AC9-AA45-E9CD671B6F22

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.