JSON and PostgreSQL 9.5: with even more powerful tools
PostgreSQL 9.5 introduced a new feature associated with JSONB, greatly enhancing its existing NoSQL features. With the addition of new operators and functions, it is now possible to easily modify data stored in JSONB format. This article will present these new operators with examples of how they can be used.
With the addition of the JSON data type in version 9.2, PostgreSQL has finally started supporting JSON natively. Despite the fact that with the release of this version it became possible to use PostgreSQL as a “NoSQL” database, not much could actually be done at that time due to the lack of operators and interesting functions. Since the release of version 9.2, JSON support has improved significantly in each subsequent version of PostgreSQL, today resulting in a complete overcoming of the initial limitations.
Probably the most memorable changes were adding a JSONB data type to PostgreSQL 9.4 and, in the current version of PostgreSQL 9.5, introducing new operators and functions that allow you to modify and manage JSONB data.
In this article, we will focus on the new features brought by Postgres 9.5. However, before diving 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 relevance of using “NoSQL” database in your case, I recommend reading the following articles on The above topics (the titles of the articles and the authors are left in the original):
')
Operators and functions that were present in PostgreSQL before version 9.4 allowed only to extract JSONB data. Therefore, in order to change this data, you had to retrieve it, modify it, then re-insert it into the database. Not very practical, some would say.
The new operators introduced in PostgreSQL 9.5, which were based on the jsonbx extension for PostgreSQL 9.4, were able to change this, greatly improving the interoperability of JSONB data.
Now you can concatenate two JSONB objects using the operator ||:
In this example, the city key is added to the first JSONB object.
In addition, this operator can be used to overwrite existing values:
In this case, the value of the population key was rewritten to the value from the second object.
Operator - can remove a key / value pair from a JSONB object:
As you can see, the email key specified by the - operator has been removed from the object.
In addition, it is possible to remove an element from an array:
The superior example shows an array consisting of 3 elements. Knowing that the first element of the array corresponds to the 0 position ( animal ), the operator points to the element located at position 1 and removes plant from the array.
The difference in comparison with the operator - is that # - the operator can delete the nested key / value pair if the path to it is specified:
Here, the fax key is embedded in contact . We use the # operator - with the path to the fax key to delete it.
For more power when working with JSONB data, instead of just deleting and rewriting it, we can now use the new JSONB function:
The new jsonb_set processing function allows you to change the value for a specific key:
It is much easier to understand the upstream example by knowing the structure of the jsonb_set function. It has 4 arguments:
Looking back at the previous example, this time understanding its structure, we see that the phone key nested in the contact has been changed by the jsonb_set function.
Here is another example, this time creating a new key by using the boolean value true (4th argument in the structure of the jsonb_set function). As mentioned above, this argument is true by default, so it is not necessary to specify it explicitly in the following example:
The skype key / value binding , which is not present in the original JSONB object, was added and is at the nesting level that was specified in the second argument of the jsonb_set function.
If instead of true , the 4th argument of the jsonb_set function is set to false , then the skype key will not be added to the original JSONB object.
Reading JSONB entries is not so easy, considering that it does not store spaces. The jsonb_pretty function formats the output, making it easier to read:
Again, in this example, the value of the phone key nested in contact is changed to the value passed in the 3rd argument of the jsonb_set function. The only difference is that we used it now with the jsonb_pretty function, the output is now shown in a more understandable and readable form.
Despite the fact that they are trying to prove to us that a non-relational database cannot be a universal solution, not everyone will agree with this.
Therefore, when speaking of “NoSQL” databases, you need to keep in your mind the thought of whether such a database is better for you than a relational one. PostgreSQL, with its JSONB features, can give you an advantage: you can use both options (both document-oriented and relational databases) provided by the same solution, avoiding all the difficulties of using two different products.
With the addition of the JSON data type in version 9.2, PostgreSQL has finally started supporting JSON natively. Despite the fact that with the release of this version it became possible to use PostgreSQL as a “NoSQL” database, not much could actually be done at that time due to the lack of operators and interesting functions. Since the release of version 9.2, JSON support has improved significantly in each subsequent version of PostgreSQL, today resulting in a complete overcoming of the initial limitations.
Probably the most memorable changes were adding a JSONB data type to PostgreSQL 9.4 and, in the current version of PostgreSQL 9.5, introducing new operators and functions that allow you to modify and manage JSONB data.
In this article, we will focus on the new features brought by Postgres 9.5. However, before diving 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 relevance of using “NoSQL” database in your case, I recommend reading the following articles on The above topics (the titles of the articles and the authors are left in the original):
')
- 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
New JSONB Operators
Operators and functions that were present in PostgreSQL before version 9.4 allowed only to extract JSONB data. Therefore, in order to change this data, you had to retrieve it, modify it, then re-insert it into the database. Not very practical, some would say.
The new operators introduced in PostgreSQL 9.5, which were based on the jsonbx extension for PostgreSQL 9.4, were able to change this, greatly improving the interoperability of JSONB data.
Concatenation with ||
Now you can 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 this example, the city key is added to the first JSONB object.
In addition, this operator can be used to overwrite 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 population key was rewritten to the value from the second object.
Uninstalling with -
Operator - can remove a key / value pair from a JSONB object:
SELECT '{"name": "Karina", "email": "karina@localhost"}'::jsonb - 'email'; ?column? ------------------- {"name": "Karina"} (1 row) As you can see, the email key specified by the - operator has been removed from the object.
In addition, it is possible to remove an element from an array:
SELECT '["animal","plant","mineral"]'::jsonb - 1; ?column? ----------------- ["animal", "mineral"] (1 row) The superior example shows an array consisting of 3 elements. Knowing that the first element of the array corresponds to the 0 position ( animal ), the operator points to the element located at position 1 and removes plant from the array.
Removing with # -
The difference in comparison with the operator - is that # - the operator can delete the nested key / value pair if the path to it is specified:
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 embedded in contact . We use the # operator - with the path to the fax key to delete it.
New JSONB functions
For more power when working with JSONB data, instead of just deleting and rewriting it, we can now use the new JSONB function:
jsonb_set
The new jsonb_set processing function allows you to change 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 much easier to understand the upstream example by knowing the structure of the jsonb_set function. It has 4 arguments:
- target jsonb: JSONB value to be changed
- path text []: path to the value of interest, represented as a text array
- new_value jsonb: a new key / value binding that needs to be changed (or added)
- create_missing boolean: An optional field that allows the creation of a new key / value bundle if it does not already exist
Looking back at the previous example, this time understanding its structure, we see that the phone key nested in the contact has been changed by the jsonb_set function.
Here is another example, this time creating a new key by using the boolean value true (4th argument in the structure of the jsonb_set function). As mentioned above, this argument is true by default, so it is not necessary to specify it explicitly in the following 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 binding , which is not present in the original JSONB object, was added and is at the nesting level that was specified in the second argument of the jsonb_set function.
If instead of true , the 4th argument of the jsonb_set function is set to false , then the skype key will not be added to the original JSONB object.
jsonb_pretty
Reading JSONB entries is not so easy, considering that it does not store spaces. The jsonb_pretty function formats the output, making it easier to 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 phone key nested in contact is changed to the value passed in the 3rd argument of the jsonb_set function. The only difference is that we used it now with the jsonb_pretty function, the output is now shown in a more understandable and readable form.
Conclusion
Despite the fact that they are trying to prove to us that a non-relational database cannot be a universal solution, not everyone will agree with this.
Therefore, when speaking of “NoSQL” databases, you need to keep in your mind the thought of whether such a database is better for you than a relational one. PostgreSQL, with its JSONB features, can give you an advantage: you can use both options (both document-oriented and relational databases) provided by the same solution, avoiding all the difficulties of using two different products.
Source: https://habr.com/ru/post/304026/
All Articles