Re: Delete values from JSON - Mailing list pgsql-general

 

 

Am 17.03.2023 um 08:56 schrieb Andreas Joseph Krogh <andreas@visena.com>:

 

Hi, in PG-14, how do I delete the keys "dunsNumber": "NaN":

 

{  "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6",  "details": [    {      "keyInformation": {        "dunsNumber": "NaN",        "organizationType": "LIMITED_COMPANY"      }    },    {      "keyInformation": {        "dunsNumber": "123",        "organizationType": "LIMITED_COMPANY"      }    }  ],  "nisse": 123
}

 

So that the result becomes:

 

{  "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6",  "details": [    {      "keyInformation": {        "organizationType": "LIMITED_COMPANY"      }    },    {      "keyInformation": {        "dunsNumber": "123",        "organizationType": "LIMITED_COMPANY"      }    }  ],  "nisse": 123
}

 

Thanks.

 

Hi Andreas, this works for me.

 

➤ 2023-03-18 14:23:51 CET bz@[local]:5432/test

=# WITH data(j)

  AS (VALUES (CAST('{ "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6", "details": [ { "keyInformation": { "dunsNumber": "NaN", "organizationType": "LIMITED_COMPANY" } }, { "keyInformation": { "dunsNumber": "123", "organizationType": "LIMITED_COMPANY" } } ], "nisse": 123 }' AS jsonb)))

 

  SELECT jsonb_pretty(jsonb_set(j

                              , '{details}'

                              , (SELECT jsonb_agg(CASE

                                                    WHEN ((elem -> 'keyInformation') ->> 'dunsNumber') = 'NaN'

                                                      THEN jsonb_set(elem

                                                                   , '{keyInformation}'

                                                                   , (elem -> 'keyInformation') - 'dunsNumber')

                                                    ELSE elem

                                                  END)

                                 FROM jsonb_array_elements(data.j -> 'details') AS elem))) AS nice_output

  FROM data

;

nice_output

{

    "nisse": 123,

    "details": [

        {

            "keyInformation": {

                "organizationType": "LIMITED_COMPANY"

            }

        },

        {

            "keyInformation": {

                "dunsNumber": "123",

                "organizationType": "LIMITED_COMPANY"

            }

        }

    ],

    "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6"

}

(1 row)

Time: 0,731 ms

 

--

Boris

 

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com