Extract json-array for use in SELECT ... IN clause

Question

Not sure what postgresql function should I use to extract an array from a JSONB object and use it later in select ... in clause. Preferably as a result set with one integer column. From the json below I want to extract types

{
    "data": {
        "types": [
            1,
            2,
            3
        ]
    }
}

I've tried the following but it triggers an error:

select * from json_array_elements(
select * from jsonb_extract_path_text('
{
    "data": {
        "types": [
            1,
            2,
            3
        ]
    }
}
', 'data', 'types')
);

Maybe I'm missing the right syntax/function ?

Answer 1

In PostgreSQL 12+, you could use set-returning jsonb_path_query(): demo

select * from test 
where id in (
select jsonb_path_query('{"data": {"types": [1,2,3]}}'::jsonb,
                        '$.data.types[*]')::int);

In earlier versions without JSONPath, as @Diego D suggested, you could use jsonb_array_elements():

select * from test 
where id in (
select jsonb_array_elements('{"data": {"types": [1,2,3]}}'::jsonb#>'{data,types}')::int);

Both INs can also be replaced with an inner join. The second solution might fail if a given jsonb doesn't have an array at that path. The first one doesn't mind.