????Fortunately, PostgreSQL is your friend and provides the jsonb_set function:jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])Given a jsonb column, you can set a new value on the specified path:Reference: PostgreSQL Json functionsThe 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.
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.
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?.????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.
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.
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.
Let me explain a bit about this part:(‘{‘||index-1||’,value}’)::text[] as pathIt 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.
Wrapping upJSONB 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.
Side note: that solution came out of a pair programming session with Lucas Cegatti.
Are you looking for a creative company to implement your next idea?.Check out LNA Systems and let’s talk.
.. More details