The article provides the method to update the JSONB array using Postgres. Bobcares, as a part of our Server Management Services, offers solutions to every query that comes our way.

Updating JSONB array using Postgres

Although JSONB is a great tool, we must know how to query and manage the data. We usually use JSONB arrays to store data that is dynamic in nature. In this article, we will show the steps to update a JSONB array using PostgreSQL with the help of an example.

Updating JSONB array using Postgres – Example

Consider developing a customer screen to keep dynamic contacts for every customer. Then we decide that it makes sense to store the contacts as a JSONB column since they are dynamic and can thus benefit from a non-relational data structure.

So here we create a DB table, named customers with a JSONB contacts column. Then insert some data into it.

The above code is simple and easy. However, updating a specific contact for a specific customer may be difficult. We can use Postgres in these scenarios. PostgreSQL has a jsonb_set function that makes it possible. The syntax is as follows:

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

We can change a value on the specified path for a certain jsonb column:

It will return:

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

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

We must specify the path “1, value,” which refers to the second object in the array (which starts at 0), in order to modify Jimi’s email address in the contacts list. Changing Janis’ email is also similar, but its email object is at index 0.

Non-relational data have the drawback of being dynamic. That’s certainly one of the benefits of using JSONB, but it also presents a challenge: as we can see, Jimi’s email object is at index 1 on the array, whereas Janis’ email object is at position 0. Another client may have a radically different array with various indexes. So to find the index for each sort of contact, the solution is to order the elements of the array and get its index.

The output from the query is 1, which is the index of the email object (type email) in the customer Jimi’s contacts array. Now that we know how to update a jsonb value and get the object’s index that needs to be changed, the only thing left is the update part.

The “with block” is the most significant component of this query. Despite the fact that it is a vital tool, in this case, we may think of it as a “means to store a variable” that is the path of the contact we need to update and which will change based on the record.

The above code builds the path as ‘{1, value}’. We need to convert to text[] because that’s the type expected on the jsonb_path function.

[Need help with another issue? We’re here to help.]

Conclusion

JSONB is a fantastic and useful technology that may address many issues. But bear in mind that this type of data also requires querying and updating. When choosing the tools to use, we must take into account the expense that results from this.