Search code examples
javajsonpostgresqljooq

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

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

Solution

  • 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.