Skip to content

SELECT_JSON Statements

Jump to bottom
go-jet edited this page Mar 14, 2025 · 7 revisions

In some cases, performance issues can arise with SELECT statements. The issue is that number of rows tends to grow rapidly as more tables are joined, as result set is de-normalized and contains a lot of duplicate data. For a small number of rows (e.g., fewer than 1,000), this isn't a major concern. However, as the row count exceeds a certain threshold (e.g., more than 10,000), the performance impact becomes noticeable.

One way to avoid data duplication during the transfer between the SQL server and the web server is to encode query result as JSON first on the SQL server, and then return that JSON as a result. Instead of returning a large set of rows, the query now returns a single row with one column containing the entire result as JSON.

Depending of number of duplication and how close sql server and web server are, in some cases it is possible to achieve several-fold performance improvements over standard queries.

Jet provides built-in support for JSON result generation via two specialized statement types: SELECT_JSON_OBJ and SELECT_JSON_ARR.

Contents

SELECT_JSON_OBJ

SELECT_JSON_OBJ constructs a single JSON object from the selected columns.

Example:

stmt := SELECT_JSON_OBJ(Actor.AllColumns).
	FROM(Actor).
	WHERE(Actor.ActorID.EQ(Int32(2)))

The destination must be a pointer to a struct or a map[string]any:

var dest model.Actor

err := stmt.QueryContext(ctx, db, &dest)
Click to see stmt.Sql() output

PostgreSQL/CockroachDB:

SELECT row_to_json(records) AS "json"
FROM (
          SELECT actor.actor_id AS "actorID",
               actor.first_name AS "firstName",
               actor.last_name AS "lastName",
               to_char(actor.last_update, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "lastUpdate"
          FROM dvds.actor
          WHERE actor.actor_id = $1::integer
     ) AS records;

MySQL/MariaDB:

SELECT JSON_OBJECT(
          'actorID', actor.actor_id,
          'firstName', actor.first_name,
          'lastName', actor.last_name,
          'lastUpdate', DATE_FORMAT(actor.last_update,'%Y-%m-%dT%H:%i:%s.%fZ')
     ) AS "json"
FROM dvds.actor
WHERE actor.actor_id = ?;

SELECT_JSON_ARR

SELECT_JSON_ARR constructs a JSON array of objects from the selected columns.

Example:

stmt := SELECT_JSON_ARR(Rental.AllColumns).
	FROM(Rental).
	WHERE(Rental.CustomerID.LT(Int(2))).
	ORDER_BY(Rental.StaffID.ASC())

The destination must be a pointer to a slice of structs (e.g., []model.Rental) or a slice of maps ([]map[string]any):

var dest []model.Rental

err := stmt.QueryContext(ctx, db, &dest)
Click to see stmt.Sql() output

PostgreSQL/CockroachDB:

SELECT json_agg(row_to_json(records)) AS "json"
FROM (
          SELECT rental.rental_id AS "rentalID",
               to_char(rental.rental_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "rentalDate",
               rental.inventory_id AS "inventoryID",
               rental.customer_id AS "customerID",
               to_char(rental.return_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "returnDate",
               rental.staff_id AS "staffID",
               to_char(rental.last_update, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "lastUpdate"
          FROM dvds.rental
          WHERE rental.customer_id < $1
          ORDER BY rental.staff_id ASC
     ) AS records;

MySQL/MariaDB:

SELECT JSON_ARRAYAGG(JSON_OBJECT(
          'rentalID', rental.rental_id,
          'rentalDate', DATE_FORMAT(rental.rental_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
          'inventoryID', rental.inventory_id,
          'customerID', rental.customer_id,
          'returnDate', DATE_FORMAT(rental.return_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
          'staffID', rental.staff_id,
          'lastUpdate', DATE_FORMAT(rental.last_update,'%Y-%m-%dT%H:%i:%s.%fZ')
     )) AS "json"
FROM dvds.rental
WHERE rental.customer_id < ?
ORDER BY rental.staff_id ASC;

Note

SELECT_JSON statements support the same clauses as regular SELECT statements, making them easily interchangeable.

Combining SELECT_JSON Statements

Creating Complex JSON Objects with multiple SELECT_JSON statements

SELECT_JSON statements can be combined to create more complex JSON objects.

	stmt := SELECT_JSON_OBJ(
		Customer.AllColumns,

		SELECT_JSON_ARR(Rental.AllColumns).
			FROM(Rental).
			WHERE(Rental.CustomerID.EQ(Customer.CustomerID)).
			ORDER_BY(Rental.RentalID).
			OFFSET(1).AS("Rentals"),
	).FROM(
		Customer,
	).WHERE(
		Customer.CustomerID.EQ(Int32(11)),
	)

	var dest struct {
		model.Customer

		Rentals []model.Rental
	}

	err := stmt.QueryContext(ctx, db, &dest)
Click to see stmt.Sql() output

PostgreSQL/CockroachDB:

SELECT row_to_json(records) AS "json"
FROM (
          SELECT customer.customer_id AS "customerID",
               customer.store_id AS "storeID",
               customer.first_name AS "firstName",
               customer.last_name AS "lastName",
               customer.email AS "email",
               customer.address_id AS "addressID",
               customer.activebool AS "activebool",
               to_char(customer.create_date::timestamp, 'YYYY-MM-DD') || 'T00:00:00Z' AS "createDate",
               to_char(customer.last_update, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "lastUpdate",
               customer.active AS "active",
               (
                    SELECT json_agg(row_to_json(rentals_records)) AS "rentals_json"
                    FROM (
                              SELECT rental.rental_id AS "rentalID",
                                   to_char(rental.rental_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "rentalDate",
                                   rental.inventory_id AS "inventoryID",
                                   rental.customer_id AS "customerID",
                                   to_char(rental.return_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "returnDate",
                                   rental.staff_id AS "staffID",
                                   to_char(rental.last_update, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "lastUpdate"
                              FROM dvds.rental
                              WHERE rental.customer_id = customer.customer_id
                              ORDER BY rental.rental_id
                              OFFSET $1
                         ) AS rentals_records
               ) AS "Rentals"
          FROM dvds.customer
          WHERE customer.customer_id = $2::integer
     ) AS records;

MySQL/MariaDB:

SELECT JSON_OBJECT(
          'customerID', customer.customer_id,
          'storeID', customer.store_id,
          'firstName', customer.first_name,
          'lastName', customer.last_name,
          'email', customer.email,
          'addressID', customer.address_id,
          'active', customer.active = 1,
          'createDate', DATE_FORMAT(customer.create_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
          'lastUpdate', DATE_FORMAT(customer.last_update,'%Y-%m-%dT%H:%i:%s.%fZ'),
          'Rentals', (
               SELECT JSON_ARRAYAGG(JSON_OBJECT(
                         'rentalID', rental.rental_id,
                         'rentalDate', DATE_FORMAT(rental.rental_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
                         'inventoryID', rental.inventory_id,
                         'customerID', rental.customer_id,
                         'returnDate', DATE_FORMAT(rental.return_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
                         'staffID', rental.staff_id,
                         'lastUpdate', DATE_FORMAT(rental.last_update,'%Y-%m-%dT%H:%i:%s.%fZ')
                    )) AS "json"
               FROM dvds.rental
               WHERE rental.customer_id = customer.customer_id
               ORDER BY rental.rental_id
               OFFSET ?
          )
     ) AS "json"
FROM dvds.customer
WHERE customer.customer_id = ?;

Args: [1 11]

Note that nested SELECT_JSON statement is effectively LEFT_JOIN. In this example, the nested SELECT_JSON statement is effectively performing a LEFT JOIN between Customer and Rental, even though no explicit LEFT_JOIN clause is used.

Each nested SELECT_JSON used as a projection must have an alias(or the query will panic), which will serve as the key in the resulting JSON object. The alias must match the corresponding struct field name.

Note

There is no limitation on how deeply SELECT_JSON statements can be nested.

Combining SELECT_JSON with Other Statements (SELECT, INSERT, UPDATE, DELETE)

SELECT_JSON statements can also be combined with other SQL statements to return hybrid results. For example, the following query returns the Rentals as a JSON array while Customers are returned as a regular result set.

	stmt := SELECT(
		Customer.AllColumns,

		SELECT_JSON_ARR(Rental.AllColumns).
			FROM(Rental).
			WHERE(Rental.CustomerID.EQ(Customer.CustomerID)).
			ORDER_BY(Rental.RentalID).
			OFFSET(1).AS("Rentals"),
	).FROM(
		Customer,
	).WHERE(
		Customer.CustomerID.EQ(Int32(11)),
	)

	var dest struct {
		model.Customer

		Rentals []model.Rental `json_column:"Rentals"`   // !! json_column tag
	}

	err := stmt.QueryContext(ctx, db, &dest)

In this case, the query may return multiple rows, and multiple columns of which one is JSON column named Rentals. To ensure proper scanning of the JSON column, the destination struct field must be tagged with the json_column tag.

Click to see stmt.Sql() output

PostgreSQL/CockroachDB:

SELECT customer.customer_id AS "customer.customer_id",
     customer.store_id AS "customer.store_id",
     customer.first_name AS "customer.first_name",
     customer.last_name AS "customer.last_name",
     customer.email AS "customer.email",
     customer.address_id AS "customer.address_id",
     customer.activebool AS "customer.activebool",
     customer.create_date AS "customer.create_date",
     customer.last_update AS "customer.last_update",
     customer.active AS "customer.active",
     (
          SELECT json_agg(row_to_json(rentals_records)) AS "rentals_json"
          FROM (
                    SELECT rental.rental_id AS "rentalID",
                         to_char(rental.rental_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "rentalDate",
                         rental.inventory_id AS "inventoryID",
                         rental.customer_id AS "customerID",
                         to_char(rental.return_date, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "returnDate",
                         rental.staff_id AS "staffID",
                         to_char(rental.last_update, 'YYYY-MM-DD"T"HH24:MI:SS.USZ') AS "lastUpdate"
                    FROM dvds.rental
                    WHERE rental.customer_id = customer.customer_id
                    ORDER BY rental.rental_id
                    OFFSET $1
               ) AS rentals_records
     ) AS "Rentals"
FROM dvds.customer
WHERE customer.customer_id = $2::integer;

MySQL/MariaDB:

SELECT customer.customer_id AS "customer.customer_id",
     customer.store_id AS "customer.store_id",
     customer.first_name AS "customer.first_name",
     customer.last_name AS "customer.last_name",
     customer.email AS "customer.email",
     customer.address_id AS "customer.address_id",
     customer.active AS "customer.active",
     customer.create_date AS "customer.create_date",
     customer.last_update AS "customer.last_update",
     (
          SELECT JSON_ARRAYAGG(JSON_OBJECT(
                    'rentalID', rental.rental_id,
                    'rentalDate', DATE_FORMAT(rental.rental_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
                    'inventoryID', rental.inventory_id,
                    'customerID', rental.customer_id,
                    'returnDate', DATE_FORMAT(rental.return_date,'%Y-%m-%dT%H:%i:%s.%fZ'),
                    'staffID', rental.staff_id,
                    'lastUpdate', DATE_FORMAT(rental.last_update,'%Y-%m-%dT%H:%i:%s.%fZ')
               )) AS "json"
          FROM dvds.rental
          WHERE rental.customer_id = customer.customer_id
          ORDER BY rental.rental_id
          OFFSET ?
     ) AS "Rentals"
FROM dvds.customer
WHERE customer.customer_id = ?;

Args: [1 11]

Raw JSON

In cases where no further processing is required on the retrieved JSON data, it is possible to skip unmarshaling completely and directly forward the JSON to the client. This could provide an additional minor performance boost.

To achieve this, we first need to wrap the SELECT_JSON query with a regular SELECT statement so that it can be properly scanned by QRM.

Example (using the query from above):

	stmt := SELECT(           // !! SELECT statement
		SELECT_JSON_OBJ(
			Customer.AllColumns,

			SELECT_JSON_ARR(Rental.AllColumns).
				FROM(Rental).
				WHERE(Rental.CustomerID.EQ(Customer.CustomerID)).
				ORDER_BY(Rental.RentalID).
				OFFSET(1).AS("Rentals"),
		).FROM(
			Customer,
		).WHERE(
			Customer.ID.EQ(Int32(11))
		).AS("raw_json"),              // !! json column alias
	)       

	var dest struct {
		RawJson []byte
	}

	err := stmt.Query(db, &dest)

	// .........................

	w http.ResponseWriter

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusOK)
	w.Write(dest.RawJson)

In this approach, the query returns the raw JSON data as a []byte, which is then directly written to the HTTP response, bypassing the need for unmarshalling.

Note

The generated JSON is encoded in accordance with RFC 7159, the same format used by the standard library's encoding/json package.

Custom JSON Unmarshaler

By default, the SELECT_JSON Query method unmarshals results using the json.Unmarshal function from the standard encoding/json package. It is possible to replace standard library with a different more performant library.

For example, to use the github.com/bytedance/sonic library for JSON unmarshaling, you can configure it during application initialization as follows:

import(
    "github.com/go-jet/jet/v2/qrm"
    "github.com/bytedance/sonic"
)

func ServerStartUp(){
    qrm.GlobalConfig.JsonUnmarshalFunc = sonic.Unamrshal
}

This will replace default QRM JSON unmarshaling function with Unamrshal function from the sonic libarary.

Clone this wiki locally