postgresql - Postgres - jsonb：使用从另一个表中获取的值更新列中的键

Question

我正在使用 postgres 9.5。我有一个配置文件表，其中列出了名称：

public.profiles:
      id |   first_name      |        last_name
      --- ---------------  ---------------------
       1        Jason                   Bourne
       2       Jhonny                    Quest

我有一张发票表：

  public.invoices:
            invoice_id      |         billing_address       |    profile_id
         ------------------   -----------------------------  ---------------------
                   1          {                                        2
                               "address_line1": "445 Mount 
                                     Eden Road", 
                               "city":"Mount Eden", 
                               "country": "Auckland"
                              }

我想用 profile 表中的 first_name 和 last_name 更新 invoices 表的 billing_address 列，例如：

 public.invoices:
                invoice_id      |         billing_address       |    profile_id
             ------------------   -----------------------------  ---------------------
                       1          {
                                   "name" : "Jhonny Quest"                2
                                   "address_line1": "445 Mount 
                                         Eden Road", 
                                   "city":"Mount Eden", 
                                   "country": "Auckland"
                                  }

为此，我尝试使用 jsonb_set：

UPDATE invoices AS i SET billing_address = jsonb_set(billing_address,'{name}', SELECT t::jsonb FROM (SELECT CONCAT (p.first_name,p.middle_name, p.last_name) FROM profiles p WHERE p.id = i.profile_id)t )

它在 SELECT 处引发错误。TBH 我什至不确定该声明是否合法。寻找任何指导。

标签： postgresqljsonbpostgresql-9.5

Answer 1

点击：demo:db<>fiddle

UPDATE invoices i
SET billing_address = s.new_billing_address
FROM (
    SELECT
        i.invoice_id,
        jsonb_set(
            billing_address, 
            '{name}'::text[], 
            to_jsonb(concat_ws(' ', first_name, last_name))
        ) AS new_billing_address
    FROM 
        invoices i
    JOIN profiles p ON i.profile_id = p.id
) s
WHERE s.invoice_id = i.invoice_id;

创建SELECT并加入第二个表；之后，您可以使用to_jsonb()concat 运算符||（或 concat_ws()，当然，如评论中所述）从名称部分创建新的 JSON 对象。