单个Postgresql查询中的文本和jsonb串联 [英] Text and jsonb concatenation in a single postgresql query

问题描述

如何在Postgresql中的串联jsonb对象内串联字符串？换句话说，我在同一查询中使用JSONb串联运算符和文本串联运算符，并遇到麻烦。

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.

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

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.

我的查询：

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 ：

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)

这篇关于单个Postgresql查询中的文本和jsonb串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！