JSON and PostgreSQL 9.5: with even more powerful tools

PostgreSQL 9.5 has introduced a new functionality that is associated with JSONB, which significantly strengthens its existing NoSQL features. With the addition of new operators and functions, it is now possible to easily change the data stored in JSONB format. This article will describe these new operators with examples of how they can be used.

With the addition of JSON data type in version 9.2, PostgreSQL has finally started to support native JSON. Despite the fact that with the release of this version it became possible to use PostgreSQL like a "NoSQL" database, not much could be done actually at the time due to the lack of operators and exciting features. Since the release of 9.2 version, JSON support was significantly improved in each of the next PostgreSQL version, resulting today in overcoming the original limitations.

Probably the most memorable changes was the addition of JSONB datatype in PostgreSQL 9.4 and, in the current PostgreSQL version 9.5, the performance of new operators and functions that will allow You to change and manage JSONB data.

In this article, we will focus on the new features brought in Postgres 9.5. However, before you dive into this topic, if You want to learn more about the differences between JSON and JSONB data types, or if You have doubts about the appropriateness of using "NoSQL" database in Your case, I recommend to read the following articles on the above topics (the titles of the articles and authors are left in the original):

the

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

the

New JSONB operators

Operators and functions present in PostgreSQL prior to version 9.4, was only able to extract the JSONB data. Therefore, in order to change the data, had to extract them, edit, then re-insert them into the database. Not too practical, some would say.

New operators in PostgreSQL 9.5, which was based on jsonbx extension for PostgreSQL 9.4 was able to change, greatly improving the possibility of interaction with JSONB data.

Concatenation using ||

Now You can concatenate two JSONB object using the ||operator:

the

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

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

In this example, the key city added to the first JSONB object.

In addition, this operator can be used to overwrite existing values:

the

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

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

In this case, the key value population was rewritten to the value of the second object.

Delete by using -

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

the

SELECT
'{"name": "Karina",
"email": "karina@localhost"}'::jsonb - 'email';

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

As You can see, the key email specified by the operator is has been removed from the object.

It's also possible to remove an item from array:

the

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

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

Parent example shows an array of 3 elements. Knowing that the first element of the array corresponds with 0 position (animal), operator is indicates the element located at position 1 and remove plant array.

Deleting with #-

The difference in comparison with the operator is is that #- the operator can remove the nested pair key/value, if the path to it is specified:

the

SELECT
'{"name": "Claudia",
"contact": {
"phone": "555-5555",


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

Here, the key is fax invested in contact. We use the operator #- path to key fax to remove it.

the

New JSONB functions

For more power when working with JSONB data, instead of just deleting them, and rewriting, we can now use the new JSONB feature:

jsonb_set

New function handling jsonb_set allows to change the value for a specific key:

the

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 much easier to understand an example of a parent knowing the structure of jsonb_set function. It has 4 arguments:
the

the
• target jsonb: JSONB value that should be changed
the
• , path text[]: the path to the selected values shown in a text array
the
• new_value jsonb: new pair key/value that needs to be changed (or added)
the
• create_missing boolean: Optional field that allows creating a new mapping of key/value if it does not already exist

Looking back at the previous example, this time knowing its structure, we see that nested in contact the key phone was changed by jsonb_set.

Here's another example, this time creating a new key through the use of Boolean values true (4th argument in the structure of the function jsonb_set). As mentioned above, this argument has the value true by default, so it is not necessary to specify it explicitly in the following example:

the

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)

A bunch of key/value skype, which is not present in the original JSONB object has been added and is at the nesting level specified in the second argument of jsonb_set function.

If is true in the 4th argument to the function jsonb_set to put false, the key skype will not be added to the original JSONB object.

jsonb_pretty

Read JSONB recording not so much just, given that it does not store the gaps. The jsonb_pretty formats the output, making it easier to read:

the

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 enclosed in contact key phone changed to the value passed in the 3rd argument of the function jsonb_set. The only difference is that we used now in combination with a jsonb_pretty, the output is now displayed in a more understandable and readable form.

the

Conclusion

Contrary to what we are trying to prove that non-relational database may not be a universal solution, not all will agree with this.

Therefore, speaking about "NoSQL" databases, you need to keep in mind about the fact if this database is better than relational. PostgreSQL JSONB with its features can give You an advantage: you can use both options (and document-oriented, and relational databases) provided the same solution, avoiding all the complexities of using two different products.

Article based on information from habrahabr.ru