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 / Umair's PlanetPostgreSQL3 / Processing PostgreSQL JSON & JSONB data in Java

This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post.

pgjava

Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations.

String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation.

JSON & JSONB

JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects in binary format. Major differences between JSON & JSONB are highlighted in the table below:

 

JSON

JSONB

1

Stores data in text format

Stores data in decomposed binary format

2

Input is fast, as no conversion are required

Input is slightly slower, as there is an overhead related to binary conversion

3

Processing functions must re-parse the data on each execution

Re-parsing is not needed, making data processing significantly faster

4

All white space and line feeds in the input are preserved as-is

Extra white space and line feeds are stripped

5

Indexing is not supported

Indexing is supported

6

Duplicate keys are retained, processing functions only consider the last value

Duplicate keys are purged at input, only the last value is stored

7

Order of the keys is preserved

Order is not preserved

JSON data definition

A JSON column is created just like any other data type. We create a table ‘sales’ below (which we will use in subsequent examples) containing 2 columns, ‘id’ and ‘sale’, with the latter being a JSON:

json_sample=# CREATE TABLE sales (id INT, sale JSON); 
CREATE TABLE

JSON data insertion

The JSON data type checks for a valid JSON format, so insert statements should be mindful of that. The simple Java program below inserts 4 records into the table we just created.

String[] json = {"{\"customer_name\": \"John\", \"items\": { \"description\": \"milk\", \"quantity\": 4 } }",
 "{\"customer_name\": \"Susan\", \"items\": { \"description\": \"bread\", \"quantity\": 2 } }",
 "{\"customer_name\": \"Mark\", \"items\": { \"description\": \"bananas\", \"quantity\": 12 } }",
 "{\"customer_name\": \"Jane\", \"items\": { \"description\": \"cereal\", \"quantity\": 1 } }"};

try {
 String sql = "INSERT INTO sales VALUES (?, ?::JSON)";
 PreparedStatement ps = conn.prepareStatement(sql);
 
 for (int i=0; i<4; i++) {
  ps.setInt (1, i+1);
  ps.setObject (2, json[i]);
  ps.executeUpdate();
 }
 conn.commit();

} catch (Exception e) {
   System.out.println(e.getMessage());
   e.printStackTrace();
}

Notice how the string is being cast to JSON within the prepared statement.

This is how the data shows up in psql after the insert above:

sample=# select * from sales;
 id | sale
 ----+-----------------------------------------------------------------------------------
 1 | {"customer_name": "John", "items": { "description": "milk", "quantity": 4 } }
 2 | {"customer_name": "Susan", "items": { "description": "bread", "quantity": 2 } }
 3 | {"customer_name": "Mark", "items": { "description": "bananas", "quantity": 12 } }
 4 | {"customer_name": "Jane", "items": { "description": "cereal", "quantity": 1 } }
 (4 rows)

JSON data retrieval

While retrieving JSON data, you can use either PostgreSQL native operators to access individual elements or you can use the JSONObject Java library to process the objects within Java. Examples of both cases are given below.

Using PostgreSQL operator

PostgreSQL provides the ‘->’ operator to retrieve values of the various keys in a JSON object. The sample program below retrieves a list of ‘customer_name’ and then a list of ‘description’ of ‘items’ of the sale. The latter is an embedded JSON.

try {
 /*
 Retrieving customer_name
 */
 String sql = "select sale->'customer_name' from sales";
 PreparedStatement ps = conn.prepareStatement(sql);
 ResultSet rs = ps.executeQuery();
 
 while(rs.next()) {
 System.out.println(rs.getString(1));
 }
 
 System.out.println("******************************************");
 
 /*
 Retrieving description, which is an embedded JSON
 */
 sql = "select sale->'items'->'description' from sales";
 ps = conn.prepareStatement(sql);
 rs = ps.executeQuery();
 
 while(rs.next()) {
 System.out.println(rs.getString(1));
 }

} catch (Exception e) {
 System.out.println(e.getMessage());
 e.printStackTrace();
}

Output of the program above is:

"John"
"Susan"
"Mark"
"Jane"
******************************************
"milk"
"bread"
"bananas"
"cereal"

Using JSONObject

In order to use JSONObject with your Java program, you need to have its library jar file in your CLASSPATH. The jar file is freely available from many locations including this one. Basic JSON manipulation will be described below, but you can get more API details here.

Following Java code achieves the same result as demonstrated above:

try {
 /*
  Retrieving customer_name
 */
 String sql = "select sale from sales";
 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
 ResultSet rs = stmt.executeQuery(sql);
 
 JSONObject json;
 JSONObject json2;
 
 while(rs.next()) {
  json = new JSONObject(rs.getString(1));
  System.out.println(json.get("customer_name"));
 }
 
 System.out.println("******************************************");
 
 /*
 Retrieving description, which is an embedded JSON
 */
 rs.first();
 
 do {
  json = new JSONObject(rs.getString(1));
  json2 = (JSONObject)json.get("items");
  System.out.println(json2.get("description"));
 } while(rs.next());

} catch (Exception e) {
 System.out.println(e.getMessage());
 e.printStackTrace();
}

Output from this program is:

John
Susan
Mark
Jane
******************************************
milk
bread
bananas
cereal

Other blogs in this series

Using Querydsl with PostgreSQL

Using Java Object Oriented Querying (jOOQ) with PostgreSQL

Using Hibernate Query Language (HQL) with PostgreSQL 

Using Java arrays to insert, retrieve, & update PostgreSQL arrays

Popular Articles from EDB

  • Connecting PostgreSQL using psql and pgAdmin
  • How to use PostgreSQL with Django | EDB
  • How to use PostgreSQL with Laravel | EDB
  • 10 Examples of PostgreSQL Stored Procedures
  • How to import and export data using CSV files in PostgreSQL

Featured Articles from EDB

  • PostgreSQL Replication and Automatic Failover Tutorial
  • PostgreSQL vs. SQL Server (MSSQL) - Extremely Detailed ...
  • The Complete Oracle to PostgreSQL Migration Guide ...
  • PostgreSQL vs. MySQL: A 360-degree Comparison ...
  • How to use pg_dump and pg_restore in multi-host ...

Useful Links from EDB

  • EDB Postgres Advanced Server: The Oracle Alternative
  • Backup and Recovery Tool: Disaster Recovery for PostgreSQL
  • Migration Portal: Convert Oracle database schemas to ...
  • Migration Toolkit: Command-line data migration tool
  • Managing Postgres Replication Made Easy

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
Explaining CREATE INDEX CONCURRENTLY Problem with Buildfarm registrations fixed
Scroll to top
×