Postgresql 9.4 - Invalid input syntax when converting to JSONB

Question

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?

you'll need to call `jsonb_each()`, not `jsonb_each_text()` – pozs Jun 14 '17 at 12:15 — pozs, Jun 14 '17 at 12:15

pozs · Accepted Answer · 2017-06-14T13:36:13.317

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

I've tried the `||` operator before, but it didn't work. Then I discovered that PostgreSQL 9.4 doesn't support it. So I created this function. Thanks for the help! — Pedro Corso, Jun 14 '17 at 13:13
@PedroCorso Yes, it is introduced in 9.5. I've a [detailed answer here](https://stackoverflow.com/a/23500670/1499698) for what you want to achieve for each Postgres versions back to 9.3. — pozs, Jun 14 '17 at 13:31

Postgresql 9.4 - Invalid input syntax when converting to JSONB

1 Answers1