Compare · 006

JSON columns: ClickHouse and CockroachDB

Every file of the ClickHouse and CockroachDB versions side by side, with the lines that differ marked. 8 parameter or column types differ in Go.

Compare ClickHouse withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

ClickHouse
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
- name: orders
- name: orders
  dialect: clickhouse
  dialect: postgres
  migrations: db/migrations
  migrations: db/migrations
  queries: db/queries
  queries: db/queries
  go:
  go:
    package: store
    package: store
    out: internal/store
    out: internal/store
    driver: database/sql
    json_tags:
      case: camel
      omitempty: true
    struct_tags:
      db: "{name}"
    empty_slices: true

Migrations

db/migrations/0001_orders.up.sqldiffers

ClickHouse
CockroachDB
1CREATE TABLE orders (
1CREATE TABLE orders (
2    id       UInt64,
2    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    customer String,
3    customer TEXT   NOT NULL,
4    basket   String,
4    basket   JSONB  NOT NULL,
5    delivery Nullable(String)
5    delivery JSONB
6) ENGINE = MergeTree() ORDER BY id;
6);

db/migrations/0001_orders.down.sqlidentical

ClickHouse
CockroachDB
1DROP TABLE orders;
1DROP TABLE orders;

Queries

PlaceOrderdiffers

ClickHouse · db/queries/orders.sql
CockroachDB · db/queries/orders.sql
1-- sgl.query(PlaceOrder, exec)
1-- sgl.query(PlaceOrder, exec)
2INSERT INTO orders (id, customer, basket, delivery)
2INSERT INTO orders (customer, basket, delivery) VALUES ($1, $2, $3);
3VALUES ({id:UInt64}, {customer:String}, {basket:String}, {delivery:Nullable(String)});

GetOrderdiffers

ClickHouse · db/queries/orders.sql
CockroachDB · db/queries/orders.sql
1-- sgl.query(GetOrder, one)
1-- sgl.query(GetOrder, one)
2SELECT id, customer, basket, delivery FROM orders WHERE id = {id:UInt64};
2SELECT id, customer, basket, delivery FROM orders WHERE id = $1;

OrdersWithFishdiffers

ClickHouse · db/queries/orders.sql
CockroachDB · db/queries/orders.sql
1-- sgl.query(OrdersWithFish, many)
1-- sgl.query(OrdersWithFish, many)
2-- $1 as sgl.param(fish, type: text)
2SELECT
3SELECT
3    id,
4    id,
4    customer,
5    customer,
5    JSONExtractRaw(basket, 'extras') AS extras,
6    basket -> 'extras' AS extras,
6    JSONExtractString(delivery, 'address') AS address
7    delivery ->> 'address' AS delivery_address
7FROM orders
8FROM orders
8WHERE JSONExtractString(basket, 'fish') = {fish:String}
9WHERE basket ->> 'fish' = $1
9ORDER BY id;
10ORDER BY id;

OrdersContainingonly CockroachDB

ClickHouse · not in this version
CockroachDB · db/queries/orders.sql
1-- sgl.query(OrdersContaining, many)
2-- $1 as sgl.param(subset)
3SELECT id, customer
4FROM orders
5WHERE basket @> $1::jsonb
6ORDER BY id;

What sgl generates

PlaceOrderdiffers

ClickHouse
CockroachDB
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) error
1func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) error
2
2
3type PlaceOrderParams struct {
3type PlaceOrderParams struct {
4	ID       uint64
5	Customer string
4	Customer string
6	Basket   string
5	Basket   JSON
7	Delivery *string
6	Delivery JSON
8}
7}
8
9type JSON []byte

GetOrderdiffers

ClickHouse
CockroachDB
1func (queries *Queries) GetOrder(ctx context.Context, id uint64) (GetOrderRow, error)
1func (queries *Queries) GetOrder(ctx context.Context, id int64) (GetOrderRow, error)
2
2
3type GetOrderRow struct {
3type GetOrderRow struct {
4	ID       uint64  `json:"id"`
4	ID       int64  `json:"id,omitempty" db:"id"`
5	Customer string  `json:"customer"`
5	Customer string `json:"customer,omitempty" db:"customer"`
6	Basket   string  `json:"basket"`
6	Basket   JSON   `json:"basket,omitempty" db:"basket"`
7	Delivery *string `json:"delivery"`
7	Delivery JSON   `json:"delivery,omitempty" db:"delivery"`
8}
8}
9
10type JSON []byte

OrdersWithFishdiffers

ClickHouse
CockroachDB
1func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error)
1func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error)
2
2
3type OrdersWithFishRow struct {
3type OrdersWithFishRow struct {
4	ID       uint64  `json:"id"`
4	ID              int64   `json:"id,omitempty" db:"id"`
5	Customer string  `json:"customer"`
5	Customer        string  `json:"customer,omitempty" db:"customer"`
6	Extras   string  `json:"extras"`
6	Extras          JSON    `json:"extras,omitempty" db:"extras"`
7	Address  *string `json:"address"`
7	DeliveryAddress *string `json:"deliveryAddress,omitempty" db:"delivery_address"`
8}
8}
9
10type JSON []byte

OrdersContainingonly CockroachDB

ClickHouse · not in this version
CockroachDB
1func (queries *Queries) OrdersContaining(ctx context.Context, subset JSON) ([]OrdersContainingRow, error)
2
3type JSON []byte
4
5type OrdersContainingRow struct {
6	ID       int64  `json:"id,omitempty" db:"id"`
7	Customer string `json:"customer,omitempty" db:"customer"`
8}

The program

example.godiffers

ClickHouse
CockroachDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"encoding/json"
6	"encoding/json"
7	"fmt"
7	"fmt"
8	"io"
8	"io"
9	"reflect"
10	"strings"
9
11
10	"seagull.sh/seagull/examples/clickhouse/006_json/internal/store"
12	"seagull.sh/seagull/examples/postgres/006_json/internal/store"
11)
13)
12
14
13// basket is the document an order's basket column holds.
15// basket is the document an order's basket column holds.
14type basket struct {
16type basket struct {
15	// Fish is the fish the order is for.
17	// Fish is the fish the order is for.
16	Fish string `json:"fish"`
18	Fish string `json:"fish"`
17
19
18	// Portions is how many portions of fish and chips.
20	// Portions is how many portions of fish and chips.
19	Portions int `json:"portions"`
21	Portions int `json:"portions"`
20
22
21	// Extras lists the sides.
23	// Extras lists the sides.
22	Extras []string `json:"extras"`
24	Extras []string `json:"extras"`
23}
25}
24
26
25// delivery is the document an order's delivery column holds, when the order is delivered.
27// delivery is the document an order's delivery column holds, when the order is delivered.
26type delivery struct {
28type delivery struct {
27	// Address is where the order goes.
29	// Address is where the order goes.
28	Address string `json:"address"`
30	Address string `json:"address"`
29}
31}
30
32
31// run places three chip shop orders and reads them back. The documents live in String
33// run places three chip shop orders and reads them back. A JSONB column becomes the
32// columns, the usual home for JSON text in ClickHouse, so Basket is a string and
34// generated store.JSON type, which holds the raw document as bytes: encode a value into
33// Delivery, a Nullable(String), is *string: encode a value into it with json.Marshal and
35// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
34// decode it with json.Unmarshal. A nil Delivery is SQL NULL.
35//
36//
36// OrdersWithFish reads inside the documents with ClickHouse's JSON functions instead of
37// JSONB stores a parsed document, not the text it was sent, and writes it back in its own
37// operators: JSONExtractString(basket, 'fish') extracts a value as text, and
38// form: a space after each comma and colon, keys reordered and duplicate keys dropped, so
38// JSONExtractRaw(basket, 'extras') extracts the fragment's JSON text. Both return a
39// compare documents by decoding them, not by their bytes.
39// String, which is empty for a missing key rather than NULL, so Extras is a string.
40// Address is *string because the delivery column is Nullable(String), and a JSON function
41// given NULL returns NULL.
42//
40//
41// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
42// which extracts a JSONB fragment, so Extras is store.JSON again. DeliveryAddress is
43// *string because ->> gives NULL when the delivery column or its address is missing. The
44// ->> comparison's parameter is pinned with sgl.param(fish, type: text), as inference
45// leaves it as any. OrdersContaining uses @>, which matches a document containing the one
46// bound to $1::jsonb.
47//
48// The rows go straight out as JSON, as an HTTP handler would send them, so sgl.yaml
49// shapes the generated structs for that: go.json_tags writes each field's json tag in
50// camel case with omitempty, so delivery_address is "deliveryAddress" and a nil one is
51// left out; go.struct_tags adds a db tag holding the column's name, {name}, for libraries
52// that map columns by tag; and go.empty_slices makes a many method return an empty slice
53// when no row matches, which encodes as [] rather than null.
54//
43// Takes database (*sql.DB) which is a migrated database.
55// Takes database (*sql.DB) which is a migrated database.
44// Takes output (io.Writer) which receives the decoded orders and the search.
56// Takes output (io.Writer) which receives the decoded orders and the searches.
45//
57//
46// Returns error when a query fails or a document does not encode or decode.
58// Returns error when a query fails or a document does not encode or decode.
47func run(ctx context.Context, database *sql.DB, output io.Writer) error {
59func run(ctx context.Context, database *sql.DB, output io.Writer) error {
48	queries := store.New(database)
60	queries := store.New(database)
49
61
50	orders := []struct {
62	orders := []struct {
51		customer string
63		customer string
52		basket   basket
64		basket   basket
53		delivery *delivery
65		delivery *delivery
54	}{
66	}{
55		{customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
67		{customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
56		{customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
68		{customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
57		{customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
69		{customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
58	}
70	}
59	for index, order := range orders {
71	for _, order := range orders {
60		encodedBasket, err := json.Marshal(order.basket)
72		encodedBasket, err := json.Marshal(order.basket)
61		if err != nil {
73		if err != nil {
62			return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
74			return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
63		}
75		}
64		parameters := store.PlaceOrderParams{ID: uint64(index + 1), Customer: order.customer, Basket: string(encodedBasket), Delivery: nil}
76		parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
65		if order.delivery != nil {
77		if order.delivery != nil {
66			encodedDelivery, err := json.Marshal(order.delivery)
78			encodedDelivery, err := json.Marshal(order.delivery)
67			if err != nil {
79			if err != nil {
68				return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
80				return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
69			}
81			}
70			parameters.Delivery = new(string(encodedDelivery))
82			parameters.Delivery = encodedDelivery
71		}
83		}
72		if err := queries.PlaceOrder(ctx, parameters); err != nil {
84		if err := queries.PlaceOrder(ctx, parameters); err != nil {
73			return fmt.Errorf("placing %s's order: %w", order.customer, err)
85			return fmt.Errorf("placing %s's order: %w", order.customer, err)
74		}
86		}
75	}
87	}
76
88
77	first, err := queries.GetOrder(ctx, 1)
89	first, err := queries.GetOrder(ctx, 1)
78	if err != nil {
90	if err != nil {
79		return fmt.Errorf("getting order 1: %w", err)
91		return fmt.Errorf("getting order 1: %w", err)
80	}
92	}
81	var decoded basket
93	var decoded basket
82	if err := json.Unmarshal([]byte(first.Basket), &decoded); err != nil {
94	if err := json.Unmarshal(first.Basket, &decoded); err != nil {
83		return fmt.Errorf("decoding order 1's basket: %w", err)
95		return fmt.Errorf("decoding order 1's basket: %w", err)
84	}
96	}
85	fmt.Fprintf(output, "order 1 stored %s\n", first.Basket)
97	fmt.Fprintf(output, "order 1 decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
86	fmt.Fprintf(output, "decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
87
98
88	cod, err := queries.OrdersWithFish(ctx, "cod")
99	cod, err := queries.OrdersWithFish(ctx, "cod")
89	if err != nil {
100	if err != nil {
90		return fmt.Errorf("finding cod orders: %w", err)
101		return fmt.Errorf("finding cod orders: %w", err)
91	}
102	}
92	fmt.Fprintln(output, "\ncod orders:")
103	fmt.Fprintln(output, "\ncod orders:")
93	for _, order := range cod {
104	for _, order := range cod {
94		address := "collecting"
105		address := "collecting"
95		if order.Address != nil {
106		if order.DeliveryAddress != nil {
96			address = "to " + *order.Address
107			address = "to " + *order.DeliveryAddress
97		}
108		}
98		fmt.Fprintf(output, "  order %d  %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
109		fmt.Fprintf(output, "  order %d  %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
99	}
110	}
111	if err := writeJSON(output, "cod orders as JSON", cod); err != nil {
112		return err
113	}
114
115	plaice, err := queries.OrdersWithFish(ctx, "plaice")
116	if err != nil {
117		return fmt.Errorf("finding plaice orders: %w", err)
118	}
119	if err := writeJSON(output, "plaice orders as JSON", plaice); err != nil {
120		return err
121	}
122	field, _ := reflect.TypeFor[store.OrdersWithFishRow]().FieldByName("DeliveryAddress")
123	fmt.Fprintf(output, "DeliveryAddress is tagged %s\n", field.Tag)
124
125	subset := store.JSON(`{"extras": ["pickled egg"]}`)
126	containing, err := queries.OrdersContaining(ctx, subset)
127	if err != nil {
128		return fmt.Errorf("finding orders containing %s: %w", subset, err)
129	}
130	customers := make([]string, 0, len(containing))
131	for _, order := range containing {
132		customers = append(customers, order.Customer)
133	}
134	fmt.Fprintf(output, "\norders containing %s: %s\n", subset, strings.Join(customers, ", "))
100	return nil
135	return nil
101}
136}
137
138// writeJSON writes rows encoded as JSON, as an HTTP handler would send them, after a
139// label.
140//
141// Takes output (io.Writer) which receives the label and the document.
142// Takes label (string) which says what the rows are.
143// Takes rows (any) which are the rows to encode.
144//
145// Returns error when the rows do not encode.
146func writeJSON(output io.Writer, label string, rows any) error {
147	encoded, err := json.Marshal(rows)
148	if err != nil {
149		return fmt.Errorf("encoding %s: %w", label, err)
150	}
151	fmt.Fprintf(output, "%s: %s\n", label, encoded)
152	return nil
153}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	ClickHouse	CockroachDB
PlaceOrder	parameter `basket`	`string`	`JSON`
PlaceOrder	parameter `delivery`	`*string`	`JSON`
GetOrder	parameter `id`	`uint64`	`int64`
GetOrder	column `id`	`uint64`	`int64`
GetOrder	column `basket`	`string`	`JSON`
GetOrder	column `delivery`	`*string`	`JSON`
OrdersWithFish	column `id`	`uint64`	`int64`
OrdersWithFish	column `extras`	`string`	`JSON`