tessl/golang-github-com-jackc-pgx-v5
pgx is a pure Go driver and toolkit for PostgreSQL providing a native high-performance interface with PostgreSQL-specific features plus a database/sql compatibility adapter.
json.mddocs/types/
JSON Types
JSON and JSONB support for PostgreSQL.
Import
import "github.com/jackc/pgx/v5/pgtype"Overview
pgx automatically encodes and decodes Go structs, maps, and slices to/from JSON and JSONB columns using encoding/json by default.
Basic Usage
Query JSON/JSONB into Struct
type Metadata struct {
Author string `json:"author"`
Tags []string `json:"tags"`
Version int `json:"version"`
}
var meta Metadata
err := conn.QueryRow(ctx,
"SELECT metadata FROM documents WHERE id = $1", id).Scan(&meta)Insert JSON/JSONB from Struct
meta := Metadata{
Author: "Alice",
Tags: []string{"draft", "review"},
Version: 1,
}
_, err := conn.Exec(ctx,
"INSERT INTO documents (title, metadata) VALUES ($1, $2)",
"My Document", meta)Query into map[string]any
var data map[string]any
err := conn.QueryRow(ctx,
"SELECT config FROM settings WHERE key = $1", key).Scan(&data)Query into []any
var items []any
err := conn.QueryRow(ctx,
"SELECT items FROM collections WHERE id = $1", id).Scan(&items)JSONCodec and JSONBCodec
// JSONCodec — customizable JSON marshaler/unmarshaler (uses encoding/json by default)
type JSONCodec struct {
Marshal func(v any) ([]byte, error)
Unmarshal func(data []byte, v any) error
}
// JSONBCodec — customizable JSONB marshaler/unmarshaler (uses encoding/json by default)
type JSONBCodec struct {
Marshal func(v any) ([]byte, error)
Unmarshal func(data []byte, v any) error
}Custom JSON Marshaler
To use a custom JSON library (e.g., github.com/goccy/go-json):
import gojson "github.com/goccy/go-json"
config, _ := pgx.ParseConfig(connString)
m := config.TypeMap()
// Replace JSON codec
jsonType, _ := m.TypeForName("json")
jsonType.Codec = &pgtype.JSONCodec{
Marshal: gojson.Marshal,
Unmarshal: gojson.Unmarshal,
}
// Replace JSONB codec
jsonbType, _ := m.TypeForName("jsonb")
jsonbType.Codec = &pgtype.JSONBCodec{
Marshal: gojson.Marshal,
Unmarshal: gojson.Unmarshal,
}
conn, err := pgx.ConnectConfig(ctx, config)Handling NULL JSON
Use pointers for nullable JSON fields:
type Document struct {
ID int64
Metadata *Metadata // nil if NULL
}
var doc Document
err := conn.QueryRow(ctx,
"SELECT id, metadata FROM documents WHERE id = $1", id).Scan(&doc.ID, &doc.Metadata)
if doc.Metadata != nil {
fmt.Println("Author:", doc.Metadata.Author)
} else {
fmt.Println("No metadata")
}JSON vs JSONB
- JSON: Stores exact text representation, preserves whitespace and key order
- JSONB: Binary format, faster queries, supports indexing, strips whitespace
Use JSONB unless you need to preserve exact formatting.
Querying JSON Fields
// Query nested JSON field
var author string
err := conn.QueryRow(ctx,
"SELECT metadata->>'author' FROM documents WHERE id = $1", id).Scan(&author)
// Query with JSON path
var tags []string
err = conn.QueryRow(ctx,
"SELECT metadata->'tags' FROM documents WHERE id = $1", id).Scan(&tags)
// Query with jsonb_path_query
type Item struct {
Name string `json:"name"`
Price float64 `json:"price"`
}
rows, err := conn.Query(ctx,
"SELECT jsonb_path_query(data, '$.items[*]') FROM products")
items, err := pgx.CollectRows(rows, pgx.RowTo[Item])Raw JSON Bytes
To work with raw JSON bytes without parsing:
var jsonBytes []byte
err := conn.QueryRow(ctx,
"SELECT metadata FROM documents WHERE id = $1", id).Scan(&jsonBytes)
// jsonBytes contains the raw JSONJSON Arrays
type Tag struct {
Name string `json:"name"`
Color string `json:"color"`
}
var tags []Tag
err := conn.QueryRow(ctx,
"SELECT tags FROM articles WHERE id = $1", id).Scan(&tags)Performance Notes
- JSONB is faster for queries but slightly slower for inserts
- Use GIN indexes on JSONB columns for better query performance
- Consider using
jsonb_path_opsoperator class for specific path queries - For very large JSON documents, consider splitting into separate tables
Install with Tessl CLI
npx tessl i tessl/golang-github-com-jackc-pgx-v5@5.8.0