单个 postgresql 查询中的文本和 jsonb 连接

如何在 postgresql 中连接 jsonb 对象内的字符串？换句话说，我在同一查询中使用 JSONb 连接运算符以及文本连接运算符，但遇到了麻烦。

或者...如果我应该执行一个完全不同的查询，我将不胜感激听到建议。目标是更新包含 jsonb 列的行。我们不想覆盖 jsonb 列中查询中未提供的现有键值对，并且我们还希望一次更新多行。

我的查询：

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

错误：

 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

您可能正在寻找

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

但这不是构建 JSON 对象的方式 - 那v.latitude可能不是有效的 JSON 文字，甚至包含一些注入，例如"", "otherKey": "oops"。 （诚​​然，在您的示例中，您控制了这些值，并且它们是数字，因此可能没问题，但这仍然是一个不好的做法）。相反，使用jsonb_build_object https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE:

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