Nested Structs and Joins in Go Row Scanning Done Well

A user has many orders. In Go that is a User struct with a slice of Order. In SQL it is a join, and the result of a join is flat: one row per order, with the user's columns repeated. Turning flat rows into nested structs is a problem every data layer meets, and the ways of solving it have different costs. This article shows the two good ones and the bad one, on PostgreSQL 18 with pgx v5 and Go 1.25.6.

The data

Three users: ada has two orders, grace one, and linus none. The query joins them with a LEFT JOIN so that linus still appears.

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

type Order struct {
	ID    int `db:"order_id" json:"id"`
	Cents int `db:"cents" json:"cents"`
}

type UserWithOrders struct {
	ID     int     `db:"id" json:"id"`
	Name   string  `db:"name" json:"name"`
	Orders []Order `json:"orders"`
}

type joinRow struct {
	UserID  int    `db:"id"`
	Name    string `db:"name"`
	OrderID *int   `db:"order_id"` // NULL for a user with no orders (left join)
	Cents   *int   `db:"cents"`
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	_, err = pool.Exec(ctx, `drop table if exists o594; drop table if exists u594;
		create table u594 (id int primary key, name text);
		create table o594 (order_id int primary key, user_id int references u594(id), cents int);
		insert into u594 values (1, 'ada'), (2, 'grace'), (3, 'linus');
		insert into o594 values (10, 1, 500), (11, 1, 250), (12, 2, 900)`)
	if err != nil {
		log.Fatal(err)
	}

	// 1. Left join gives one row per order; users with no orders appear once with NULLs.
	rows, _ := pool.Query(ctx, `select u.id, u.name, o.order_id, o.cents from u594 u left join o594 o on o.user_id = u.id order by u.id, o.order_id`)
	flat, err := pgx.CollectRows(rows, pgx.RowToStructByName[joinRow])
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("join returned %d rows for 3 users\n", len(flat))

	// 2. Group the flat rows into nested structs in Go.
	var out []*UserWithOrders
	byID := map[int]*UserWithOrders{}
	for _, r := range flat {
		u, ok := byID[r.UserID]
		if !ok {
			u = &UserWithOrders{ID: r.UserID, Name: r.Name, Orders: []Order{}}
			byID[r.UserID] = u
			out = append(out, u)
		}
		if r.OrderID != nil {
			u.Orders = append(u.Orders, Order{ID: *r.OrderID, Cents: *r.Cents})
		}
	}
	b, _ := json.Marshal(out)
	fmt.Println("grouped in Go:   ", string(b))

	// 3. Let Postgres aggregate: one row per user, orders as JSON.
	var viaJSON []UserWithOrders
	rows, _ = pool.Query(ctx, `select u.id, u.name,
		coalesce(json_agg(json_build_object('id', o.order_id, 'cents', o.cents) order by o.order_id) filter (where o.order_id is not null), '[]') as orders
		from u594 u left join o594 o on o.user_id = u.id group by u.id order by u.id`)
	for rows.Next() {
		var u UserWithOrders
		var raw []byte
		if err := rows.Scan(&u.ID, &u.Name, &raw); err != nil {
			log.Fatal(err)
		}
		_ = json.Unmarshal(raw, &u.Orders)
		viaJSON = append(viaJSON, u)
	}
	b, _ = json.Marshal(viaJSON)
	fmt.Println("json_agg version:", string(b))

	// 4. The N+1 shape to avoid: one query per user.
	var queries int
	for _, u := range out {
		var n int
		_ = pool.QueryRow(ctx, `select count(*) from o594 where user_id = $1`, u.ID).Scan(&n)
		queries++
	}
	fmt.Printf("N+1 version issued %d extra queries for %d users\n", queries, len(out))
	_, _ = pool.Exec(ctx, `drop table o594; drop table u594`)
}

go run . 2>&1

join returned 4 rows for 3 users
grouped in Go:    [{"id":1,"name":"ada","orders":[{"id":10,"cents":500},{"id":11,"cents":250}]},{"id":2,"name":"grace","orders":[{"id":12,"cents":900}]},{"id":3,"name":"linus","orders":[]}]
json_agg version: [{"id":1,"name":"ada","orders":[{"id":10,"cents":500},{"id":11,"cents":250}]},{"id":2,"name":"grace","orders":[{"id":12,"cents":900}]},{"id":3,"name":"linus","orders":[]}]
N+1 version issued 3 extra queries for 3 users

Option 1: scan flat, group in Go

select u.id, u.name, o.order_id, o.cents
from u594 u left join o594 o on o.user_id = u.id
order by u.id, o.order_id

The result has four rows for three users: ada twice, grace once, linus once. The program scans each row into a flat joinRow and collects them with pgx.CollectRows. Two details matter.

Left join NULLs need pointers. For linus, order_id and cents are NULL. The fields in joinRow are *int, which are nil for him. Plain int fields would fail the scan, as explained in scanning NULL.

Alias columns that would collide. Both tables have an id; the orders table names its key order_id so the flat struct can map each column by name. Where you cannot rename, alias in the select list.

Then the program groups the rows:

byID := map[int]*UserWithOrders{}
for _, r := range flat {
	u, ok := byID[r.UserID]
	if !ok {
		u = &UserWithOrders{ID: r.UserID, Name: r.Name, Orders: []Order{}}
		byID[r.UserID] = u
		out = append(out, u)
	}
	if r.OrderID != nil {
		u.Orders = append(u.Orders, Order{ID: *r.OrderID, Cents: *r.Cents})
	}
}

A map from parent ID to the struct pointer builds the parent once, and the slice out keeps the order the query returned. A row with a nil OrderID creates the parent and adds no child. The JSON of the result reads:

[{"id":1,"name":"ada","orders":[{"id":10,"cents":500},{"id":11,"cents":250}]},{"id":2,"name":"grace","orders":[{"id":12,"cents":900}]},{"id":3,"name":"linus","orders":[]}]

linus has "orders":[], not null, because the parent was created with an empty slice. APIs and front ends generally prefer an empty array to null; initialise the slice.

Strengths of this approach: one query, plain SQL, works with any driver and any depth by repeating the idea. Weaknesses: the parent's columns are repeated on every row (wasted bytes if the parent is wide), LIMIT applies to joined rows and not parents, and you write the grouping code per shape.

The pagination trap

limit 20 on the joined query returns 20 rows, which might be five users with their orders. To page parents, select the page of parents in a subquery or CTE first and join the children onto it:

with page as (select * from u594 order by id limit 20)
select p.id, p.name, o.order_id, o.cents
from page p left join o594 o on o.user_id = p.id
order by p.id, o.order_id

Now the limit counts users.

Option 2: aggregate in SQL

PostgreSQL can return the children as a JSON array inside the parent row:

select u.id, u.name,
  coalesce(json_agg(json_build_object('id', o.order_id, 'cents', o.cents) order by o.order_id)
           filter (where o.order_id is not null), '[]') as orders
from u594 u left join o594 o on o.user_id = u.id
group by u.id order by u.id

One row per user. The filter clause drops the NULL child that the left join makes for linus, coalesce turns the resulting NULL aggregate into [], and order by inside json_agg fixes the child order. The Go side scans the orders column into a []byte and calls json.Unmarshal into u.Orders. The program's output is the same JSON as option 1:

json_agg version: [{"id":1,"name":"ada","orders":[...]},...]

Strengths: one row per parent, so LIMIT, OFFSET and counts behave; no grouping code in Go; the parent's columns are not repeated. Weaknesses: PostgreSQL-specific (MySQL has JSON_ARRAYAGG, SQLite has json_group_array), the child shape is built in SQL as JSON and decoded in Go, which costs CPU and loses type safety for the child columns (everything passes through JSON), and a very large child set is carried inside one value. Dates and numbers follow JSON rules, so a numeric becomes a JSON number and may lose precision when decoded into float64; decode into json.Number or a decimal type when that matters.

You can scan the JSON column directly into a Go slice type with pgx, which decodes json and jsonb into any type that encoding/json can unmarshal, saving the manual step: rows.Scan(&u.ID, &u.Name, &u.Orders). The lab decodes by hand to keep the types visible.

The one to avoid: N+1

The last block of the program loops over the users and runs one query per user, select count(*) from o594 where user_id = $1. It issued 3 extra queries for 3 users. For a page of 50 users it is 51 round trips, and each is a pool checkout, a network hop and a parse. At 1 ms each that is 50 ms of latency added to a page that could have been one query. It hides well in code that looks tidy: a service method ordersFor(user) called in a loop. The tell in a profile or a log is the same SQL repeated with different parameters.

Fixes, in order of preference: one of the two joins above; a second query with where user_id = any($1) for all the parent IDs, then grouping the children in Go (two queries total, independent of page size); or a cache. The two-query form avoids the repeated parent columns and keeps child scanning typed:

rows, _ := pool.Query(ctx, `select user_id, order_id, cents from o594 where user_id = any($1)`, ids)

Choosing

Deeper trees and several children

Users with orders is one level. Real models have orders with lines, lines with discounts, and users with orders and addresses. Joining two independent one-to-many relations in one query multiplies the rows: a user with 3 orders and 4 addresses returns 12 rows, and grouping them in Go requires de-duplicating both children. Do not join sibling collections together. Use one query per collection, keyed on the parent IDs, and assemble in Go. The query count grows with the number of collections, not with the number of rows, which is the property you want.

For a tree, such as categories with subcategories, PostgreSQL's recursive CTEs return all levels in one query as flat rows with a parent ID, and the Go code builds the tree with a map from ID to node, the same trick as in option 1. Keep the depth bounded in the query (where depth < 10) so a cycle in bad data cannot run forever.

When the nested shape is only for an API response, consider skipping the structs: json_agg on the database and passing the bytes through as json.RawMessage avoids decoding and re-encoding. It makes the SQL responsible for the response shape, which couples the two; that is acceptable for read-only views and a bad idea for anything you also write back.

Testing

Test the shape with the awkward cases (the transaction rollback technique in rollback tests keeps seeding cheap): a parent with no children (the linus case, where a nil or empty slice bug shows), a parent with many children, children out of order, and a page boundary that splits a parent's rows. Seed them in a table and assert the JSON. Name-based struct mapping makes the tests indifferent to column order, as in scanning rows into structs. The PostgreSQL manual documents the aggregate functions and JSON functions used here.