arrays - JSON 数组的 Postgres 查询

Question

我有一个名为 cust_data 的表，它存储 id 和 JSON 对象。我想写 postgres select 语句来获取:

1. 选择所有 id，其中“性别”:“女性”不存在于人员数组中 [这应该从下面的数据中返回 id#3]
2. 选择所有存在“性别”:“女性”且“状态”:“已婚”的 ID [这应该从下面的数据中返回 ID#2]

表:客户数据

id(numeric) | connections (jsonb)
------------------------------

1, {"Persons": [
            {
              "personName": "Tom",
              "gender": "Male",
              "country": "USA",
              "status":"single"

            },
            {
            "personName": "Harry",
            "gender": "Male",
            "country": "USA",
            "status":"single"
            },
            {
            "personName": "Lisa",
            "gender": "Female",
            "country": "Mexico",
            "status":"single"
              }    
          ]
        }

        2,{
          "Persons": [
            {
              "personName": "Lisa",
              "gender": "Male",
              "country": "UK",
              "status":"single"
            },
            {
            "personName": "Harry",
            "gender": "Male",
            "country": "USA",
            "status":"single"
            },
            {
            "personName": "Lisa",
            "gender": "Female",
            "country": "Mexico",
            "status":"married"
              }    
          ]
        }

        3,{
          "Persons": [
            {
              "personName": "Lisa",
              "gender": "Male",
              "country": "UK",
              "status":"single"
            },
            {
            "personName": "Harry",
            "gender": "Male",
            "country": "USA",
            "status":"single"
            }   
          ]
        }

Answer 1

您可以使用 boolean aggregate functions :

select id
from cust_data,
lateral jsonb_array_elements(connections->'Persons')
group by 1
having not bool_or(value->>'gender' = 'Female');

 id 
----
  3
(1 row)

select id
from cust_data,
lateral jsonb_array_elements(connections->'Persons')
group by 1
having bool_or(value->>'gender' = 'Female' and value->>'status' = 'married');

 id 
----
  2
(1 row)

Test it here.

---

如果数组可能为空，则应使用 left join ... on true 而不是 lateral。还为聚合添加具有适当默认值的 coalesce()，因为它们可以产生 null，例如:

select id
from cust_data
left join jsonb_array_elements(connections->'Persons') on true
group by 1
having not coalesce(bool_or(value->>'gender' = 'Female'), false);