JSON Columns in Go With Postgres JSONB Explained Well

PostgreSQL's jsonb type lets a relational table hold documents. It is the right tool for data whose shape varies or is owned by someone else: user preferences, webhook payloads, feature flags, form answers. It is the wrong tool for data you query by the same fields on every request, which belongs in columns. This article covers the Go side with pgx on PostgreSQL 18, Go 1.25.6: writing, reading, querying, updating and indexing, with the behaviours that surprise people.

json versus jsonb

PostgreSQL has two JSON types. json stores the text exactly as given, whitespace and key order included, and re-parses it on every use. jsonb stores a decomposed binary form: slower to write, much faster to query, and indexable. Almost always you want jsonb. Use json only when you must preserve the original text byte for byte, such as an audit record of a webhook body.

Writing and reading

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 Prefs struct {
	Theme  string   `json:"theme"`
	Langs  []string `json:"langs"`
	Notify bool     `json:"notify"`
}

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 p595; create table p595 (id int primary key, prefs jsonb not null);
		create index p595_prefs on p595 using gin (prefs jsonb_path_ops)`)
	if err != nil {
		log.Fatal(err)
	}

	// Write a struct: pgx marshals it to JSON for a jsonb parameter.
	_, err = pool.Exec(ctx, `insert into p595 values ($1, $2), ($3, $4)`,
		1, Prefs{"dark", []string{"go", "sql"}, true},
		2, Prefs{"light", []string{"go"}, false})
	fmt.Println("insert:", err)

	// Read back into a struct.
	var p Prefs
	err = pool.QueryRow(ctx, `select prefs from p595 where id = 1`).Scan(&p)
	fmt.Printf("scan into struct: %+v err=%v\n", p, err)

	// Query inside the document.
	var theme string
	_ = pool.QueryRow(ctx, `select prefs->>'theme' from p595 where id = 2`).Scan(&theme)
	fmt.Println("prefs->>'theme':", theme)
	rows, _ := pool.Query(ctx, `select id from p595 where prefs @> '{"langs": ["sql"]}' order by id`)
	ids, _ := pgx.CollectRows(rows, pgx.RowTo[int])
	fmt.Println("containment @> {langs:[sql]}:", ids)

	// Update one key without rewriting the client side struct.
	_, _ = pool.Exec(ctx, `update p595 set prefs = jsonb_set(prefs, '{notify}', 'true') where id = 2`)
	var raw []byte
	_ = pool.QueryRow(ctx, `select prefs from p595 where id = 2`).Scan(&raw)
	fmt.Println("after jsonb_set:", string(raw))

	// jsonb normalises: key order and whitespace are not kept, and duplicate keys collapse.
	var normalised string
	_ = pool.QueryRow(ctx, `select '{"b": 1,   "a": 2, "a": 3}'::jsonb::text`).Scan(&normalised)
	fmt.Println("jsonb normalised:", normalised)

	// Unknown shape: scan into map[string]any.
	var m map[string]any
	_ = pool.QueryRow(ctx, `select prefs from p595 where id = 1`).Scan(&m)
	b, _ := json.Marshal(m)
	fmt.Println("as map:", string(b))

	// The planner uses the GIN index for @> on a bigger table.
	_, _ = pool.Exec(ctx, `insert into p595 select g, jsonb_build_object('theme', 'x', 'langs', jsonb_build_array('l' || g)) from generate_series(3, 20000) g`)
	_, _ = pool.Exec(ctx, `analyze p595`)
	var plan string
	rows2, _ := pool.Query(ctx, `explain select id from p595 where prefs @> '{"langs": ["l777"]}'`)
	defer rows2.Close()
	for rows2.Next() {
		var line string
		_ = rows2.Scan(&line)
		if plan == "" {
			plan = line
		}
	}
	fmt.Println("plan:", plan[:min(len(plan), 60)])
	_, _ = pool.Exec(ctx, `drop table p595`)
}

go run . 2>&1

insert: <nil>
scan into struct: {Theme:dark Langs:[go sql] Notify:true} err=<nil>
prefs->>'theme': light
containment @> {langs:[sql]}: [1]
after jsonb_set: {"langs": ["go"], "theme": "light", "notify": true}
jsonb normalised: {"a": 3, "b": 1}
as map: {"langs":["go","sql"],"notify":true,"theme":"dark"}
plan: Bitmap Heap Scan on p595  (cost=429.30..436.73 rows=2 width=

The table is p595 (id int primary key, prefs jsonb not null), with a GIN index. The Go type is a struct with JSON tags:

type Prefs struct {
	Theme  string   `json:"theme"`
	Langs  []string `json:"langs"`
	Notify bool     `json:"notify"`
}

Writing a struct. pgx sees a parameter whose target type is jsonb and encodes the Go value with encoding/json. The call pool.Exec(ctx, "insert into p595 values ($1, $2)", 1, Prefs{...}) needs no manual json.Marshal. The program inserts two documents and prints insert: <nil>.

Reading into a struct. pool.QueryRow(...).Scan(&p) decodes the document into p. The output is scan into struct: {Theme:dark Langs:[go sql] Notify:true} err=<nil>. For unknown shapes, scan into map[string]any and the program prints the keys sorted, as encoding/json does for maps: {"langs":["go","sql"],"notify":true,"theme":"dark"}. For raw bytes, scan into []byte or json.RawMessage, which is the cheap path when you pass the document straight to an HTTP response.

Two things to know. An unknown field in the stored document is ignored by encoding/json unless you decode with DisallowUnknownFields, so a schema drift goes unnoticed. And a field that is null in the document becomes the zero value in the struct, which loses the difference between missing and null; use pointers or json.RawMessage for fields where it matters, and see scanning NULL for the SQL-side equivalent.

Querying inside documents

The operators are the reason to use jsonb.

The program reads one field with prefs->>'theme' and gets light for the second row, and filters with containment: where prefs @> '{"langs": ["sql"]}' returns [1], the row whose nested array holds sql. Containment is the operator the GIN index accelerates, and it is the one to prefer for filters.

Pass the containment document as a parameter, not by concatenation: where prefs @> $1 with a Go struct or map. In pgx, the parameter is typed jsonb by the server's inference of the operator, and the encoder marshals the Go value.

Updating without rewriting

Reading, changing and writing the whole document in Go risks a lost update when two requests do it at once. jsonb_set changes one key in the database in one statement:

update p595 set prefs = jsonb_set(prefs, '{notify}', 'true') where id = 2

After this, the stored document reads {"langs": ["go"], "theme": "light", "notify": true}. Related functions: jsonb_set_lax for NULL handling, || to merge two documents (prefs || '{"theme":"dark"}'), and - to delete a key (prefs - 'theme'). Prefer these to a Go round trip; they take the row lock for the duration of the statement only.

What jsonb does to your document

jsonb does not store what you sent. The program casts '{"b": 1, "a": 2, "a": 3}'::jsonb::text and gets {"a": 3, "b": 1}. Three changes: whitespace is gone, keys are sorted (by length then bytes, which for short keys looks alphabetical), and the duplicate key a kept its last value. Arrays keep their order. Numbers keep their precision as numeric. This matters if you hash or sign documents (hash the canonical form from the database, not your original), and if you compare text output of two documents.

Indexing

For @>, ?, ?| and ?&, a GIN index helps: create index on p595 using gin (prefs jsonb_path_ops). The jsonb_path_ops operator class is smaller and faster for containment, and does not support the key-existence operators; the default jsonb_ops supports them all. The program inserts 20000 more rows, runs analyze, and asks the planner about where prefs @> '{"langs": ["l777"]}'. The first line of the plan is a Bitmap Heap Scan on p595, driven by the GIN index.

For a single field you filter or sort on often, an expression index is better: create index on p595 ((prefs->>'theme')). And when a field is queried by every request, promote it to a real column; a generated column keeps the document and the column in step: theme text generated always as (prefs->>'theme') stored.

When not to use jsonb

• A fixed set of fields queried everywhere: use columns, with types, constraints and foreign keys. The database can enforce what a JSON document cannot.
• Relationships: a document with an array of IDs cannot have foreign keys. Use a join table.
• Large documents updated often: PostgreSQL rewrites the whole value on each update; a 500 KB document with a one-key change writes 500 KB.
• Needs for statistics: the planner has little information about the inside of documents, so estimates for predicates on JSON fields are rough.

A pattern that works: columns for what you query and constrain, one jsonb column for the rest. Document the expected shape and validate it, in Go with a struct, or in the database with a check constraint built from jsonb_typeof and the key-existence operators, for the few fields that must be present.

Testing

Test with the real database, since the operator semantics are the thing under test. Insert a few documents, run each query, assert the ids returned, including documents with the key missing, null and of the wrong type. Include a test for jsonb_set that two concurrent updates to different keys both land. See PostgreSQL tests for the container setup. The PostgreSQL manual has the full list of JSON types and operators and the GIN index chapter, and the Go side of scanning is in the struct scanning article.