Question 313983 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres check existence of any array element within JSONB column with another array

I am working on PostgreSQL 13.9. I want to check an array within a JSONB with another input array to see if there was any overlap of elements between the two then return true in the sample SQL below

 select 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id2[*] ?| ["5","7"]))'::jsonpath

I am getting error ERROR: syntax error at or near "|" of jsonpath
input LINE 7: }’::jsonb @? ‘$ ? ((@.id2[*] ?| ["5","7"]))’::jsonpath
^
SQL state: 42601
Character: 153

Same works when i compare with a single element as shown below and returns "true"

select 
  '  {
  "id1": ["9"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id1 == "9") && (@.id2[*] == "5"))'::jsonpath

I am not able to identify what i am doing wrong. I want to do in a perf efficient manner. So do not want to write with multiple OR like

(@.id2[*]== "5" || @.id2[*] == 7)

Any pointers on what i am doing wrong will be of great help

Tags:

2

Answers


  1. 0

    Quick and dirty answer using jsonb_path_query from here JSON functions/operators Table 9.49. JSON Processing Functions:

    select jsonb_path_query( 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb, '($.id2)') ?| array['5','7'];
?column? 
----------
 t

select jsonb_path_query( 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb, '($.id3)') ?| array['5','7'];
 ?column? 
----------
 f
    Login or Signup to reply.
  2. 0

    As already pointed out, ?| you’re trying to use exists as plain PostgreSQL operator, but it’s not offered by this implementation of JSONPath, therefore cannot be used in such expression. Also, you can’t use array or object literals in the expression, so ["5","7"] can’t be used in there, either.

    If what you have is a jsonb object you want compare with another, you can pass the whole thing as a variable into the expression. The @? operator doesn’t allow that, but jsonb_path_exists() does. Once you have both objects in the JSONPath, they can both use [*] accessor:

    select jsonb_path_exists('{"id1": ["5"],  "id2": ["5"],
                           "id3": ["cc","dd" ],
                           "id4": "15",   "id5": "11",
                           "id6": "2",  "id7": ["12","18"]
                          }'::jsonb,
                         '$.id2[*]?(@==$var.id2[*])',
                         '{"var":{"id2":["5","7"]}}'::jsonb);
    jsonb_path_exists
    t

    Or you can add it in:

    select jsonb_path_exists('{"id1": ["5"],  "id2": ["5"],
                           "id3": ["cc","dd" ],
                           "id4": "15",   "id5": "11",
                           "id6": "2",  "id7": ["12","18"]
                          }'::jsonb||'{"var":{"id2":["5","7"]}}'::jsonb,
                         '$.id2[*]?(@==$.var.id2[*])');
    jsonb_path_exists
    t

    Or you can access the key with a regular -> and use your text[] array with ?| directly:

    select 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb->'id2' ?| '{5,7}'
    ?column?
    t

    Demo at db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search