Think about using JSON arrays as a substitute of objects to switch information

When implementing the superior MULTISET operator in jOOQ, its implementation principally relied on SQL/JSON assist of varied RDBMS. Briefly, whereas commonplace SQL helps nested collections by way of ARRAY or MULTISET operators like this:

SELECT
  f.title,
  MULTISET(
    SELECT a.first_name, a.last_name
    FROM actor AS a
    JOIN film_actor AS fa ON a.actor_id = fa.actor_id
    WHERE fa.film_id = f.film_id
  )
FROM movie AS f;

That is poorly supported in most RDBMS, so jOOQ emulates it utilizing SQL/JSON as follows (or related):

SELECT
  f.title,
  (
    SELECT json_arrayagg(json_array(a.first_name, a.last_name))
    FROM actor AS a
    JOIN film_actor AS fa ON a.actor_id = fa.actor_id
    WHERE fa.film_id = f.film_id
  )
FROM movie AS f;

Wait a second. A JSON array of arrays??

You’d anticipate a nested assortment in JSON to be of this type, usually, for debuggability and good measure, and so forth. and so forth.:

[
{
"first_name": "John",
"last_name": "Doe"
}, {
"first_name": "Jane",
"last_name": "Smith"
}, ...
]

However jOOQ is serialising this, as a substitute??

[
[ "John", "Doe" ],
[ "Jane", "Smith" ],
...
]

Why, sure in fact! jOOQ is in full management of your SQL assertion and is aware of precisely what column (and information kind) is at which place, since you helped jOOQ assemble not solely the question object mannequin, but additionally the end result construction. So, a a lot quicker index entry is feasible, in comparison with the a lot slower column title entry.

The identical is true for abnormal end result units, by the best way, the place jOOQ at all times calls JDBC’s ResultSet.getString(int), for instance, over ResultSet.getString(String). Not solely is it quicker, but additionally extra dependable. Take into consideration duplicate column names, e.g. when becoming a member of two tables that each include an ID column. Whereas JSON isn’t opinionated about duplicate object keys, not all JSON parsers assist this, not to mention Java Map varieties.

Enjoyable reality, whereas the Map API specification forbids duplicate keys, an implementation might in precept ignore this specification and outline non-standard behaviour that permits for accessing duplicate keys by way of the iteration strategies, comparable to Map::entrySet.

See some experiments on this difficulty right here: https://github.com/jOOQ/jOOQ/points/11889

In any case, as soon as access-by-name is changed by positional entry, duplicate column names are not a difficulty.

Not associated to SQL

Whereas jOOQ does this in a SQL context, be aware that you would additionally do that between any shopper and server software with any programming languages on all sides. That is by no means associated to SQL itself!

When to use this recommendation

Efficiency recommendation is at all times a troublesome factor. There’s no such factor as “at all times” (use JSON arrays) or “by no means” (use JSON objects). It’s a tradeoff. Within the case of jOOQ, JSON arrays of arrays are getting used to serialise information that customers don’t see in any respect. Customers don’t even suppose when it comes to JSON, when utilizing the MULTISET operator, and since it’s mature sufficient now, they hardly must debug the serialisation. If we might, we’d use a binary serialisation format between the server and the shopper in jOOQ for even quicker outcomes. Regrettably, there’s no such factor in SQL as binary information aggregation.

If you design an API (particularly one that’s consumed by others), then readability could also be far more necessary than growing pace by some percentages. JSON objects are far more clear when modelling an information kind, in comparison with JSON arrays that solely assist positional area entry.

I feel that this recommendation applies to many technical JSON serialisation use-cases, the place JSON must be used, but when it had been doable, binary codecs could be a most well-liked choice. But when doubtful, at all times do measure first!

Compression

Individuals could also be lured into pondering that that is about information switch, in case of which compression might mitigate a number of the repetitive object key overheads. That is definitely true, particularly in an HTTP context.

However the price of compression and decompression continues to be current when it comes to CPU overhead at each ends of the switch, so whereas it reduces the transferred information measurement, it is perhaps a lot easier to simply do that manually with a special information structure.

Benchmark

What would a declare about issues being quicker be with no reproducible benchmark? The next benchmark makes use of:

H2 in-memory, however you would definitely reproduce it wherever else, too, e.g. on Oracle or PostgreSQL
JMH, regardless of this not being a micro benchmark. JMH provides nice reproducibility and statistics instruments (common, commonplace deviation, and so forth.)

Through the use of these two instruments, the benchmark can measure all of:

The server facet implications of serialising information
The shopper facet implications of parsing information
The community switch overhead (not measured within the case of H2, so compression wouldn’t assist right here anyway)

It makes use of JDBC instantly, not jOOQ, so mapping overheads are omitted. It does use jOOQ’s built-in JSON parser, although, however you’ll get related outcomes with some other JSON parser, e.g. Jackson.

The outcomes on my machine are (larger is best)

For H2 (in reminiscence):

Benchmark                                   Mode  Cnt        Rating       Error  Models
JSONObjectVsArrayBenchmark.testJsonArray   thrpt    7  1005894.475 ┬▒ 57611.223  ops/s
JSONObjectVsArrayBenchmark.testJsonObject  thrpt    7   809580.238 ┬▒ 19848.664  ops/s

For PostgreSQL:

Benchmark                                   Mode  Cnt     Rating      Error  Models
JSONObjectVsArrayBenchmark.testJsonArray   thrpt    7  1588.895 ┬▒  433.826  ops/s
JSONObjectVsArrayBenchmark.testJsonObject  thrpt    7  1387.053 ┬▒ 1132.281  ops/s

In every case, there’s roughly a 15% – 25% enchancment for a small information set (10 rows with 2 columns). I might anticipate it to be extra important for bigger outcomes, e.g. right here’s 10000 rows once more:

For H2:

Benchmark                                   Mode  Cnt     Rating    Error  Models
JSONObjectVsArrayBenchmark.testJsonArray   thrpt    7  2932.684 ┬▒ 41.095  ops/s
JSONObjectVsArrayBenchmark.testJsonObject  thrpt    7  1643.838 ┬▒ 31.943  ops/s

For PostgreSQL:

Benchmark                                   Mode  Cnt    Rating   Error  Models
JSONObjectVsArrayBenchmark.testJsonArray   thrpt    7  122.875 ┬▒ 7.133  ops/s
JSONObjectVsArrayBenchmark.testJsonObject  thrpt    7   71.916 ┬▒ 3.232  ops/s

These outcomes are important sufficient to justify the decreased readability, on condition that each jOOQ question utilizing MULTISET will revenue from the speed-up.

The benchmark code is under. It makes use of a quite simple JSON parser handler, which doesn’t actually maintain observe of object keys or array indexes, simply assumes that there’s precisely 1 quantity column and 1 string column. This shouldn’t actually matter for extra complicated outcomes, although.

Benchmark code under:

package deal org.jooq.impl;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Assertion;
import java.util.ArrayList;
import java.util.Checklist;
import java.util.Properties;

import org.jooq.DSLContext;
import org.jooq.check.benchmarks.native.PGQueryBenchmark.BenchmarkState;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.Fork;
import org.openjdk.jmh.annotations.Degree;
import org.openjdk.jmh.annotations.Measurement;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.Setup;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.annotations.TearDown;
import org.openjdk.jmh.annotations.Warmup;

@Fork(worth = 1, jvmArgsAppend = "-Dorg.jooq.no-logo=true")
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class JSONObjectVsArrayBenchmark {

    @State(Scope.Benchmark)
    public static class BenchmarkState {

        DSLContext ctx;
        Connection connection;
        Assertion  assertion;

        @Setup(Degree.Trial)
        public void setup() throws Exception {
            // H2 setup
            connection = DriverManager.getConnection("jdbc:h2:mem:json-benchmark", "sa", "");
            ctx = DSL.utilizing(connection);

            assertion = connection.createStatement();
            assertion.executeUpdate("drop desk if exists t");
            assertion.executeUpdate("create desk t (number_column int, string_column varchar(100))");
            assertion.executeUpdate("insert into t choose i, i from system_range(1, 10) as t(i)");

            // PostgreSQL setup
//            attempt (InputStream is = BenchmarkState.class.getResourceAsStream("/config.postgres.properties")) {
//                Properties p = new Properties();
//                p.load(is);
//                Class.forName(p.getProperty("db.postgres.driver"));
//                connection = DriverManager.getConnection(
//                    p.getProperty("db.postgres.url"),
//                    p.getProperty("db.postgres.username"),
//                    p.getProperty("db.postgres.password")
//                );
//                ctx = DSL.utilizing(connection);
//
//                assertion = connection.createStatement();
//                assertion.executeUpdate("drop desk if exists t");
//                assertion.executeUpdate("create desk t (number_column int, string_column varchar(100))");
//                assertion.executeUpdate("insert into t choose i, i from generate_series(1, 10) as t(i)");
//            }
        }

        @TearDown(Degree.Trial)
        public void teardown() throws Exception {
            assertion.executeUpdate("drop desk if exists t");
            assertion.shut();
            connection.shut();
        }
    }

    // Simply present that each strategies actually do the identical factor
    public static void foremost(String[] args) throws Exception {
        JSONObjectVsArrayBenchmark f = new JSONObjectVsArrayBenchmark();
        BenchmarkState state = new BenchmarkState();
        state.setup();

        System.out.println(f.testJsonObject(state));
        System.out.println(f.testJsonArray(state));
    }

    file R(int n, String s) {}

    @Benchmark
    public Checklist testJsonObject(BenchmarkState state) throws SQLException {
        Checklist end result = new ArrayList<>();

        attempt (ResultSet rs = state.assertion.executeQuery(

            // H2 syntax (if solely there was a approach to summary over syntax ;)
            """
            choose
              json_arrayagg(json_object(
                key number_column worth number_column,
                key string_column worth string_column
              ))
            from t
            """

            // PostgreSQL syntax
//            """
//            choose
//              json_agg(json_build_object(
//                number_column, number_column,
//                string_column, string_column
//              ))
//            from t
//            """
        )) {
            whereas (rs.subsequent()) {
                new JSONParser(state.ctx, rs.getString(1), ch(end result)).parse();
            }
        }

        return end result;
    }

    @Benchmark
    public Checklist testJsonArray(BenchmarkState state) throws SQLException {
        Checklist end result = new ArrayList<>();

        attempt (ResultSet rs = state.assertion.executeQuery(

            // H2 syntax
            """
            choose
              json_arrayagg(json_array(number_column, string_column))
            from t
            """

            // PostgreSQL syntax
//            """
//            choose
//              json_agg(json_build_array(number_column, string_column))
//            from t
//            """
        )) {
            whereas (rs.subsequent()) {
                new JSONParser(state.ctx, rs.getString(1), ch(end result)).parse();
            }
        }

        return end result;
    }

    personal JSONContentHandler ch(Checklist end result) {
        return new JSONContentHandler() {

            int degree;
            int n;
            String s;

            @Override
            public void valueTrue() {
            }

            @Override
            public void valueString(String string) {
                s = string;
            }

            @Override
            public void valueNumber(String string) {
                n = Integer.parseInt(string);
            }

            @Override
            public void valueNull() {
            }

            @Override
            public void valueFalse() {
            }

            @Override
            public void startObject() {
                if (degree >= 1) {
                    n = 0;
                    s = null;
                }
            }

            @Override
            public void startArray() {
                if (degree++ >= 1) {
                    n = 0;
                    s = null;
                }
            }

            @Override
            public void property(String key) {
            }

            @Override
            public void endObject() {
                end result.add(new R(n, s));
            }

            @Override
            public void endArray() {
                if (level-- >= 1) {
                    end result.add(new R(n, s));
                }
            }
        };
    }
}