postgresql - Postgres jsonb，嵌套数组查询以隐藏特定字段

Question

我有以下格式的 jsonb 数据，带有嵌套数组

{
  "outerArray": [
    {
      "price": {
        "amount": 108.95,
        "currencyCode": "GBP"
      },
      "innerArray": [
        {
          "details": {
            "field1": "val1",
            "field2": "val2",
            "field3": "val3"
          },
          "otherDetail": {
            "date": "2016-07-23",
            "time": "19:43:00"
          },
          "innerMostArray": [
            {
              "A1": "A1"
            },
            {
              "B1": "B1"
            }
          ]
        }
      ],
      "someField": "values"
    },
    {
      "price": {
        "amount": 108.95,
        "currencyCode": "GBP"
      },
      "innerArray": [
        {
          "details": {
            "field1": "val1",
            "field2": "val2",
            "field3": "val3"
          },
          "otherDetail": {
            "date": "2016-07-23",
            "time": "19:43:00"
          },
          "innerMostArray": [
            {
              "A1": "A1"
            },
            {
              "B1": "B1"
            }
          ]
        }
      ],
      "someField": "values"
    }
  ]
}

我想对此编写一个检索查询，以保持相同的 json 结构但隐藏字段“price”、“details”、“otherDetail”和“someField”

检索到的结果应该是这样的

{
  "outerArray": [
    {
      "innerArray": [
        {
          "innerMostArray": [
            {
              "A1": "A1"
            },
            {
              "B1": "B1"
            }
          ]
        }
      ]
    },
    {
      "innerArray": [
        {
          "innerMostArray": [
            {
              "A1": "A1"
            },
            {
              "B1": "B1"
            }
          ]
        }
      ]
    }
  ]
}

这可以做到吗？

Answer 1

请始终指定您正在使用的 PostgreSQL 版本。下面的示例应该适用于 v9.5+ 版本。

我会通过使用 jsonb_build_object() 构建您需要的 JSONB 对象来解决这个问题和 jsonb_build_array()功能:

示例查询:

WITH test(data) AS ( VALUES
  ('{
      "outerArray": [
        {
          "price": {
            "amount": 108.95,
            "currencyCode": "GBP"
          },
          "innerArray": [
            {
              "details": {
                "field1": "val1",
                "field2": "val2",
                "field3": "val3"
              },
              "otherDetail": {
                "date": "2016-07-23",
                "time": "19:43:00"
              },
              "innerMostArray": [
                {
                  "A1": "A1"
                },
                {
                  "B1": "B1"
                }
              ]
            }
        ],
        "someField": "values"
      },
    {
      "price": {
        "amount": 108.95,
        "currencyCode": "GBP"
      },
      "innerArray": [
        {
          "details": {
            "field1": "val1",
            "field2": "val2",
            "field3": "val3"
          },
          "otherDetail": {
            "date": "2016-07-23",
            "time": "19:43:00"
          },
          "innerMostArray": [
            {
              "A1": "A1"
            },
            {
              "B1": "B1"
            }
          ]
        }
      ],
      "someField": "values"
    }
  ]}'::JSONB)
)
SELECT
  jsonb_build_object(
    'outerArray',
    array_agg(
        jsonb_build_object(
            'innerArray',
            json_build_array(
                json_build_object(
                    'innerMostArray',
                    innerArray->'innerMostArray')
            )
        )
    )
  ) as result
FROM test t,
    jsonb_array_elements(t.data->'outerArray') as outerElement,
    jsonb_array_elements(outerElement->'innerArray') as innerArray;

结果:

    result                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------
 {"outerArray": [{"innerArray": [{"innerMostArray": [{"A1": "A1"}, {"B1": "B1"}]}]}, {"innerArray": [{"innerMostArray": [{"A1": "A1"}, {"B1": "B1"}]}]}]}
(1 row)