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.

question from:https://stackoverflow.com/questions/65876377/query-json-array-having-only-strings-oracle

Answer 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