Excellent! Thanks!
På lørdag 18. mars 2023 kl. 14:26:57, skrev Boris Zentner <b...@2bz.de <mailto:b...@2bz.de>>: Am 17.03.2023 um 08:56 schrieb Andreas Joseph Krogh <andr...@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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
