Correct query for jsonb_path contained in array

Question

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

klin · Accepted Answer · 2022-11-24 07:37:01Z

2

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)$" )');

edited Nov 24, 2022 at 7:37

answered Nov 24, 2022 at 7:31

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2022-11-24 07:43:10Z

1

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.

edited Nov 24, 2022 at 7:43

answered Nov 24, 2022 at 7:35

user330315

2 Comments

platypus Over a year ago

I can, but I need to check for content in an array. (corrected description)

user330315 Over a year ago

You can use an OR condition

Collectives™ on Stack Overflow

Correct query for jsonb_path contained in array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related