query json array having only strings oracle

Question

Below is the json stored in a table called "Sample" and the column name is "argument". I want to fetch all those records having a particular value in a specified argument. I could query the argument name but not able to query a particular value as it is an array of strings. (Please find my keys have . in it)

{
 "arguments":{
    "app.argument1.appId":["123", "456"],
    "app.argument2.testId":["546", "567"]
 }
}

This gives me all the records having particular argument.

 select * from sample where json_exists(argument, '$.arguments."app.argument1.appId"');

But I need to match argument value. I tried below but getting JSON expression error.

select * from sample where json_exists(argument, '$.arguments."app.argument1.appId[*]"?(@ == "123"));

Please help. Thanks in advance.

MT0 · Accepted Answer · 2021-01-25 00:22:25Z

1

You have the quotation marks in the wrong place; you want the double quotes before the square-brackets for the array instead of afterwards:

select *
from   sample
where  json_exists(
         argument,
         '$.arguments."app.argument1.appId"[*]?(@ == "123")'
       );

Which, for the sample data:

CREATE TABLE sample ( argument CLOB CHECK ( argument IS JSON ) );

INSERT INTO sample ( argument ) VALUES ( '{
 "arguments":{
    "app.argument1.appId":["123", "456"],
    "app.argument2.testId":["546", "567"]
 }
}');

Outputs:

| ARGUMENT                                                                                                                 |
| :----------------------------------------------------------------------------------------------------------------------- |
| {<br> "arguments":{<br>    "app.argument1.appId":["123", "456"],<br>    "app.argument2.testId":["546", "567"]<br> }<br>} |

db<>fiddle here

Do you know a way to do this in 12.1?

You could also use EXISTS with a correlated JSON_TABLE (which is available from Oracle 12c Release 1 (12.1.0.2)).:

select *
from   sample
where  EXISTS (
  SELECT 1
  FROM   JSON_TABLE(
           argument,
           '$.arguments."app.argument1.appId"[*]'
           COLUMNS (
             value VARCHAR2(100) PATH '$'
           )
         )
  WHERE  value = '123'
);

db<>fiddle here

edited Jan 25, 2021 at 0:22

answered Jan 24, 2021 at 22:15

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

rishi Over a year ago

I had already tried this query. I just found out this syntax is supported from 12.2 and I'm running Oracle 12.1. Do you know a way to do this in 12.1?

MT0 Over a year ago

@rishi Updated. If you are using 12.1.0.2 then you can use JSON_TABLE.

Collectives™ on Stack Overflow

query json array having only strings oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related