Postgres JSON equivalent to HSTORE subtract operator

Question

Postgres' hstore extension has a neat subtraction operator:

hstore - text[]

hstore - hstore

In the first case, it removes the key/value pairs where the keys are found in the array of strings: in the second case it removes all matching key/value pairs from the first hstore that appear in the second hstore.

It seems this operator does not exist for the new jsonb data type. Is there a simple way to perform these tasks?

Answer 1

The key is the json_each() function, and the ability in PostgreSQL to manually build up a json value.

Here is a function which can handle json - text[]:

CREATE OR REPLACE FUNCTION "json_object_delete_keys"(
  "json" json,
  VARIADIC "keys_to_delete" TEXT[]
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
     FROM json_each("json")
    WHERE "key" <> ALL ("keys_to_delete")),
  '{}'
)::json
$function$;

To handle the json - json case, you simple need to change the WHERE clause:

    WHERE "json"->>"key" <> ("remove"->>"key")),