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

Correct query for jsonb_path contained in array – Postgresql

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

Tags:

2

Answers


  1. 0

    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)$" )');
    Login or Signup to reply.
  2. 0

    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.

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

Back To Top
Search