Postgres jsonb, nested array querying to hide specific fields

Question

I have a jsonb data of following format, with nested arrays

{
  "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"
    }
  ]
}

I want to write a retrieve query on this, to maintain same json structure but hide fields "price", "details" , "otherDetail"and "someField"

The retrieved result should look like this

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

Can this be done?

I am new to jsonb and postgres. In my actual scenario there were several other nesting and to reach this current json i have used the jsonb_extract_path_text. And currently i am retriving the entire json data from postgress and doing the hiding/mapping part from my side using jackson json. — phx
– phx, Commented Mar 15, 2017 at 8:44
depending on version of Postres you use, it is more or less comfortable to do, but this is just my opinion manipulating json with js is alwas more comfortable — Vao Tsun
– Vao Tsun, Commented Mar 15, 2017 at 8:56

Dzmitry Savinkou · Accepted Answer · 2017-03-15 18:28:52Z

Please always specify a version of PostgreSQL you are using. An example below should work fine for versions v9.5+.

I would approach this by building a JSONB object you need with jsonb_build_object() and jsonb_build_array() functions:

Sample query:

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:

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

Collectives™ on Stack Overflow

Postgres jsonb, nested array querying to hide specific fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related