如何更新 JSONB 数组上的特定值

假设您决定将数据以 json 或 jsonb 存储在数据库中,并发现您刚刚为自己创造了以前没有的新问题。你不是一个人。

JSONB 是一个强大的工具,但它需要一些成本,因为您需要调整查询和处理数据的方式。

将整个 jsonb 对象加载到内存中,使用您喜欢的编程语言对其进行转换,然后将其保存回数据库的情况并不少见。但是,您刚刚制造了另一个问题:性能瓶颈和资源浪费。

在本文中,让我们看看如何使用一个查询来更新数组中对象的特定值。

TL;DR :最终查询在文章末尾,您可以在DB Fiddle上查看一个实时示例,以便复制和粘贴和玩。

假设您正在实施一个客户屏幕来存储每个客户的动态联系人。然后你想出了将联系人存储为 JSONB 列的想法,因为它们是动态的,因此使用非关系数据结构是有意义的。

然后创建一个带有 JSONB 联系人列的客户表,并在其中插入一些数据:

create table customers (name varchar(256), contacts jsonb);

insert into customers (name, contacts) values (
  'Jimi',
  '[
    {"type": "phone", "value": "+1-202-555-0105"},
    {"type": "email", "value": "jimi@gmail.com"}
  ]'
);

insert into customers (name, contacts) values (
  'Janis',
  '[
    {"type": "email", "value": "janis@gmail.com"}
   ]'
);

很容易吧?但是如何更新特定客户的特定联系人?如何更改 Jimi 的电子邮件或 Janis 的电话? 🤔

幸运的是,PostgreSQL 是你的朋友,它提供了 jsonb_set 函数:

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

给定一个 jsonb 列,您可以在指定路径上设置一个新值:

[](https://res.cloudinary.com/practicaldev/image/fetch/s--uZcjNtd4--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://leandrocp.com.br/ img/posts/sql-jsonb-1.png)

参考:PostgreSQL Json 函数

以上选择将返回:

[{"type": "phone", "value": "+1–202–555–0105"}, {"type": "email", "value": "jimi.hendrix@gmail.com"}]
  [{"type": "email", "value": "janis.joplin@gmail.com"}]

要更改联系人列表中 Jimi 的电子邮件,您需要通知路径1, value,这意味着数组中的第二个对象(从 0 开始)和键 value。那是_path_。这同样适用于更改 Janis 的电子邮件,但其电子邮件对象位于索引 0。

你可能会想:我只需要在更新语句上使用 jsonb_set 就可以了?这就是想法,但这还不够。

非关系数据的问题在于它们是动态的。嗯,这就是使用 JSONB 的原因之一,但这带来了一个问题:看到 Jimi 的电子邮件对象在索引 1 处,而 Janis 的电子邮件对象在数组中的索引 0 处,另一个客户可能有一个非常不同的数组,具有不同的索引.那么,如何发现每个联系人类型的索引呢? 🤔

答案是对数组的元素进行排序并获取其索引:

select index-1 as index
  from customers
      ,jsonb_array_elements(contacts) with ordinality arr(contact, index)
 where contact->>'type' = 'email'
   and name = 'Jimi';

该查询返回 1 ,这是客户 Jimi 的联系人数组中的电子邮件对象(类型电子邮件)的 index。

现在我们已经解决了所有难题:我们知道如何更新 jsonb 值以及如何发现要更新的对象的索引。

剩下的唯一步骤是更新本身。综上所述,我们有:

[](https://res.cloudinary.com/practicaldev/image/fetch/s--XRaeQWF_--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://leandrocp.com.br/ img/posts/sql-jsonb-2.png)

这个查询最重要的部分是with块。它是一个强大的资源,但对于本示例,您可以将其视为一种“存储变量的方式”,即您需要更新的联系人的_path_,它将根据记录而动态变化。

让我解释一下这部分:

('{'||index-1||',value}')::text[] as path

它只是将路径构建为'{1, value}',但我们需要转换为text[],因为这是jsonb_path函数所期望的类型。

结束

JSONB 是一个伟大而有价值的工具,可以解决很多问题。但请记住,您还需要查询和更新此类数据。这带来了在决定选择使用哪些工具时必须考虑的成本。

旁注:该解决方案来自与Lucas Cegatti的配对编程会话。