Is there a jsonb array overlap function for postgres?

Question

Am not able to extract and compare two arrays from jsonb in postgres to do an overlap check. Is there a working function for this?

Example in people_favorite_color table:

{
    "person_id":1,
    "favorite_colors":["red","orange","yellow"]
}
{
    "person_id":2,
    "favorite_colors":["yellow","green","blue"]
}
{
    "person_id":3,
    "favorite_colors":["black","white"]
}

Array overlap postgres tests:

select 
p1.json_data->>'person_id',
p2.json_data->>'person_id',
p1.json_data->'favorite_colors' && p2.json_data->'favorite_colors'
from people_favorite_color p1 join people_favorite_color p2 on (1=1)
where p1.json_data->>'person_id' < p2.json_data->>'person_id'

Expected results:

p1.id;p2.id;likes_same_color
1;2;t
1;3;f
2;3;f

--edit-- Attempting to cast to text[] results in an error:

select
('{
        "person_id":3,
        "favorite_colors":["black","white"]
}'::jsonb->>'favorite_colors')::text[];

ERROR:  malformed array literal: "["black", "white"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

Answer 1

Use array_agg() and jsonb_array_elements_text() to convert jsonb array to text array:

with the_data as (
    select id, array_agg(color) colors
    from (
        select json_data->'person_id' id, color
        from 
            people_favorite_color, 
            jsonb_array_elements_text(json_data->'favorite_colors') color
        ) sub
    group by 1
    ) 
select p1.id, p2.id, p1.colors && p2.colors like_same_colors
from the_data p1
join the_data p2 on p1.id < p2.id
order by 1, 2;

 id | id | like_same_colors 
----+----+------------------
 1  | 2  | t
 1  | 3  | f
 2  | 3  | f
(3 rows)