CODEWITHSUNDEEP
sqlpostgresqljsonb
Oliver Rice
Oliver Rice

Reputation: 893

JSONB Recursive Search and Replace by Key

Given an arbitrarily nested jsonb document, how can all instances of a specific key be replaced by another document.

For example:

select '{
    "good_key": {
        "critical": "info",
        "bad_key": {
            "worthless": "trash"
        }
    },
    "bad_key": {
        "more": "garbage"
    }
}'::jsonb

replacing all instances of "bad_key" and it's contents, with {"substitute_key": {"some": "info"}}

Resulting in

'{
    "good_key": {
        "critical": "info",
        "substitute_key": {
           "some": "info"
        }
    },
    "substitute_key": {
        "some": "info"
    }
}'

Upvotes: 0

Views: 223

Answers (1)

Bergi
Bergi

Reputation: 664589

You can do it with a combination of jsonb_each and jsonb_object_agg:

CREATE FUNCTION jsonb_replace_by_key(obj jsonb, search text, substitute jsonb) RETURNS jsonb
STRICT LANGUAGE SQL AS $$
  SELECT CASE jsonb_typeof(obj)
    WHEN 'object' THEN
      (SELECT jsonb_object_agg(key, CASE WHEN key = search
          THEN substitute
          ELSE jsonb_replace_by_key(value, search, substitute)
        END)
      FROM jsonb_each(obj))
    WHEN 'array' THEN
      (SELECT jsonb_agg(jsonb_replace_by_key(el, search, substitute))
      FROM jsonb_array_elements(obj) el)
    ELSE
      obj
 END;
$$;

Since you don't want to just replace the value of the property, but completely remove the property and add something else when it existed, use the following query in the object case instead:

SELECT jsonb_object_agg(key, jsonb_replace_by_key(value, search, substitute))
  || CASE WHEN obj ? search THEN substitute ELSE '{}' END
FROM jsonb_each(obj)
WHERE key <> search

(online demo)

Upvotes: 1

Related Questions