I want to fetch the data if a particular employee id exists using JSON_EXISTS function.
But I am getting syntax error, not sure where I am wrong. Can someone suggest?

create table test (
  id int generated by default as identity primary key
 ,data json); 

insert into test(data) values
('{"description":"employee",
    "criteria": {
      "employee_id":{
        "in":["10137","12137","19137"]
      }
    }
  }')
returning jsonb_pretty(data::jsonb);

The below is the query I am using for that

select
    data
from test
where JSON_EXISTS(
                  data,
                  'criteria'->'employee_id'->'strict $.in[*] ?@ ["1299","12137"]' 
                 );

I want to fetch the data if a particular employee id exists using JSON_EXISTS function.
But I am getting syntax error, not sure where I am wrong. Can someone suggest?

create table test (
  id int generated by default as identity primary key
 ,data json); 

insert into test(data) values
('{"description":"employee",
    "criteria": {
      "employee_id":{
        "in":["10137","12137","19137"]
      }
    }
  }')
returning jsonb_pretty(data::jsonb);

The below is the query I am using for that

select
    data
from test
where JSON_EXISTS(
                  data,
                  'criteria'->'employee_id'->'strict $.in[*] ?@ ["1299","12137"]' 
                 );

• sql
• postgresql
• psql
• jsonb
• jsonpath

2 Answers 2

I think you're using wrong approach to use JSON_EXISTS, please try the below one:

select
    data
from test
where JSON_EXISTS(
    data::jsonb,  -- convert your data to jsonb first
    '$.criteria.employee_id.in'  -- corrected path of your expression
);

The example mixed in a jsonb operator ?@ as a JSONPath filter expression element, which isn't allowed. There's also a mix of -> accessors prior to the JSONPath, which is also invalid.

Move the path to the context part and fix the expression, and it'll work:
_{demo at db<>fiddle}

select data
from test
where JSON_EXISTS(data->'criteria'->'employee_id'
                 ,'strict $.in[*] ? (@=="1299" || @=="12137")' 
                 );

The path can also be merged into the JSONPath expression:

select data
from test
where JSON_EXISTS(data
                 ,'strict $.criteria.employee_id.in[*] ? (@=="1299" || @=="12137")' 
                 );

Alternatively, you could switch to jsonb and a ?| to avoid having to construct the list of == comparisons:

select data
from test
where (data->'criteria'->'employee_id'->'in')::jsonb
      ?| array['1299','12137'];

I'm ignoring wrong/mismatched brackets, missing insert target column and calling jsonb_pretty() on a non-jsonb without casting - I edited those out as secondary issues, focusing on actual SQL vs JSONPath syntax differences.