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

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;

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;

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.Listing;
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.Stage;
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(Stage.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
//            strive (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(Stage.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 major(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));
    }

    report R(int n, String s) {}

    @Benchmark
    public Listing<R> testJsonObject(BenchmarkState state) throws SQLException {
        Listing<R> consequence = new ArrayList<>();

        strive (ResultSet rs = state.assertion.executeQuery(

            // H2 syntax (if solely there was a solution 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(consequence)).parse();
            }
        }

        return consequence;
    }

    @Benchmark
    public Listing<R> testJsonArray(BenchmarkState state) throws SQLException {
        Listing<R> consequence = new ArrayList<>();

        strive (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(consequence)).parse();
            }
        }

        return consequence;
    }

    non-public JSONContentHandler ch(Listing<R> consequence) {
        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() {
                consequence.add(new R(n, s));
            }

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