如何编写PostgreSQL JSONB字段的IN查询及自定义查询函数？

你原有函数执行失败的核心问题是直接用IN操作符比较JSONB类型的node_ids字段和文本数组参数，IN操作符不支持JSONB数组与普通文本数组的交集判断，需要用PostgreSQL专门的JSONB操作符。

针对你的需求（匹配node_ids包含传入列表中任意元素的记录），直接使用JSONB的?|操作符即可，该操作符的作用就是判断JSONB数组是否包含给定文本数组中的任意一个元素：

SELECT location_id::text
FROM node_mapping
WHERE node_ids ?| ARRAY['uuid100', 'uuid200', 'uuid300'];

拿你给出的示例数据测试，上述语句会返回UUID1，符合预期。

CREATE OR REPLACE FUNCTION find_location_ids_for_seller(_seller_id text[])
   RETURNS TABLE(location_id text)
   LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT n.location_id::text
   FROM   node_mapping n
   WHERE  n.node_ids ?| _seller_id;
END
$func$;

-- 传入需要匹配的字符串列表参数
SELECT * FROM find_location_ids_for_seller(ARRAY['uuid100', 'uuid200', 'uuid300']);

如果表数据量较大，建议为node_ids字段创建GIN索引，可大幅提升该类查询的性能：

CREATE INDEX idx_node_mapping_node_ids ON node_mapping USING GIN(node_ids);

内容的提问来源于stack exchange，提问作者Akash Beura