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

This Post Has 1 Comment

  1. jgigov says:

    For anyone interested in using standard JSR-353 javax.json.* objects in combination with Hibernate, there is now a type module you can use for that.

    http://mvnrepository.com/artifact/com.mopano/hibernate-json-contributor

    You just have to add the dependency to your project. Type contributors are loaded at the creation of the EntityManagerFactory.

    Minimum Hibernate version for it is 5.1 and any JSR-353 implementation. One limitation is that you currently cannot bind JsonArray objects as parameters to native queries. That is a problem from the hibernate-core and may be fixed by 5.2.9 and possibly back-ported since 5.1 is still under maintenance.

Leave A Reply