The `jsonb_insert` function brought in with PostgreSQL 9.6 gives more control when inserting new values into a jsonb array and/or new keys into a jsonb object. This article introduces this new function together with some examples.

It seems that in every recent PostgreSQL release, another improvement or new feature regarding JSON data comes to light. This has been happening since PostgreSQL 9.2, where the JSON data type was first introduced.

Just a reminder, we have already talked about the many improvements to JSON support included on the previous PostgresSQL release (9.5).

On the current stable release (9.6), the new feature included was the jsonb_insert function. This new function gives more control when inserting new data into an existing jsonb array as well as a new key into an existing jsonb object.

The jsonb_insert function

The structure of the jsonb_insert function presents the 4 arguments:

jsonb_insert(
    target jsonb,          -> The JSONB data target.
    path text[],           -> The path on the target where the new_value will be inserted.
    new_value jsonb,       -> The new value/key to be inserted.
    [insert_after boolean] -> If true, new_value will be inserted after the target path.
                              And before, if it´s set to false. Default is false.
    )

A simple example of this function being used looks like this:

SELECT
    jsonb_insert('{
        "name": "John",
        "phone": ["333-3333", "555-5555"]}'::jsonb,
        '{phone, 0}',
        '"777-7777"'::jsonb,
        true);
        
                          jsonb_insert
-----------------------------------------------------------------
 {"name": "John", "phone": ["333-3333", "777-7777", "555-5555"]}
(1 row)
   

In the example above, the new phone number was inserted right after the index 0 of the JSON array at the phone key. It is worth noting that if the second element on the path argument (the number 0 on the example) is a negative number, the counting of the index array will start in the other direction (from the right to the left).

What happens if I want to insert a new key object?

As you can see, in the previous example, the last item on the path argument was an array index. If, instead, it was an object key, this key would be created if non-existent, and the new value would be assigned to it. Let’s see an example:

SELECT
    jsonb_insert('{
        "name": "John",
        "contact": {"phone": "555-5555"}}'::jsonb,
        '{contact, email}',
        '"[email protected]"'::jsonb);
        
                                 jsonb_insert
------------------------------------------------------------------------------
 {"name": "John", "contact": {"email": "[email protected]", "phone": "555-5555"}}
(1 row)

In this case, as the last item in the path argument was the non-existent object key email, it was created within contact and the new value given to it.

A very important point to remember is that the jsonb_insert function won’t overwrite any existing value or key. If the key in the previous example already existed, nothing would have been modified.

Conclusion

Although it was already possible to insert a new key/value through the jsonb_set function which was included in PostgreSQL 9.5, its focus was more on performing data modification than data insertion.

The new jsonb_insert function specifically helps in that scenario, giving more control to where the data will be inserted, specifically when dealing with JSON arrays.

That’s it for today, folks. See you next time!