如何通过PostgreSQL查询提取JSON数组中inputs->r1->work3的a、b属性值

需求：从给定的JSON数组数据中，提取层级inputs->r1->work3下的a、b属性值，输出包含a、b两列的结果集。

JSON数据示例 #

[
  {
    "calculation": "Not_Applicable",
    "inputs": {
      "r1": [
        {
          "work1": [
            {
              "oid": "WEEKLY_OT_1",
              "clientFactor": 1.5,
              "threshold": "PT40H0M"
            }
          ],
          "work2": "premiumWeeklyOT",
          "work3": [
            {
              "a": "0001",
              "b": "Bi-Weekly"
            },
            {
              "a": "0002",
              "b": "Monthly"
            }
          ]
        }
      ],
      "policy1": [
        "8639587_1"
      ],
      "policy2": [
        "8639587_1"
      ]
    }
  }
]

查询语句 #

由于JSON中存在多层数组（最外层数组、r1数组、work3数组），需要多次使用json_array_elements函数逐层展开数组，再提取目标属性：

SELECT
  work3_obj->>'a' AS a,
  work3_obj->>'b' AS b
FROM
  -- 展开最外层JSON数组
  json_array_elements('[
    {
      "calculation": "Not_Applicable",
      "inputs": {
        "r1": [
          {
            "work1": [
              {
                "oid": "WEEKLY_OT_1",
                "clientFactor": 1.5,
                "threshold": "PT40H0M"
              }
            ],
            "work2": "premiumWeeklyOT",
            "work3": [
              {
                "a": "0001",
                "b": "Bi-Weekly"
              },
              {
                "a": "0002",
                "b": "Monthly"
              }
            ]
          }
        ],
        "policy1": [
          "8639587_1"
        ],
        "policy2": [
          "8639587_1"
        ]
      }
    }
  ]'::json) AS root_obj,
  -- 展开r1数组
  json_array_elements(root_obj->'inputs'->'r1') AS r1_obj,
  -- 展开work3数组
  json_array_elements(r1_obj->'work3') AS work3_obj;

如果JSON数据存储在表中（假设表名为your_table，JSON字段名为json_data），可修改为：

SELECT
  work3_obj->>'a' AS a,
  work3_obj->>'b' AS b
FROM
  your_table,
  json_array_elements(json_data) AS root_obj,
  json_array_elements(root_obj->'inputs'->'r1') AS r1_obj,
  json_array_elements(r1_obj->'work3') AS work3_obj;

查询结果 #

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