Horton

Thread

  1. Delete values from JSON

    Andreas Joseph Krogh <andreas@visena.com> — 2023-03-17T07:56:51Z

    
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.






--
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>

  2. Re: Delete values from JSON

    Romain MAZIÈRE <romain.maziere@sigmaz-consilium.fr> — 2023-03-17T10:56:22Z

    Hi,

If it is jsonb type, you can have a look at the documentation : 
https://www.postgresql.org/docs/14/functions-json.html

There are some examples :

|jsonb| |-| |text| → |jsonb|

Deletes a key (and its value) from a JSON object, or matching string 
value(s) from a JSON array.

|'{"a": "b", "c": "d"}'::jsonb - 'a'| → |{"c": "d"}|

|'["a", "b", "c", "b"]'::jsonb - 'b'| → |["a", "c"]|

|jsonb| |-| |text[]| → |jsonb|

Deletes all matching keys or array elements from the left operand.

|'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]| → |{}|

|jsonb| |-| |integer| → |jsonb|

Deletes the array element with specified index (negative integers count 
from the end). Throws an error if JSON value is not an array.

|'["a", "b"]'::jsonb - 1| → |["a"]|

|jsonb| |#-| |text[]| → |jsonb|

Deletes the field or array element at the specified path, where path 
elements can be either field keys or array indexes.

|'["a", {"b":1}]'::jsonb #- '{1,b}'| → |["a", {}]|

Regards

Romain MAZIÈRE
romain.maziere@sigmaz-consilium.fr
+33.535.545.085
+33.781.46.36.96
https://sigmaz-consilium.fr

Le 17/03/2023 à 08:56, Andreas Joseph Krogh a écrit :
>
> 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.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>

  3. Re: Delete values from JSON

    Andreas Joseph Krogh <andreas@visena.com> — 2023-03-17T11:06:05Z

    
På fredag 17. mars 2023 kl. 11:56:22, skrev Romain MAZIÈRE <
romain.maziere@sigmaz-consilium.fr <mailto:romain.maziere@sigmaz-consilium.fr>>:
Hi,

If it is jsonb type, you can have a look at the documentation : 
https://www.postgresql.org/docs/14/functions-json.html 
<https://www.postgresql.org/docs/14/functions-json.html>

There are some examples :


jsonb - text → jsonb

Deletes a key (and its value) from a JSON object, or matching string value(s) 
from a JSON array.

'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}

'["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]


jsonb - text[] → jsonb

Deletes all matching keys or array elements from the left operand.

'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}


jsonb - integer → jsonb

Deletes the array element with specified index (negative integers count from 
the end). Throws an error if JSON value is not an array.

'["a", "b"]'::jsonb - 1 → ["a"]


jsonb #- text[] → jsonb

Deletes the field or array element at the specified path, where path elements 
can be either field keys or array indexes.

'["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]

Regards

I have looked at the docs, but it doesn't, AFAIU, show how to conditionally 
delete a key based on its value, and leave other keys in the JSONB not matching 
the value alone.

I want to delete all keys in the (pseudo) path 
details.keyInformation[*].dunsNumber if the value is "NaN".






--
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>

  4. Re: Delete values from JSON

    Boris Zentner <bzm@2bz.de> — 2023-03-18T13:26:57Z

    
> 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

  5. Re: Delete values from JSON

    Andreas Joseph Krogh <andreas@visena.com> — 2023-03-18T17:07:40Z

    
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>