PostgreSQL 9.5 has introduced new JSONB functionalities, greatly improving its already present NoSQL characteristics. With the inclusion of new operators and functions, now it is possible to easily modify the JSONB data. In this article these new modifiers will be presented together with some examples of how to use them.

With the inclusion of the JSON data type in its 9.2 release, PostgreSQL finally started supporting JSON natively. Although with this release it was possible to use Postgres as a “NoSQL” database, not much could actually be done at the time due to the lack of operators and interesting functions. Since 9.2, JSON support has been improving significantly in each new version of PostgreSQL, resulting today in the complete overcome of the initial limitations.

Probably, the most remarkable improvements were the addition of the JSONB data type in Postgres 9.4 and, in the current Postgres 9.5 release, the introduction of new operators and functions that permit you to modify and manipulate JSONB data.

In this article we will focus on the new capabilities brought by Postgres 9.5. However, before diving into that, if you want to know more about the differences between the JSON and JSONB data types, or if you have doubts with respect to a “NoSQL” database being a good solution in your use case (which, you should ;)), I suggest you read the previous articles we have written regarding these topics:

• NoSQL with PostgreSQL 9.4 and JSONB by Giuseppe Broccolo
• JSONB type performance in PostgreSQL 9.4 by Marco Nenciarini
• PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns by Craig Ringer

The new JSONB Operators

The operators and functions present in PostgreSQL until 9.4 only made it possible to extract JSONB data. Therefore, to actually modify this data, one would extract it, modify it, and then reinsert the data. Not too practical, some would say.

The new operators included in PostgreSQL 9.5, which were based on the jsonbx extension for PostgreSQL 9.4 have changed this, greatly improving how to handle the JSONB data.

Concatenate with ||

You can now concatenate two JSONB objects using the || operator:

SELECT
    '{"name": "Marie",
      "age": 45}'::jsonb || '{"city": "Paris"}'::jsonb;

                      ?column?
   ----------------------------------------------
    {"age": 45, "name": "Marie", "city": "Paris"}
   (1 row)

In the example above, the key town is appended to the first JSONB object.

It can also be used to overwrite already existing values:

SELECT
    '{"city": "Niceland",
      "population": 1000}'::jsonb || '{"population": 9999}'::jsonb;

                    ?column?
   -------------------------------------------
    {"city": "Niceland", "population": 9999}
   (1 row)

In this case, the value of the key population was overwritten by the value of the second object.

Delete with –

The – operator can remove a key/value pair from a JSONB object:

SELECT
    '{"name": "Karina",
      "email": "[email protected]"}'::jsonb - 'email';

         ?column?
    -------------------
     {"name": "Karina"}
    (1 row)

As you can see, the key email specified by the operator – was removed from the object.

It is also possible to remove an element from an array:

SELECT
    '["animal","plant","mineral"]'::jsonb - 1;

       ?column?
   -----------------
    ["animal", "mineral"]
   (1 row)

The example above shows an array containing 3 elements. Knowing that the first element in an array corresponds to the position 0 ( animal ), the – operator specifies the element at position 1 to be removed, and consequently removes plant from the array.

Delete with #-

The difference in comparing against the – operator is that with the #- operator, a nested key/value pair can be removed, if the path to be followed is provided:

SELECT
    '{"name": "Claudia",
      "contact": {
          "phone": "555-5555",
          "fax": "111-1111"}}'::jsonb #- '{contact,fax}'::text[];

                           ?column?
   ---------------------------------------------------------
    {"name": "Claudia", "contact": {"phone": "555-5555"}}
   (1 row)

Here, the fax key is nested within contact. We use the #- operator to indicate the path to the fax key in order to remove it.

The new JSONB functions

For more data processing power to edit JSONB data instead of only deleting or overwriting it, we can now use the new JSONB function:

jsonb_set

The new jsonb_set processing function allows to update the value for a specific key:

SELECT
    jsonb_set(
        '{"name": "Mary",
          "contact":
              {"phone": "555-5555",
               "fax": "111-1111"}}'::jsonb,
        '{contact,phone}',
        '"000-8888"'::jsonb,
        false);

                                    jsonb_replace
   --------------------------------------------------------------------------------
    {"name": "Mary", "contact": {"fax": "111-1111", "phone": "000-8888"}}
   (1 row)

It is easier to understand the above example knowing the structure of the jsonb_set function. It has 4 arguments:

• target jsonb: The JSONB value to be modified
• path text[]: The path to the value target to be changed, represented as a text array
• new_value jsonb: The new key/value pair to be modified (added or changed)
• create_missing boolean: An optional field that allows the creation of the new key/value if it doesn’t yet exist

Looking back at the previous example, now understanding its structure, we can see that the nested phone key within contact has been changed by the jsonb_set.

Here is one more example, now creating a new key through the use of the true boolean parameter (4th argument on the jsonb_set structure). As mentioned before, this argument defaults to true, thus it is not necessary to explicitly declare it on the next example:

SELECT
    jsonb_set(
        '{"name": "Mary",
          "contact":
              {"phone": "555-5555",
               "fax": "111-1111"}}'::jsonb,
        '{contact,skype}',
        '"maryskype"'::jsonb,
        true);

                                                 jsonb_set
   ------------------------------------------------------------------------------------------------------
    {"name": "Mary", "contact": {"fax": "111-1111", "phone": "555-5555", "skype": "maryskype"}}
   (1 row)

The skype key/value pair, which wasn’t present in the original JSONB object, was added and is nested within contact accordingly to the path specified in the 2nd argument of the jsonb_set structure.

If, instead of true on the 4th argument of jsonb_set, we have set it to false, the skype key wouldn’t be added to the JSONB object.

jsonb_pretty

Reading a JSONB entry is not that easy considering that it doesn’t preserve white spaces. The jsonb_pretty function formats the output, making it easier to be read:

SELECT
    jsonb_pretty(
        jsonb_set(
            '{"name": "Joan",
              "contact": {
                  "phone": "555-5555",
                  "fax": "111-1111"}}'::jsonb,
            '{contact,phone}',
            '"000-1234"'::jsonb));

             jsonb_pretty
   ---------------------------------
    {                              +
        "name": "Joan",            +
        "contact": {               +
            "fax": "111-1111",     +
            "phone": "000-1234"    +
        }                          +
    }
   (1 row)

Again, in this example, the value of the nested phone key is changed within contact by the value given in the 3rd argument of the jsonb_set function. The only difference is that, as we have used it together with the jsonb_pretty function, the output is shown in a more clear and readable way.

Conclusion

Contrary to what the momentary hype on “NoSQL” databases is trying to show, a non-relational database cannot be seen as a “one size fits all” solution and, certainly, won’t be everyone’s favourite cup of tea.

Because of this, when talking about “NoSQL” databases, one thing to keep in mind is if a document database will fit your use case better than a relational one. If you conclude that this is the case, a great advantage is brought by the PostgreSQL JSONB features: you can have both options (a document and a relational database) delivered by the same solution, avoiding all the complexity that using different products would bring.