3

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?

0

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.