2

This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question.

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;

First question, why value = 'null' works, but value IS NULL is not?

Second question, is there any bad reason to use WHERE value <> 'null' instead of WHERE key NOT IN (SELECT key FROM ...) ?

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> 'null';
$$ LANGUAGE SQL;

Third question, why this function also incorrect (always returns NULL)? Is it because UNION ALL change all resulting record into TEXT?

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> (NULL::JSONB)
$$ LANGUAGE SQL;

-- check function:
SELECT COALESCE(jsonb_merge('{}','{"b":null}'),'{"x":"its empty"}'::JSONB) FROM xxx;
      coalesce      
--------------------
 {"x": "its empty"}
3

Question 1
From PostgreSQL Documentation Table 8.23 - JSON primitive types and corresponding PostgreSQL types:

JSON primitive type | PostgreSQL type | Notes
null                |(none)           | SQL NULL is a different concept

So you must not confuse SQL NULL value with with JSON null type.

Question 2
In your previous question you wanted the JSON value null to act as a kind of DELETE type. If you have both {"b": 3} and {"b": null} in the UNION and you do value <> 'null' the {"b": 3} will not be deleted. When you do NOT IN the pair b:3 will be left out of the new set too (the null type is found for the key "b" in the subquery).

Question 3
Related to question 1: NULL::JSONB means a SQL NULL value of the data type JSONB, not a JSON null type.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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