How to pass parameter with double quote inside JSON_EXISTS Oracle SQL Developer

Question

I have created a table

CREATE TABLE json_data(
                       json_col   VARCHAR2(1000), 
                       CONSTRAINT must_be_json CHECK (json_col IS JSON )
                      )

Inserted Data into table

INSERT INTO json_data VALUES ('{ "abc" : { "fname" : "john" ,"lname" : "rambo" }}')
INSERT INTO json_data VALUES ('{ "abc" : { "fname" : "james" ,"lname" : "bond" }}')

Need to select value using below query

SELECT json_col FROM json_data 
WHERE JSON_EXISTS(json_col, '$.abc') 
    AND JSON_EXISTS(json_col,'$?(@.abc.name == "john")')

works fine without problem, however I need to pass value "John" as parameter like..

SELECT json_col FROM json_data 
WHERE JSON_EXISTS(json_col, '$.abc') 
    AND JSON_EXISTS(json_col,'$?(@.abc.name == :johnParam)'

where :johnParam is a parameter

its not taking as parameter, please help me how to pass :johnParam parameter with double quotes

Is your real question how to use a parameter in a JSON_EXISTS expression? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2021 at 11:15
There are no double quotes in the data, so what do double quotes have to do with this? The string values don't contain their delimiters. The real question should be whether you can use a parameter with JSON_EXISTS at all. If you can, you could pass the entire expression as a parameter. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2021 at 11:18
You assume you can pass a parameter inside a string in the first place. You can't. Parameters aren't string replacement variables, they are similar to Java function parameters. Maybe you can pass the entire expression as a parameter and write AND JSON_EXISTS(json_col,:someParam) but some SO questions suggest this isn't possible either — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2021 at 11:37
You may be able to use the PASSING clause to include an external value in the JSON Path expression, eg json_exists(po_document, '$.PONumber?(@ > $d)' PASSING 1500 AS "d"). Perhaps you can use a parameter in PASSING, eg PASSING :myParam AS "d") — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2021 at 12:06

Chris Saxon · Accepted Answer · 2021-01-11 15:27:47Z

If you want to pass a variable to a JSON path in json_exists, use the passing clause:

create table json_data (
  json_col varchar2(1000)
    check ( json_col is json )
);
insert into json_data 
  values ('{ "abc" : { "fname" : "john" ,"lname" : "rambo" }}');
insert into json_data 
  values ('{ "abc" : { "fname" : "james" ,"lname" : "bond" }}');

select json_col from json_data 
where  json_exists ( 
  json_col,
  '$?(@.abc.fname == $johnparam)'
  passing 'john' as "johnparam"
);

JSON_COL                                             
{ "abc" : { "fname" : "john" ,"lname" : "rambo" }}

'john' in the passing clause becomes :johnParam in your application.

To search for documents where an attribute equals a specific value, you may find it easier to use simple dot-notation or json_value instead:

select * from json_data j
where  j.json_col.abc.fname = 'john';

JSON_COL                                             
{ "abc" : { "fname" : "john" ,"lname" : "rambo" }}   

select * from json_data j
where  json_value ( json_col, '$.abc.fname' ) = 'john';

JSON_COL                                             
{ "abc" : { "fname" : "john" ,"lname" : "rambo" }}

Then you can replace 'john' with :johnParam as you would for queries against non-JSON data.

Barbaros Özhan · Accepted Answer · 2021-01-11 14:57:46Z

0

You can use substitution parameter with ampersand such as &name with the following query

SELECT json_col
  FROM json_data
 WHERE JSON_EXISTS(json_col, '$.abc?(@.fname == "&name")')

where the part JSON_EXISTS(json_col, '$.abc') seems redundant. When prompted, enter john without quotes as value for $name.

answered Jan 11, 2021 at 14:57

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

How to pass parameter with double quote inside JSON_EXISTS Oracle SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related