Postgresql 9.4 - Invalid input syntax when converting to JSONB

I've made a simple function to update a jsonb with new values:

CREATE OR REPLACE FUNCTION jsonupdate(
    IN "pJson" jsonb, IN "pNewValues" jsonb)
  RETURNS jsonb AS
$BODY$

DECLARE 

    jsonreturn jsonb;

BEGIN

    jsonreturn := (SELECT json_object_agg(keyval.key, keyval.value::jsonb)
             FROM (SELECT key, 
                      CASE WHEN "pNewValues" ? key THEN
                       (SELECT "pNewValues" ->> key)
                      ELSE
                       value
                      END
                     FROM jsonb_each_text("pJson")) keyval);            

    RETURN jsonreturn;

END; $BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

Sample inputs and outputs:

IN: SELECT jsonupdate('{"a" : "1", "b" : "2"}', '{"a": "3"}');

OUT: {"a": 3, "b": 2}

IN: SELECT jsonupdate('{"a" : "3", "b" : { "c": "text", "d": 1 }}', '{"b": { "c": "another text" }}');

OUT: {"a": 3, "b": {"c": "another text"}}

IN: SELECT jsonupdate('{"a" : "1", "b" : "2", "c": 3, "d": 4}', '{"a": "5", "d": 6}');

OUT: {"a": 5, "b": 2, "c": 3, "d": 6}

The problem happens when using inputs like this one: SELECT jsonupdate('{"a" : "1", "b" : ""}', '{"a": "5"}') or this one: SELECT jsonupdate('{"a" : "1", "b" : "2"}', '{"a": "."}') or this one: SELECT jsonupdate('{"a" : "1", "b" : "2"}', '{"a": ""}') it gives me an error

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1:

What's wrong here?

Solution

You sould use the jsonb_each() function (instead of jsonb_each_text()). Also, the -> operator (instead of ->>):

CREATE OR REPLACE FUNCTION jsonupdate(IN "pJson" jsonb, IN "pNewValues" jsonb)
  RETURNS jsonb
  LANGUAGE sql
  IMMUTABLE AS
$BODY$
 SELECT json_object_agg(key, CASE
          WHEN "pNewValues" ? key THEN "pNewValues" -> key
          ELSE value
        END)
 FROM   jsonb_each("pJson")
$BODY$;

jsonb_each_text() and the ->> operator converts any non-string JSON value to their string representation. Converting those back to JSON will modify your data in a way you probably don't want to.

But I have to admit, what you are trying to achieve is almost the || (concatenation) operator. I.e.

SELECT jsonb '{"a" : "1", "b" : "2"}' || jsonb '{"a": "3"}'

will give you your desired output. The only difference between || and your function is when pNewValues contains key(s), which are not in pJson: || will append those too, while your function does not append them (it only modifies existing ones).

Update: for simulating the || operator on 9.4, you can use the following function:

CREATE OR REPLACE FUNCTION jsonb_merge_objects(jsonb, jsonb)
  RETURNS jsonb
  LANGUAGE sql
  IMMUTABLE AS
$func$
 SELECT    json_object_agg(key, COALESCE(b.value, a.value))
 FROM      jsonb_each($1) a
 LEFT JOIN jsonb_each($2) b USING (key)
$func$;