Postgresql – How to retrieve the value of deleted key in jsonb

jsonpostgresqlpostgresql-9.5

Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key?

Thus, if the key was not found, a null is returned or so. Otherwise the value is returned.

Is this possible?

Best Answer

The below function won't work on keys that are inside arrays. If you want something like that, you'll first have to define the behaviour for such JSON structures.

The main idea is to create a table returning function, which both returns the remaining jsonb and the popped key-value pair. The rest is finding and reconstructing the key-value pair in the source structure.

CREATE OR REPLACE FUNCTION jsonb_pop(source jsonb, key text)
    RETURNS TABLE (result jsonb, popped jsonb)
    LANGUAGE sql AS 
$$
WITH elem(popped) AS (
    SELECT jsonb_strip_nulls(jsonb_build_object(key, source -> key))
)
SELECT source - key, NULLIF(popped, '{}'::jsonb) FROM elem;
$$;

Related Solutions

PostgreSQL – Combining JSONB Query Statements

What I ended up doing using inspiration from MatheusOl answer.

INSERT INTO test (post_id, username, votes) 
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb) 
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE

  SET votes = test.votes || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb) 

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine

PostgreSQL 9.4 JSON – Deep Merge JSONB Values in PostgreSQL 9.4

I am with a_horse on this: Upgrade to Postgres 9.6 to have new options at your disposal (and for other reasons).

While stuck with 9.4, it might help to simplify like this:

CREATE OR REPLACE FUNCTION jsonb_merge2(jsonb1 JSONB, jsonb2 JSONB) 
  RETURNS JSONB LANGUAGE sql IMMUTABLE AS
$func$
SELECT
CASE    
   WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
     (
       SELECT jsonb_object_agg(merged.key, merged.value)
       FROM  (
         SELECT key
              , CASE WHEN p1.value <> p2.value          -- implies both are NOT NULL
                     THEN jsonb_merge2(p1.value, p2.value) 
                     ELSE COALESCE(p2.value, p1.value)  -- p2 trumps p1
                END AS value 
         FROM   jsonb_each($1) p1 
         FULL   JOIN jsonb_each($2) p2 USING (key)      -- USING helps to simplify
         ) AS merged
       WHERE  merged.value IS NOT NULL                  -- simpler, might help query planner
       AND    merged.value NOT IN ( '[]', 'null', '{}' )
     ) 
   WHEN $2 IN ( '[]', 'null', '{}' ) THEN               -- just as simple as above
     NULL
   ELSE    
     $2    
 END
$func$;

Best Answer

Related Solutions

PostgreSQL – Combining JSONB Query Statements

PostgreSQL 9.4 JSON – Deep Merge JSONB Values in PostgreSQL 9.4

Related Question