postgres是否有jsonb数组重叠函数

Question

Am无法在postgres中从jsonb提取并比较两个数组以进行重叠检查。这有工作函数吗？

people_favorite_color表中的示例：

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

数组重叠后期测试：

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'

预期结果：

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

--编辑--尝试强制转换为文本[]导致错误：

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

使用array_agg()和jsonb_array_elements_text()将jsonb数组转换为文本数组：

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)