Find rows based on nested key in jsonb array

Question

I have a jsonb column in Postgres 9.6 that contains JSON arrays of the following form:

[
  {
    "courses": { "course-1": { "graduated": false }, "course-5": { "graduated": true } }
  },
  {
    "courses": { "course-6": { "graduated": false } }
  }
]

I want to find all of the users who have enrolled in either course-1 or course-12 with a single query. That is, users who have either course-1 or course-12 in the courses object for any of the entries in their jsonb array.

I've tried a bunch of things like the following, which of course doesn't work:

select enrollment_info from users where (enrollment_info @> '["courses" ?| array['course-1', 'course-12']]')

Any suggestions on how to solve this issue? Thanks!

GMB · Accepted Answer · 2019-10-14 22:30:01Z

You can use jsonb_array_elements to unnest the array and then check if at least one of the searched keys exists:

select enrollment_info
from users,
jsonb_array_elements(enrollment_info) courses
where 
    courses->'courses'->'course-1' is not null
    or courses->'courses'->'course-12' is not null

Demo on DB Fiddle:

with users as (
    select 
    '[ 
        { "courses": { "course-1": { "graduated": false }, "course-5": { "graduated": true } }},
        { "courses": { "course-6": { "graduated": false } } }
    ]'::jsonb enrollment_info
    union all select 
    '[ 
        { "courses": { "course-12": { "graduated": false }, "course-5": { "graduated": true } }}
    ]'::jsonb
    union all select 
    '[ 
        { "courses": { "course-4": { "graduated": false } }}
    ]'::jsonb
)
select enrollment_info
from users,
jsonb_array_elements(enrollment_info) courses
where 
    courses->'courses'->'course-1' is not null
    or courses->'courses'->'course-12' is not null

| enrollment_info                                                                                                                     |
| :---------------------------------------------------------------------------------------------------------------------------------- |
| [{"courses": {"course-1": {"graduated": false}, "course-5": {"graduated": true}}}, {"courses": {"course-6": {"graduated": false}}}] |
| [{"courses": {"course-5": {"graduated": true}, "course-12": {"graduated": false}}}]                                                 |

The first two arrays match since they contain respectively course-1 and course-12. The third array does not match.

Collectives™ on Stack Overflow

Find rows based on nested key in jsonb array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related