PostgreSQL 11.13中如何从JSON数组选取元素并关联字段查询

问题背景 #

现有如下JSON格式的数据：

{
  "processedResult": {
    "orderPayment": {
      "paymentType": "VISA"
    },
    "store": "US",
    "orderDeliveryGroups": [
      {
        "address": {
          "city": "New York",
          "state": "NY"
        },
        "deliveryType": "STH"
      }
    ]
  }
}

当前使用的查询语句为：

SELECT DISTINCT myDataJsonb -> 'processedResult' -> 'orderPayment' -> 'paymentType'
FROM myData
WHERE myDataJsonb -> 'processedResult' ->> 'store'  = 'US'

期望得到包含PaymentType和DeliveryType的查询结果：

解决方案 #

要从JSON数组中提取deliveryType并与paymentType关联，需要使用jsonb_array_elements函数将数组展开为行记录，再提取对应字段。正确的查询语句如下：

SELECT 
  myDataJsonb -> 'processedResult' -> 'orderPayment' ->> 'paymentType' AS "PaymentType",
  dg ->> 'deliveryType' AS "DeliveryType"
FROM myData,
     jsonb_array_elements(myDataJsonb -> 'processedResult' -> 'orderDeliveryGroups') AS dg
WHERE myDataJsonb -> 'processedResult' ->> 'store' = 'US'

说明 #

• jsonb_array_elements函数将orderDeliveryGroups数组拆分为多行，每个数组元素对应一行数据；
• 使用->>操作符获取字段的文本值，避免返回JSON格式的结果；
• 如果需要去重，可在SELECT后添加DISTINCT关键字。

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