2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Featured3 / JSONB and PostgreSQL 9.5: with even more powerful tools!
Rubens Souza

JSONB and PostgreSQL 9.5: with even more powerful tools!

May 30, 2016/2 Comments/in Featured, Rubens' PlanetPostgreSQL /by Rubens Souza

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.

jasonb

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.

Tags: 9.5, documents, functions, JSON, JSONB, modifiers, non structured data, NOSQL, operators, postgres, PostgresSQL, PostgresSQL 9.5
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
2 replies
  1. who_cares
    who_cares says:
    May 31, 2016 at 9:26 pm

    While I love postgres;

    the reason for me to use most of the ‘nosql’ data stores is simply to be able to have HA and horizontal scale. once we get true, useful, HA and realistic sharding built in where servers can come and go without causing an outage everyone will run back to RDBMS’s that also offer JSON as a data type.

    until them it’s put relation stuff in postgres, and sometimes other stuff elsewhere.

    Reply
  2. Ariden
    Ariden says:
    December 16, 2016 at 2:29 pm

    jsonb_set is not functionnal:

    “No function matches the given name and argument types. You might need to add explicit type casts”.

    …

    > PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Using Hibernate Query Language (HQL) with PostgreSQL Application users vs. Row Level Security
Scroll to top
×