From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | Boris Zentner <bzm(at)2bz(dot)de> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Delete values from JSON |
Date: | 2023-03-18 17:07:40 |
Message-ID: | VisenaEmail.275.ed2a12df869653c3.186f5b0cdf9@origo02.app.internal.visena.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Excellent!
Thanks!
På lørdag 18. mars 2023 kl. 14:26:57, skrev Boris Zentner <bzm(at)2bz(dot)de
<mailto:bzm(at)2bz(dot)de>>:
Am 17.03.2023 um 08:56 schrieb Andreas Joseph Krogh <andreas(at)visena(dot)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(at)[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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | shashidhar Reddy | 2023-03-18 20:39:06 | Getting error while upgrading postgres from version 12 to 13 |
Previous Message | Adrian Klaver | 2023-03-18 14:25:02 | Re: Can't connect to server |