Search code examples
postgresqlconcatenationjsonb

Text and jsonb concatenation in a single postgresql query

How can I concatenate a string inside of a concatenated jsonb object in postgresql? In other words, I am using the JSONb concatenate operator as well as the text concatenate operator in the same query and running into trouble.

Or... if there is a totally different query I should be executing, I'd appreciate hearing suggestions. The goal is to update a row containing a jsonb column. We don't want to overwrite existing key value pairs in the jsonb column that are not provided in the query and we also want to update multiple rows at once.

My query:

update contacts as c set data = data || '{"geomatch": "MATCH","latitude":'||v.latitude||'}'
from (values (16247746,40.814140),
      (16247747,20.900840),
      (16247748,20.890570)) as v(contact_id,latitude) where c.contact_id = v.contact_id

The Error:

 ERROR:  invalid input syntax for type json
    LINE 85: update contacts as c set data = data || '{"geomatch": "MATCH...
                                                     ^
    DETAIL:  The input string ended unexpectedly.
    CONTEXT:  JSON data, line 1: {"geomatch": "MATCH","latitude":
    SQL state: 22P02
    Character: 4573
Solution

  • You might be looking for

    SET data = data || ('{"geomatch": "MATCH","latitude":'||v.latitude||'}')::jsonb
--              ^^ jsonb                              ^^ text     ^^ text

    but that's not how one should build JSON objects - that v.latitude might not be a valid JSON literal, or even contain some injection like "", "otherKey": "oops". (Admittedly, in your example you control the values, and they're numbers so it might be fine, but it's still a bad practice). Instead, use jsonb_build_object:

    SET data = data || jsonb_build_object('geomatch', 'MATCH', 'latitude', v.latitude)