Correct query for jsonb_path contained in array-CodePudding

Table

CREATE TABLE users
(
    username   VARCHAR(128) PRIMARY KEY,
    info       JSONB
);
INSERT INTO users (username, info)
VALUES 
('Lana', '[
  {
    "id": "first"
  },
  {
    "id": "second"
  }
]'),
('Andy', '[
  {
     "id": "first"
  },
  {
      "id": "third"
  }
 ]');

So I want to find all users, whose info.id contained in array like ["first"].

request should be like:

SELECT * 
FROM users 
where jsonb_path_exists(info, '$.id ? (@ in ("first", "second", "third",...) )');

But I can't find the correct implementation

CodePudding user response：

You need to iterate over the array elements using $[*] then use ==

SELECT * 
FROM users 
where jsonb_path_exists(info, '$[*] ? (@.id == "first" || @.id == "second" || @.id == "third")');

Or maybe collect all IDs and use the ?| operator:

SELECT * 
FROM users 
where jsonb_path_query_array(info, '$[*].id') ?|  array['first','second','third');

That would return rows that contain at least one of those values.

If you need to find rows that contain all values, use ?& instead.

CodePudding user response：

There is no IN operator in the SQL/JSON Path language. If you want to find one of many alternatives, use a regex, e.g.

select * 
from users 
where jsonb_path_exists(info, '$.id ? (@ like_regex "^(second|third)$" )');