英文:
Query Go string into a jsonb argument for jsonb_set in Postgres
问题
我正在使用Go和"github.com/jackc/pgx/v4/pgxpool"
。
我的表中有一个字段是json
类型,我需要在一个查询中更新两个键。
第一个问题是同时更新多个键,但我认为使用嵌套的jsonb_set
函数可以正常工作。
更大的问题是在这种情况下参数替换的工作原理。
UPDATE my_table
SET
first_column=$2,
json_column = JSONB_SET(JSONB_SET(
json_column::jsonb,
'{first_key_name}',
$3,
true),
'{second_key_name}',
$4,
true),
updated_at = now() at time zone 'utc'
WHERE id=$1
然而,这会导致invalid input syntax for type json
错误。在参数($3,$4)周围/之后添加to_jsonb()
或::jsonb
也没有帮助。
下面是如何使用pgx
库将参数传递给查询的示例。两个键值都是string
类型。
err := pool.QueryRow(ctx, myQuery,
id,
first_column,
first_key_value,
second_key_value
).
Scan(...)
经过一些尝试,似乎使其工作的一种方法是使用jsonb_build_array
然后获取第0个元素。这看起来非常难以阅读,我几乎确定一定有更简单的方法来实现这个目标。
UPDATE my_table
SET
first_column=$2,
json_column = JSONB_SET(JSONB_SET(
json_column::jsonb,
'{first_key_name}',
jsonb_array_element(jsonb_build_array($3::text), 0),
true),
'{second_key_name}',
jsonb_array_element(jsonb_build_array($4::text), 0),
true),
updated_at = now() at time zone 'utc'
WHERE id=$1
英文:
I am using Go and "github.com/jackc/pgx/v4/pgxpool"
.
One of the fields in my table has json
and I need to update two keys in one query.
The first problem was to update several keys at once but I think it works fine now with a nested jsonb_set
.
A bigger problem is how parameter substitution works in this case.
UPDATE my_table
SET
first_column=$2,
json_column = JSONB_SET(JSONB_SET(
json_column::jsonb,
'{first_key_name}',
$3,
true),
'{second_key_name}',
$4,
true),
updated_at = now() at time zone 'utc'
WHERE id=$1
This yields, however, the invalid input syntax for type json
error. Adding either to_jsonb()
or ::jsonb
around/after the parameters ($3, $4) didn't help.
Below is how the pgx
library is used to pass the arguments to the query. Both key value are of string
type.
err := pool.QueryRow(ctx, myQuery,
id,
first_column,
first_key_value,
second_key_value
).
Scan(...)
After some experimenting, it seems that one way to make it work is to use jsonb_build_array
and then get the 0-th item. That looks totally unreadable and I am almost sure there must be a simpler way to achieve this.
UPDATE my_table
SET
first_column=$2,
json_column = JSONB_SET(JSONB_SET(
json_column::jsonb,
'{first_key_name}',
jsonb_array_element(jsonb_build_array($3::text), 0),
true),
'{second_key_name}',
jsonb_array_element(jsonb_build_array($4::text), 0),
true),
updated_at = now() at time zone 'utc'
WHERE id=$1
答案1
得分: 1
你可以在这里使用||
运算符。
最初,假设记录如下所示:
{"a":"b","key2":"value2"}
更新后:
update dummy set j=j::jsonb||'{"a": "new_value"}'
{"a":"new_value","key2":"value2"}
请记住,如果找到顶层键,则替换它,否则创建一个新键。在||
运算符的右侧使用jsonb_build_object
来创建JSON对象。
英文:
You could use ||
operator here.
Initially lets say the record look like this:
{"a":"b","key2":"value2"}
After update
update dummy set j=j::jsonb||'{"a": "new_value"}
{"a":"new_value","key2":"value2"}
Remember it replaces top level keys if found otherwise create a new key. use jsonb_build_object to create json object on right side of || operator
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论