Select rows where jsonb field in a provided list

Question

Say I have table in Postgres with column data of type JSONB. This column, ortholog, contains entries that look like:

{
  "9823": ["ENSSSCG00000004001"], 
  "10090": ["ENSMUSG00000022347"], 
  "10116": ["ENSRNOG00000004692"]
}

I am trying to retrieve all entries with "10090" whose any member belongs to a list of ENSMUSG ids such as ENSMUSG00000022347, ENSMUSG00000022348 .. etc.

Is there a way to make a query like this?

SELECT uuid, data ->> '10090' 
  FROM "ortholog" 
 WHERE data ->> '10090' INTERSECTS WITH 
               ('ENSMUSG00000022347', 'ENSMUSG00000022348', 'ENSMUSG0000009422');

Answer 1

I am trying to retrieve all entries with "10090" whose any member belongs to a list of ENSMUSG ids

You can use operator ?| for this:

select uuid, data ->> '10090' data_10090
from ortholog
where data -> '10090' ?| array[
    'ENSMUSG00000022347', 
    'ENSMUSG00000022348', 
    'ENSMUSG0000009422'
]

From the documentation

jsonb ?| text[] → boolean

Do any of the strings in the text array exist as top-level keys or array elements?

Demo on DB Fiddle