将Go字符串转换为Postgres中jsonb_set的jsonb参数。

huangapple go评论86阅读模式
英文:

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

huangapple
  • 本文由 发表于 2022年5月17日 18:33:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/72272460.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定