Re: Delete values from JSON

På fredag 17. mars 2023 kl. 11:56:22, skrev Romain MAZIÈRE <
romain(dot)maziere(at)sigmaz-consilium(dot)fr <mailto:romain(dot)maziere(at)sigmaz-consilium(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>