Get value from json dimensional array in oracle

Question

I have below JSON from which i need to fetch the value of issuedIdentValue where issuedIdentType = PANCARD

{
    "issuedIdent": [ 
        {"issuedIdentType":"DriversLicense","issuedIdentValue":"9797979797979797"},
        {"issuedIdentType":"SclSctyNb","issuedIdentValue":"078-01-8877"},
        {"issuedIdentType":"PANCARD","issuedIdentValue":"078-01-8877"}
    ]
}

I can not hard-code the index value [2] in my below query as the order of these records can be changed. So want to get rid off any hardcoded index.

select json_value(
    '{"issuedIdent": [{"issuedIdentType":"DriversLicense","issuedIdentValue":"9797979797979797"},{"issuedIdentType":"SclSctyNb","issuedIdentValue":"078-01-8877"},  {"issuedIdentType":"PANCARDSctyNb","issuedIdentValue":"078-01-8877"}]}', 
    '$.issuedIdent[2].issuedIdentValue'
) as output 
from d1entzendev.ExternalEventLog 
where 
    eventname = 'CustomerDetailsInqSVC' 
    and applname = 'digitalBANKING' 
    and requid = '4fe1fa1b-abd4-47cf-834b-858332c31618';

What changes will need to apply in json_value function to achieve the expected result

GMB · Accepted Answer · 2020-10-28 10:15:28Z

1

In Oracle 12c or higher, you can use JSON_TABLE() for this:

select value
from json_table(
    '{"issuedIdent": [{"issuedIdentType":"DriversLicense","issuedIdentValue":"9797979797979797"},{"issuedIdentType":"SclSctyNb","issuedIdentValue":"078-01-8877"},  {"issuedIdentType":"PANCARD","issuedIdentValue":"078-01-8877"}]}',
    '$.issuedIdent[*]' columns
         type   varchar(50) path '$.issuedIdentType',
         value  varchar(50) path '$.issuedIdentValue'
) t
where type = 'PANCARD'

This returns:

| VALUE       |
| :---------- |
| 078-01-8877 |

answered Oct 28, 2020 at 10:15

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

deltaforce Over a year ago

If i have to get 5 different array sets from same JSON then i will have to run 5 SQL statements. Isn't there any other solution in which i could get the expected result from json_value or any other single function...Just want to avoid multiple select statements

deltaforce Over a year ago

any suggestion on my previous comment?

GMB Over a year ago

@deltaforce: without seeing actual data, I can’t really tell. I would suggest that you ask a new question for this, providing proper sample data and desired results.

Collectives™ on Stack Overflow

Get value from json dimensional array in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related