Compare · 006

JSON columns: ClickHouse and MariaDB

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: orders
4 - name: orders
5 dialect: clickhouse
5 dialect: mariadb
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store

Migrations

db/migrations/0001_orders.up.sqldiffers
ClickHouse
MariaDB
1CREATE TABLE orders (
1CREATE TABLE orders (
2 id UInt64,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 customer String,
3 customer VARCHAR(50) NOT NULL,
4 basket String,
4 basket JSON NOT NULL,
5 delivery Nullable(String)
5 delivery JSON
6) ENGINE = MergeTree() ORDER BY id;
6);
db/migrations/0001_orders.down.sqlidentical
ClickHouse
MariaDB
1DROP TABLE orders;
1DROP TABLE orders;

Queries

PlaceOrderdiffers
ClickHouse · db/queries/orders.sql
MariaDB · 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 (?, ?, ?);
3VALUES ({id:UInt64}, {customer:String}, {basket:String}, {delivery:Nullable(String)});
GetOrderdiffers
ClickHouse · db/queries/orders.sql
MariaDB · 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 = ?;
OrdersWithFishdiffers
ClickHouse · db/queries/orders.sql
MariaDB · db/queries/orders.sql
1-- sgl.query(OrdersWithFish, many)
1-- sgl.query(OrdersWithFish, many)
2-- ?1 as sgl.param(fish)
2SELECT
3SELECT
3 id,
4 id,
4 customer,
5 customer,
5 JSONExtractRaw(basket, 'extras') AS extras,
6 JSON_EXTRACT(basket, '$.extras') AS extras,
6 JSONExtractString(delivery, 'address') AS address
7 JSON_UNQUOTE(JSON_EXTRACT(delivery, '$.address')) AS address
7FROM orders
8FROM orders
8WHERE JSONExtractString(basket, 'fish') = {fish:String}
9WHERE JSON_UNQUOTE(JSON_EXTRACT(basket, '$.fish')) = ?1
9ORDER BY id;
10ORDER BY id;

What sgl generates

PlaceOrderdiffers
ClickHouse
MariaDB
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
MariaDB
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"`
5 Customer string `json:"customer"`
5 Customer string `json:"customer"`
6 Basket string `json:"basket"`
6 Basket JSON `json:"basket"`
7 Delivery *string `json:"delivery"`
7 Delivery JSON `json:"delivery"`
8}
8}
9
10type JSON []byte
OrdersWithFishdiffers
ClickHouse
MariaDB
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"`
5 Customer string `json:"customer"`
5 Customer string `json:"customer"`
6 Extras string `json:"extras"`
6 Extras JSON `json:"extras"`
7 Address *string `json:"address"`
7 Address *string `json:"address"`
8}
8}
9
10type JSON []byte

The program

example.godiffers
ClickHouse
MariaDB
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
9
10 "seagull.sh/seagull/examples/clickhouse/006_json/internal/store"
10 "seagull.sh/seagull/examples/mariadb/006_json/internal/store"
11)
11)
12
12
13// basket is the document an order's basket column holds.
13// basket is the document an order's basket column holds.
14type basket struct {
14type basket struct {
15 // Fish is the fish the order is for.
15 // Fish is the fish the order is for.
16 Fish string `json:"fish"`
16 Fish string `json:"fish"`
17
17
18 // Portions is how many portions of fish and chips.
18 // Portions is how many portions of fish and chips.
19 Portions int `json:"portions"`
19 Portions int `json:"portions"`
20
20
21 // Extras lists the sides.
21 // Extras lists the sides.
22 Extras []string `json:"extras"`
22 Extras []string `json:"extras"`
23}
23}
24
24
25// delivery is the document an order's delivery column holds, when the order is delivered.
25// delivery is the document an order's delivery column holds, when the order is delivered.
26type delivery struct {
26type delivery struct {
27 // Address is where the order goes.
27 // Address is where the order goes.
28 Address string `json:"address"`
28 Address string `json:"address"`
29}
29}
30
30
31// run places three chip shop orders and reads them back. The documents live in String
31// run places three chip shop orders and reads them back. A JSON column becomes the
32// columns, the usual home for JSON text in ClickHouse, so Basket is a string and
32// 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
33// 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//
34//
36// OrdersWithFish reads inside the documents with ClickHouse's JSON functions instead of
35// In MariaDB, JSON is another name for LONGTEXT with a JSON_VALID check. An invalid
37// operators: JSONExtractString(basket, 'fish') extracts a value as text, and
36// document is refused, and a valid one is kept as the text it was written as, so it comes
38// JSONExtractRaw(basket, 'extras') extracts the fragment's JSON text. Both return a
37// back byte for byte: here, in the field order and compact form json.Marshal gave it.
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//
38//
39// MariaDB has no -> or ->> operators, and sgl vet reports Q010 for them, naming the
40// function to use instead. OrdersWithFish calls those functions. JSON_EXTRACT returns a
41// JSON fragment, so Extras is store.JSON again, written in MariaDB's own style with a
42// space after each comma. JSON_UNQUOTE(JSON_EXTRACT(...)) returns SQL text, so Address is
43// *string, NULL when the delivery column or its address is missing. The fish parameter is
44// compared with that text, so it is inferred as a string, and its binding line only gives
45// it a name in place of p1. JSON_VALUE(basket, '$.fish') is a shorter spelling for a
46// single value.
47//
43// Takes database (*sql.DB) which is a migrated database.
48// Takes database (*sql.DB) which is a migrated database.
44// Takes output (io.Writer) which receives the decoded orders and the search.
49// Takes output (io.Writer) which receives the decoded orders and the search.
45//
50//
46// Returns error when a query fails or a document does not encode or decode.
51// 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 {
52func run(ctx context.Context, database *sql.DB, output io.Writer) error {
48 queries := store.New(database)
53 queries := store.New(database)
49
54
50 orders := []struct {
55 orders := []struct {
51 customer string
56 customer string
52 basket basket
57 basket basket
53 delivery *delivery
58 delivery *delivery
54 }{
59 }{
55 {customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
60 {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{}}},
61 {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"}},
62 {customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
58 }
63 }
59 for index, order := range orders {
64 for _, order := range orders {
60 encodedBasket, err := json.Marshal(order.basket)
65 encodedBasket, err := json.Marshal(order.basket)
61 if err != nil {
66 if err != nil {
62 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
67 return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
63 }
68 }
64 parameters := store.PlaceOrderParams{ID: uint64(index + 1), Customer: order.customer, Basket: string(encodedBasket), Delivery: nil}
69 parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
65 if order.delivery != nil {
70 if order.delivery != nil {
66 encodedDelivery, err := json.Marshal(order.delivery)
71 encodedDelivery, err := json.Marshal(order.delivery)
67 if err != nil {
72 if err != nil {
68 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
73 return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
69 }
74 }
70 parameters.Delivery = new(string(encodedDelivery))
75 parameters.Delivery = encodedDelivery
71 }
76 }
72 if err := queries.PlaceOrder(ctx, parameters); err != nil {
77 if err := queries.PlaceOrder(ctx, parameters); err != nil {
73 return fmt.Errorf("placing %s's order: %w", order.customer, err)
78 return fmt.Errorf("placing %s's order: %w", order.customer, err)
74 }
79 }
75 }
80 }
76
81
77 first, err := queries.GetOrder(ctx, 1)
82 first, err := queries.GetOrder(ctx, 1)
78 if err != nil {
83 if err != nil {
79 return fmt.Errorf("getting order 1: %w", err)
84 return fmt.Errorf("getting order 1: %w", err)
80 }
85 }
81 var decoded basket
86 var decoded basket
82 if err := json.Unmarshal([]byte(first.Basket), &decoded); err != nil {
87 if err := json.Unmarshal(first.Basket, &decoded); err != nil {
83 return fmt.Errorf("decoding order 1's basket: %w", err)
88 return fmt.Errorf("decoding order 1's basket: %w", err)
84 }
89 }
85 fmt.Fprintf(output, "order 1 stored %s\n", first.Basket)
90 fmt.Fprintf(output, "order 1 stored %s\n", first.Basket)
86 fmt.Fprintf(output, "decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
91 fmt.Fprintf(output, "decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)
87
92
88 cod, err := queries.OrdersWithFish(ctx, "cod")
93 cod, err := queries.OrdersWithFish(ctx, "cod")
89 if err != nil {
94 if err != nil {
90 return fmt.Errorf("finding cod orders: %w", err)
95 return fmt.Errorf("finding cod orders: %w", err)
91 }
96 }
92 fmt.Fprintln(output, "\ncod orders:")
97 fmt.Fprintln(output, "\ncod orders:")
93 for _, order := range cod {
98 for _, order := range cod {
94 address := "collecting"
99 address := "collecting"
95 if order.Address != nil {
100 if order.Address != nil {
96 address = "to " + *order.Address
101 address = "to " + *order.Address
97 }
102 }
98 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
103 fmt.Fprintf(output, " order %d %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
99 }
104 }
100 return nil
105 return nil
101}
106}

Go type differences

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

QueryFieldClickHouseMariaDB
PlaceOrderparameter basketstringJSON
PlaceOrderparameter delivery*stringJSON
GetOrderparameter iduint64int64
GetOrdercolumn iduint64int64
GetOrdercolumn basketstringJSON
GetOrdercolumn delivery*stringJSON
OrdersWithFishcolumn iduint64int64
OrdersWithFishcolumn extrasstringJSON