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

Postgresql – How can I filter a jsonb field by multiple keys?

I have groups that have a jsonb field – targets
my sql

SELECT DISTINCT groups.* 
FROM groups 
WHERE groups.user_id = 1 AND (
  EXISTS (
    SELECT * FROM jsonb_array_elements(targets) as a 
    WHERE a->>'name' ~* 'gender|age'
  ) 
)

this will return groups that contain only gender or only age, or both gender and age together.

How to fix it so that it returns only groups that have both gender and age

my jsonb field targets looks like this

[{"name"=>"gender", "value"=>"0"}, {"name"=>"age", "value"=>"1"}]
Tags:

2

Answers


  1. 0

    How to fix it so that it returns only groups that have both gender and
    age

    You can achieve this by using the GROUP BY and HAVING clauses, ensuring the presence of both elements by employing the count() function and the case clause :

    SELECT user_id, targets
FROM groups
CROSS JOIN jsonb_array_elements(targets) as a 
GROUP BY user_id, targets
HAVING COUNT( CASE WHEN a->>'name' = 'gender' THEN 1 END) = 1
       AND COUNT( CASE WHEN a->>'name' = 'age' THEN 1 END) = 1;

    Demo here

    Login or Signup to reply.
  2. 0

    You can use jsonb_path_exists. See the docs.

    SELECT
  g.*
FROM groups g
WHERE g.user_id = 1
  AND jsonb_path_exists(g.targets, '$[*].name = "gender"')
  AND jsonb_path_exists(g.targets, '$[*].name = "age"');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search