Extracting key/value pairs from json using jooq/postgresql - java

Question

I have the json data in a PostgreSQL database column in the following form:

myData [{"key1": "value1", "key2": "value2"}, {"key1": "value1", "key2": "value2"} ]

where myData is an array containing two objects. I read the following links for solving my issue with only java but my question is slightly different. Converting JSON to Java and Parsing JSON Object in Java

1. Would it be more efficient to extract key/value pairs in database(postgresql) or should i get the whole string in java and use java libraries? Is doing it in data base even possible?

2. If doing this in database is an option, does JOOQ provides any support for this?

3. If parsing in Java is the better option, which of the two libraries given in the above links are more stable/efficient? org.json or Gson

Answer 1

You have a couple of interesting questions here.

1. Query logic in the database vs. query logic in Java

It depends. First off, in PostgreSQL specifically, jsonb has better index support. I'm no expert on the topic, but I'm pretty sure you will come to this conclusion once you benchmark 1-2 things. Here's an interesting blog post comparing different data types for performance in PostgreSQL:

http://www.databasesoup.com/2015/01/tag-all-things.html

... which brings me to the point of whether to put predicates into your SQL query, or execute them later in Java. In general: Put it in the database. If your predicate is very selective, then you can:

1. Avoid a lot of unnecessary I/O in the database
2. Avoid a lot of unnecessary I/O with the database

Both of which result in much lower latency of your queries. If your predicate is not very selective, then this is hardly ever a problem, except under extreme load. But still, if your system is under extreme load and your predicates are selective, you will still greatly reduce that load if you run the predicate in the database.

2. jOOQ support for JSON predicates

jOOQ currently doesn't offer any out-of-the-box support for JSON(B) predicates, but you can easily build a utility yourself using jOOQ's plain SQL support:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql

In essence, just write:

public static Condition someJsonPredicate(Field<?> someJsonColumn, String someValue) {
    return DSL.condition("some_json_predicate({0}, {1})", 
        someJsonColumn, DSL.val(someValue));
}

3. org.json vs Gson

I won't answer this part here as your benchmark may differ from mine.

Answer 2

Solution to get JSON data from a PostgreSQL database in Java.

• Summary of this article.
• I found last part of this answer really powerful and nice to read. Easy to wrap it in a method
• Codes are to provide visual hint, not fully working example.
• This answer do not use jooq.

Comparaison of PostgreSQL JSON (store as text) and JSONB (store as binary)

JSON	JSONB
Stores data in text format	Stores data in decomposed binary format
Input is fast, as no conversion are required	Input is slightly slower, as there is an overhead related to binary conversion
Processing functions must re-parse the data on each execution	Re-parsing is not needed, making data processing significantly faster
All white space and line feeds in the input are preserved as-is	Extra white space and line feeds are stripped
Indexing is not supported	Indexing is supported
Duplicate keys are retained, processing functions only consider the last value	Duplicate keys are purged at input, only the last value is stored
Order of the keys is preserved	Order is not preserved

Database structure

my_table
   id = PK
   json_column = JSON or BYTEA

Example of data for json_column

    {"customer_name": "John", "items": { "description": "milk", "quantity": 4 } }
    {"customer_name": "Susan", "items": { "description": "bread", "quantity": 2 } }

JAVA parsing json (bof bof...)

String sql = "select json_column from my_table";
Statement stmt = conn.createStatement(...);
ResultSet rs = stmt.executeQuery(sql);
 
while(rs.next()) {
   // Not really true as data are store in binary, not nice to parse it in string here...
   JSONObject json = new JSONObject(rs.getString(1));
   JSONObject json2 = (JSONObject)json.get("items");
   System.out.println(json.get("customer_name"));
   System.out.println(json2.get("quantity"));
}

PostgreSQL (LOVE THIS PART!)

select json_column->'customer_name' from my_table
select json_column->'items'->'quantity' from my_table

Both (really nice solution)

sql = "select json_column->'items'->'quantity' from my_table";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
   rs.getInt(1);
   ...
}