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
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • Postgres-BDR ®
    • PostgreSQL High Availability
    • Kubernetes Operators for BDR & PostgreSQL
    • Managed PostgreSQL in the Cloud
    • Installers
      • Postgres Installer
      • 2UDA
    • 2ndQPostgres
    • pglogical
    • Barman
    • repmgr
    • OmniDB
    • SQL Firewall
    • Postgres-XL
  • 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 / Rubens' PlanetPostgreSQL3 / Inserting JSONB data in PostgreSQL 9.6
Rubens Souza

Inserting JSONB data in PostgreSQL 9.6

January 26, 2017/0 Comments/in Rubens' PlanetPostgreSQL /by Rubens Souza

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!

Tags: 9.6, array, documents, functions, insert, JSON, JSONB, non structured data, NOSQL, postgres, PostgresSQL, PostgresSQL 9.6
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

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
repmgr 3.3 pgDay 2017 à Paris: conférence PostgreSQL internationale
Scroll to top
×