Re: Delete values from JSON

Excellent!
Thanks!

På lørdag 18. mars 2023 kl. 14:26:57, skrev Boris Zentner <bzm@2bz.de 
<mailto:bzm@2bz.de>>:







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 <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>