如何更新Postgresql的Jsonb数组

golang的大师之路
由浅入深,适合刚入门的你,在这里你会发现go的世界是多么美妙!
FlyWine

如何更新Postgresql的Jsonb数组

假设你决定将数据以json或者jsonb的形式存储在数据库中,然后发现你刚刚给自己制造了新的问题,而这些问题是以前没有的。

jsonb是一个强大的工具,但它也有一定的代价,因为你需要调整查询和处理数据的方式。

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

在这篇文章中,我们来看看如何通过一次查询来更新数组内对象的特定值。

假设你正在实现一个为每个客户存储动态联系人功能,那么你就会想到将联系人存储为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提供了json_set函数。函数原型:

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

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

select jsonb_set(
  '[{"type": "phone", "value": "+1-202-555-0105"},{"type": "email", "value": "jimi@gmail.com"}]',
  '{1,value}',
  '"jimi.hendrix@gmail.com"',
  false
);

select jsonb_set(
  '[{"type": "email", "value": "janis@gmail.com"}]',
  '{0,value}',
  '"janis.joplin@gmail.com"',
  false
);

jsonb_set函数的第一个参数为jsonb数据对象,第二个参数路径,在上面的例子中,{1,value}为数组中索引为1的元素,并且属性是value字段的,最后一个参数是对路径中选择的字段进行赋值。

根据这样的规则,那么第一个sql语句就是修改jimi的邮箱,而第二个sql语句就是修改janis的电子邮箱。

上面返回的结果是:

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

如果现在你认为这样就完事了,那就是你太Too young! Too simple!

非关系型数据库的问题在于它们是动态的,这也是使用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联系人的电子邮件对象索引。

现在万事俱备,只欠东风!我们把查询和更改步骤合并。

with contact_email as (
  select ('{'||index-1||',value}')::text[] as path
    from customers
        ,jsonb_array_elements(contacts) with ordinality arr(contact, index)
   where contact->>'type' = 'email'
     and name = 'Jimi'
)
update customers
   set contacts = jsonb_set(contacts, contact_email.path, '"jimi.hendrix@gmail.com"', false)
  from contact_email
 where name = 'Jimi';

这个sql中最重要的部分就是with,这是一个强大的命令,但对于这个例子来说,你可以把它看成是“储存变量”功能,with最后的结果都储存在contact_email变量内,其中就包含需要更新的路径,也就是jimi的邮箱路径。

下面再稍微详细介绍一下:

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

这一段是在建立路径{1, value},但是要转换成text[]类型,因为jsonb_set函数需要这个类型。

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值