Search Postgresql Archives

Re: Delete values from JSON

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Excellent!
Thanks!
 
På lørdag 18. mars 2023 kl. 14:26:57, skrev Boris Zentner <bzm@xxxxxx>:


 


 

Am 17.03.2023 um 08:56 schrieb Andreas Joseph Krogh <andreas@xxxxxxxxxx>:


 

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
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux