PostgreSQL与MySQL的JSON类型及Go语言处理

1
2
3
4
5
6
7

CREATE TABLE events (
    id      BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);
INSERT INTO events (payload) VALUES
    ('{"user": "alice", "tags": ["go", "db"], "score": 42}');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- -> 取子对象（返回 jsonb），->> 取文本（返回 text）
SELECT payload -> 'user'    FROM events;   -- "alice"（带引号，jsonb）
SELECT payload ->> 'user'   FROM events;   -- alice（纯文本）
-- 路径取值 #> / #>>
SELECT payload #>> '{tags, 0}' FROM events;  -- go
-- 包含判断 @>（jsonb 专属，配合 GIN 索引极快）
SELECT * FROM events WHERE payload @> '{"user": "alice"}';
-- 键是否存在 ?（注意：? 只作用于「顶层」键）
SELECT * FROM events WHERE payload ? 'score';
-- 判断嵌套键，要先下钻到那一层再 ?
SELECT * FROM events WHERE payload -> 'dimensions' ? 'weight';
-- 任一键存在 ?| / 所有键存在 ?&
SELECT * FROM events WHERE payload ?| array['score', 'level'];
-- 用 JSON 内的值做过滤：->> 取出的是 text，需显式类型转换
SELECT * FROM events WHERE (payload ->> 'score')::numeric < 100;

1
2
3
4
5
6
7
8

-- 通用 GIN 索引，支持 @> ? ?| ?& 等
CREATE INDEX idx_payload ON events USING GIN (payload);
-- jsonb_path_ops：更小更快，但只支持 @>
CREATE INDEX idx_payload_path ON events USING GIN (payload jsonb_path_ops);
-- 针对单个字段的表达式 B-Tree 索引
CREATE INDEX idx_user ON events ((payload ->> 'user'));

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 更新某个键（jsonb_set）
UPDATE events SET payload = jsonb_set(payload, '{score}', '100') WHERE id = 1;
-- 删除键
UPDATE events SET payload = payload - 'tags' WHERE id = 1;
-- 合并两个 jsonb（|| 后者覆盖前者）
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb;   -- {"a":1,"b":2}
-- 展开数组元素
SELECT jsonb_array_elements_text(payload -> 'tags') FROM events;
-- JSONPath（PG12+）
SELECT jsonb_path_query(payload, '$.tags[*] ? (@ == "go")') FROM events;

1
2
3
4
5
6
7

CREATE TABLE events (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL
);
INSERT INTO events (payload) VALUES
    ('{"user": "alice", "tags": ["go", "db"], "score": 42}');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 取值：JSON_EXTRACT 或 -> 语法糖
SELECT JSON_EXTRACT(payload, '$.user')  FROM events;  -- "alice"
SELECT payload -> '$.user'              FROM events;  -- "alice"
SELECT payload ->> '$.user'             FROM events;  -- alice（去引号，等价 JSON_UNQUOTE）
-- 数组元素
SELECT payload ->> '$.tags[0]' FROM events;  -- go
-- 是否包含
SELECT * FROM events WHERE JSON_CONTAINS(payload, '"alice"', '$.user');
-- 路径是否存在
SELECT * FROM events WHERE JSON_CONTAINS_PATH(payload, 'one', '$.score');
-- 键集合 / 长度
SELECT JSON_KEYS(payload), JSON_LENGTH(payload -> '$.tags') FROM events;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 设置（存在则改，不存在则加）
UPDATE events SET payload = JSON_SET(payload, '$.score', 100) WHERE id = 1;
-- 仅新增（已存在不动） / 仅替换（不存在不加）
UPDATE events SET payload = JSON_INSERT(payload,  '$.level', 5);
UPDATE events SET payload = JSON_REPLACE(payload, '$.score', 0);
-- 删除
UPDATE events SET payload = JSON_REMOVE(payload, '$.tags');
-- 数组追加
UPDATE events SET payload = JSON_ARRAY_APPEND(payload, '$.tags', 'sql');
-- 合并（保留两边所有值）
SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}');  -- {"a":[1,2]}
SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":2}');     -- {"a":2}（RFC 7396）

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 方式一：生成列 + 普通索引
ALTER TABLE events
    ADD COLUMN user_name VARCHAR(64)
        AS (payload ->> '$.user') STORED,
    ADD INDEX idx_user (user_name);
-- 方式二：函数索引（MySQL 8.0.13+）
ALTER TABLE events ADD INDEX idx_score ((CAST(payload ->> '$.score' AS UNSIGNED)));
-- 方式三：多值索引，专为 JSON 数组设计（8.0.17+）
ALTER TABLE events
    ADD INDEX idx_tags ((CAST(payload -> '$.tags' AS CHAR(32) ARRAY)));
-- 之后可用 MEMBER OF / JSON_CONTAINS 命中索引
SELECT * FROM events WHERE 'go' MEMBER OF (payload -> '$.tags');

1
2
3

var raw []byte
err := db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&raw)
// raw 里就是 {"user":"alice",...}，再自行 json.Unmarshal

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

package jsontype
import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
)
// JSON[T] 让任意类型 T 可直接作为 JSON/JSONB 列读写。
type JSON[T any] struct {
	Val T
}
func New[T any](v T) JSON[T] { return JSON[T]{Val: v} }
// Value 实现 driver.Valuer：写入数据库时调用。
func (j JSON[T]) Value() (driver.Value, error) {
	b, err := json.Marshal(j.Val)
	if err != nil {
		return nil, err
	}
	return b, nil // 返回 []byte，pq / mysql 驱动都接受
}
// Scan 实现 sql.Scanner：从数据库读取时调用。
func (j *JSON[T]) Scan(src any) error {
	if src == nil {
		var zero T
		j.Val = zero
		return nil
	}
	var b []byte
	switch v := src.(type) {
	case []byte:
		b = v
	case string:
		b = []byte(v)
	default:
		return fmt.Errorf("jsontype: 不支持的类型 %T", src)
	}
	return json.Unmarshal(b, &j.Val)
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

type Payload struct {
	User  string   `json:"user"`
	Tags  []string `json:"tags"`
	Score int      `json:"score"`
}
// 写入
p := jsontype.New(Payload{User: "alice", Tags: []string{"go", "db"}, Score: 42})
_, err := db.Exec("INSERT INTO events (payload) VALUES ($1)", p)
// 读取
var got jsontype.JSON[Payload]
err = db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&got)
fmt.Println(got.Val.User) // alice

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

type NullJSON[T any] struct {
	Val   T
	Valid bool // 为 false 表示数据库中是 NULL
}
func (j NullJSON[T]) Value() (driver.Value, error) {
	if !j.Valid {
		return nil, nil
	}
	return json.Marshal(j.Val)
}
func (j *NullJSON[T]) Scan(src any) error {
	if src == nil {
		j.Valid = false
		return nil
	}
	j.Valid = true
	b, ok := src.([]byte)
	if !ok {
		b = []byte(src.(string))
	}
	return json.Unmarshal(b, &j.Val)
}

1
2
3
4
5

// 复用 4.2 的泛型包装器，T = map[string]any
var attrs jsontype.JSON[map[string]any]
err = db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&attrs)
weight, ok := attrs.Val["weight"].(float64) // 代价：取值要逐个类型断言

1
2
3
4
5
6

// PostgreSQL
var user string
db.QueryRow(`SELECT payload ->> 'user' FROM events WHERE id = $1`, 1).Scan(&user)
// MySQL
db.QueryRow(`SELECT payload ->> '$.user' FROM events WHERE id = ?`, 1).Scan(&user)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

import "gorm.io/datatypes"
type Event struct {
	ID      uint
	Payload datatypes.JSONType[Payload] // 建表时自动映射为 jsonb / json
}
// 写
db.Create(&Event{Payload: datatypes.NewJSONType(Payload{User: "alice"})})
// 读
var e Event
db.First(&e, 1)
fmt.Println(e.Payload.Data().User)
// 按 JSON 字段查询（跨库表达式）
db.Where(datatypes.JSONQuery("payload").Equals("alice", "user")).Find(&events)

1
2
3
4
5
6
7

-- PG：生成列，把热字段暴露成普通列（可建 B-Tree、有统计信息）
ALTER TABLE events
  ADD COLUMN user_name text
  GENERATED ALWAYS AS (payload ->> 'user') STORED;
-- 或者只在查询侧建表达式索引，不动表结构
CREATE INDEX idx_user ON events ((payload ->> 'user'));