如何使用PostgreSQL更新JSONB数组中的对象

by Leandro Cesquini Pereira

通过莱昂德罗·塞斯奎尼·佩雷拉

如何使用PostgreSQL更新JSONB数组中的对象 (How to update objects inside JSONB arrays with PostgreSQL)

如何更新JSONB数组上的特定值 (How to update a specific value on a JSONB array)

Let’s say you decided to store data in the database as json or jsonb and discovered that you just created new problems for yourself that you didn’t have before. You’re not alone.

假设您决定将数据以json或jsonb的形式存储在数据库中，并且发现自己只是为自己创建了以前从未遇到过的新问题。 你不是一个人。

JSONB is a powerful tool, but it comes at some cost because you need to adapt the way you query and handle the data.

JSONB是一个功能强大的工具，但要付出一定的代价，因为您需要调整查询和处理数据的方式。

And it’s not rare to load the entire jsonb object into memory, transform it using your preferred programming language, and then saving it back to the database. But, you just created another problem: performance bottlenecks and resource waste.

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

In this article let’s see how to update a specific value of an object inside an array with one query.

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

TL;DR: the final query is at the end of the article, and you can check out a live example at DB Fiddle to copy & paste and play with.

TL; DR ：最终查询在文章结尾，您可以在DB Fiddle中查看一个实时示例以进行复制，粘贴和使用。

Suppose you’re implementing a customer screen to store dynamic contacts for each customer. Then you come up with the idea of storing the contacts as a JSONB column because they’re dynamic, and thus using a not relational data structure makes sense.

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

Then you create a customers table with a JSONB contacts column and insert some data into it:

然后，您创建一个带有JSONB联系人列的客户表，并将一些数据插入其中：

Pretty easy right? But how can you update a specific contact for a specific customer? How to change Jimi's email or Janis’ phone? ?

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

Fortunately, PostgreSQL is your friend and provides the jsonb_set function:

幸运的是，PostgreSQL是您的朋友，并提供jsonb_set函数：

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

jsonb_set(目标jsonb，路径文本[]，new_value jsonb [，create_missing布尔值])

Given a jsonb column, you can set a new value on the specified path:

给定一个jsonb列，您可以在指定的路径上设置一个新值：

The above selects will return:

上面的选择将返回：

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

[{“type”: “email”, “value”: “janis.joplin@gmail.com”}]

To change Jimi's email on the contacts list, you inform the path "1, value" which means the second object on the array (starting at 0) and the key value. That's the path. The same applies to change Janis’ email, but its email object is at index 0.

若要更改联系人列表吉米的电子邮件，告知你的道路“1，价值”，这意味着数组(从0开始)和密钥值的第二个对象。 那就是路 。 这同样适用于更改Janis的电子邮件，但其电子邮件对象位于索引0。

You may be thinking: I just have to use jsonb_set on an update statement and it’s all done? That’s the idea, but that’s not enough yet.

您可能在想：我只需要在更新语句上使用jsonb_set，就完成了吗？ 那是主意，但这还不够。

The problem with non-relational data is that they’re dynamic. Well, that’s one of the reasons for using JSONB but that brings a problem: see that Jimi’s email object is at index 1 and Janis’ email object is at index 0 on the array, and another customer could have a very different array with different indexes. So, how can you discover the index of each contact type? ?

非关系数据的问题在于它们是动态的。 好吧，这是使用JSONB的原因之一，但是带来了一个问题：请参见Jimi的电子邮件对象位于索引1上，而Janis的电子邮件对象位于数组的索引0上，并且另一个客户可能拥有一个具有不同索引的非常不同的数组。 那么，如何发现每种联系人类型的索引？ ？

The answer is ordering the elements of the array and getting its index:

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

That query returns 1, which is the index of the email object (type email) inside the contacts array of the customer Jimi.

该查询返回1 ，它是客户Jimi的联系人数组内的电子邮件对象(电子邮件类型)的索引 。

Now we have all the pieces of the puzzle: we know how to update a jsonb value and how to discover the index of the object to be updated.

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

The only step left is the update itself. Putting it all together we have:

剩下的唯一步骤就是更新本身。 放在一起，我们有：

The most important part of this query is the with block. It's a powerful resource, but for this example, you can think of it as a "way to store a variable" that is the path of the contact you need to update, which will be dynamic depending on the record.

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

Let me explain a bit about this part:

让我解释一下这部分：

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

It just builds the path as '{1, value}', but we need to convert to text[] because that’s the type expected on the jsonb_path function.

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

结语 (Wrapping up)

JSONB is a great and valuable tool to resolve a lot of problems. But keep in mind that you also need to query and update this kind of data. That brings a cost that you have to consider when deciding which tools you pick to use.

JSONB是解决许多问题的优秀工具。 但是请记住，您还需要查询和更新此类数据。 这带来了决定选择使用哪种工具时必须考虑的成本。

Side note: that solution came out of a pair programming session with Lucas Cegatti.

旁注：该解决方案来自与Lucas Cegatti进行的一对编程会议。

Are you looking for a creative company to implement your next idea? Check out LNA Systems and let’s talk.

您是否正在寻找一家创意公司来实施下一个想法？ 查看LNA Systems ，让我们谈谈。

翻译自: https://www.freecodecamp.org/news/how-to-update-objects-inside-jsonb-arrays-with-postgresql-5c4e03be256a/