如何查询PostgreSQL中存储对象数组的jsonb列？

1. 检索包含实体John Smith的所有记录 #

假设tsent数组中的元素结构为{"entity": "实体名称", "class_label": "情感标签"}，可通过以下两种方式实现：

方法一：使用jsonb包含操作符@> #

SELECT * FROM tbl
WHERE tsent @> '[{"entity": "John Smith"}]'::jsonb;

@>是PostgreSQL jsonb类型的包含操作符，此语句会检查tsent数组中是否存在至少一个元素包含{"entity": "John Smith"}的键值对，满足条件的记录将被返回。

方法二：使用jsonb_array_elements拆分数组+EXISTS子查询 #

SELECT * FROM tbl
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(tbl.tsent) AS elem
    WHERE elem->>'entity' = 'John Smith'
);

jsonb_array_elements会将tsent的jsonb数组拆分为单行的元素记录，EXISTS子查询只要找到任意一个entity为John Smith的元素，就会返回原表对应的记录。

2. 检索同时包含实体John Smith且class_label为negative的记录 #

基于假设的元素结构，两种实现方式如下：

方法一：使用jsonb包含操作符@> #

SELECT * FROM tbl
WHERE tsent @> '[{"entity": "John Smith", "class_label": "negative"}]'::jsonb;

此语句检查tsent数组中是否存在同时包含entity: "John Smith"和class_label: "negative"的元素，符合条件的记录将被返回。

方法二：使用jsonb_array_elements拆分数组+EXISTS子查询 #

SELECT * FROM tbl
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(tbl.tsent) AS elem
    WHERE elem->>'entity' = 'John Smith'
      AND elem->>'class_label' = 'negative'
);

拆分数组后，同时过滤entity和class_label的匹配条件，只要有元素满足两个条件，就返回原表的对应记录。

注意事项 #

• 如果你的实体字段键名不是entity，或者情感标签字段不是class_label，请替换为实际的键名。
• PostgreSQL默认区分字符串大小写，如果需要不区分大小写的匹配，可使用LOWER()函数，例如：LOWER(elem->>'entity') = LOWER('John Smith')。

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