Processing PostgreSQL JSON & JSONB data in Java

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 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"

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