PHP小码农

Appearance

JSONB 的创建与插入 ​

上一节我们确定了「默认一律用 JSONB」的结论。本节进入实操：如何正确地把 JSON 数据写进 JSONB 列。你将了解 JSONB 字面量的引号陷阱、五组生成函数（to_jsonb / jsonb_build_object / jsonb_object / row_to_json / json_object）的适用边界、从查询结果批量构造文档的写法、用 IS JSON 与检查约束把脏数据挡在写入侧，以及应用层参数绑定的正确姿势。

前置知识

阅读本节前，建议先了解：JSON 与 JSONB 的对比

基础概念 ​

JSONB 列的值可以来自三个地方：SQL 里的字面量、生成函数、应用层传入的参数。三者的类型转换路径不同，踩坑点也不同。

写入方式类型转换路径典型问题
字面量 '{"a":1}'::jsonb字符串 → jsonb 输入函数单引号需转义、JSON 内部的 " 与 SQL 的 ' 混淆
生成函数 jsonb_build_object(...)直接产出 jsonb，无二次解析参数个数、键的合法性
应用层参数绑定驱动按 jsonb 类型发送文本，服务端解析应避免拼接 SQL 字符串

JSONB 的存储结构决定了几个必须提前知道的约束：

特性取值
单个值上限1 GB（与 JSON 相同）
键的类型只能是字符串（JSON 规范要求）
值可为 SQL NULL 吗可以，SQL NULL 的键值对会被整体丢弃（见下）
数字范围受 numeric 约束，1e400 等会报错
字符串限制禁止 \u0000

有一个非常容易误解的点：jsonb_build_object('a', NULL) 不会生成 {"a": null}，而是生成 {}。

sql
SELECT
  jsonb_build_object('a', NULL)             AS built_object,
  jsonb_build_array(1, NULL, 2)             AS built_array,
  '{"a": null}'::jsonb                      AS literal_object;
text
 built_object |   built_array    | literal_object
--------------+------------------+----------------
 {}           | [1, null, 2]     | {"a": null}
(1 row)

差别在于：SQL 的 NULL 在对象构造时会让整个键值对被跳过，在数组构造时则会被转成 JSON 的 null（因为数组元素不能跳过，只能占位）。而 JSON 字面量里的 null 是 JSON null，与 SQL NULL 完全无关，会被原样保留。混淆这两者是最常见的 JSONB 数据错误来源。

关键术语：

  • JSON 字面量（JSON Literal）：以文本形式书写的 JSON，需要显式转换 ::jsonb 才能写入列。
  • JSON null 与 SQL NULL：前者是 JSON 里的一个值（"a": null），后者是「没有值」。jsonb_typeof('null'::jsonb) 返回 null 字符串，而 SQL NULL 的 jsonb_typeof 返回 SQL NULL。
  • 生成函数（Creation Functions）：直接返回 json / jsonb 的函数，如 jsonb_build_object。好处是完全绕过字符串转义。
  • 美元引用（Dollar Quoting）：$tag$...$tag$ 形式的字符串字面量，内部不需要转义单引号，适合书写大段 JSON。

语法与用法 ​

text
-- 1. 字面量转换
'{"a": 1}'::jsonb
'[1, 2, 3]'::jsonb
'null'::jsonb                     -- 合法：JSON 的 null 字面量
'"hello"'::jsonb                  -- 合法：JSON 字符串
'123'::jsonb                      -- 合法：JSON 数字

-- 2. 通用转换
to_jsonb(任意 SQL 值)
to_json(任意 SQL 值)

-- 3. 可变参数构造
jsonb_build_object(键1, 值1, 键2, 值2, ...)
jsonb_build_array(值1, 值2, ...)

-- 4. 文本数组构造
jsonb_object(text[])               -- 奇数个元素按 键,值,键,值 成对解释
jsonb_object(键数组 text[], 值数组 text[])

-- 5. 行/数组转换
row_to_json(复合类型)
array_to_json(任意数组)

-- 6. SQL/JSON 标准构造函数（PostgreSQL 16+）
json_object('k' VALUE 'v', 'k2': 'v2')
json_array(1, true, NULL::text)
json_scalar(123.45)

-- 7. 校验谓词（PostgreSQL 16+）
表达式 IS JSON                        -- 能否解析为 JSON
表达式 IS JSON OBJECT                 -- 是否对象
表达式 IS JSON ARRAY WITH UNIQUE KEYS -- 是否无重复键的数组
表达式 IS NOT JSON                    -- 取反

-- 8. 建表与插入
CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY, doc jsonb NOT NULL DEFAULT '{}'::jsonb);
INSERT INTO t (doc) VALUES ('{"a": 1}'::jsonb);

最小的写入示例：

sql
DROP TABLE IF EXISTS kv_demo;

CREATE TABLE kv_demo (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc  jsonb  NOT NULL DEFAULT '{}'::jsonb
);
text
DROP TABLE
CREATE TABLE
sql
INSERT INTO kv_demo (doc) VALUES
  ('{"a": 1, "b": "text"}'::jsonb),
  (jsonb_build_object('c', 2, 'd', jsonb_build_array(1, 2))),
  ('{}'::jsonb);   -- 使用默认值效果相同

SELECT * FROM kv_demo ORDER BY id;
text
 id |                 doc
----+--------------------------------------
  1 | {"a": 1, "b": "text"}
  2 | {"c": 2, "d": [1, 2]}
  3 | {}
(3 rows)

详细说明 ​

字面量写入：单引号陷阱与美元引用 ​

JSON 规范要求字符串用双引号定界，而 SQL 字符串字面量用单引号定界。这两套规则叠在一起，就产生了 JSONB 入门第一坑。

sql
-- 错误！JSON 内部误用单引号，SQL 解析器把 ' 当成字符串结束符
INSERT INTO kv_demo (doc) VALUES ('{"name": 'Acme'}');
text
ERROR:  syntax error at or near "Acme"
LINE 1: INSERT INTO kv_demo (doc) VALUES ('{"name": 'Acme'}');
                                                       ^

即使侥幸通过 SQL 解析，JSON 解析器也不接受单引号：

sql
-- 错误！单引号是合法的 SQL 字符串，但不是合法的 JSON
SELECT '{''a'': 1}'::jsonb;
text
ERROR:  invalid input syntax for type json
LINE 1: SELECT '{''a'': 1}'::jsonb;
               ^
DETAIL:  Token "'" is invalid.

正确做法有三条路径，按推荐程度排序：

sql
-- 方案 A（最推荐）：用生成函数，完全不用关心转义
SELECT jsonb_build_object('name', 'O''Brien', 'age', 30) AS by_function;
text
                    by_function
---------------------------------------------------
 {"age": 30, "name": "O'Brien"}
(1 row)
sql
-- 方案 B：美元引用，JSON 内部可以随意写引号
SELECT $json${"name": "O'Brien", "note": "含 \"双引号\" 也没问题"}$json$::jsonb AS by_dollar_quote;
text
                          by_dollar_quote
--------------------------------------------------------------------
 {"name": "O'Brien", "note": "含 \"双引号\" 也没问题"}
(1 row)
sql
-- 方案 C：老老实实把 JSON 内部的 " 写在 SQL 的 '' 之间
SELECT '{"name": "O''Brien"}'::jsonb AS by_escaping;
text
        by_escaping
---------------------------
 {"name": "O'Brien"}
(1 row)

三种写法的选择建议：

场景推荐写法原因
值来自 SQL 变量/列jsonb_build_object自动处理转义与类型
手写固定的演示数据美元引用可读性最好，无需数引号
少量简单字面量（无引号）普通单引号最简洁
多行大文档美元引用换行不会被误解析

绝对不要用字符串拼接把用户输入塞进 JSON 字面量，这既是转义灾难也是注入入口：

sql
-- 错误！用户输入 O'Brien 会让 SQL 语法崩掉；更糟的是可能被注入
-- INSERT INTO kv_demo (doc) VALUES ('{"name": "' || user_input || '"}');

生成函数对比 ​

五组生成函数的定位差异很大，选错会导致写出冗长或语义错误的 SQL：

函数返回类型输入形态适用场景
to_jsonb(anyelement)jsonb任意单个 SQL 值把一行、一个数组、一个标量整体转 JSON
jsonb_build_object(VARIADIC "any")jsonb交替的键值对手工拼装小对象，键名是字面量
jsonb_build_array(VARIADIC "any")jsonb任意个值手工拼装小数组
jsonb_object(text[])jsonb一维或二维文本数组键值来自数组（如解构的行）
row_to_json(record)json复合类型整个表行转对象，键名即列名
json_object(...)jsonk VALUE v 或 k: v标准 SQL/JSON 语法，需放宽到 PG16+

注意一个不对称：jsonb_build_object 系列返回 jsonb，而 row_to_json / array_to_json / json_object / json_array 返回 json。要用 jsonb 需要再转一次。

sql
-- row_to_json 返回 json，需要 ::jsonb 才能放进 jsonb 列
SELECT
  pg_typeof(row_to_json(kv_demo))          AS row_to_json_type,
  pg_typeof(to_jsonb(kv_demo))             AS to_jsonb_type,
  pg_typeof(jsonb_build_object('a', 1))    AS build_object_type;
text
 row_to_json_type | to_jsonb_type | build_object_type
------------------+---------------+-------------------
 json             | jsonb         | jsonb
(1 row)

jsonb_build_object / jsonb_build_array ​

这是最常用的两个函数。键会被强制转成 text，值按 to_jsonb 规则转换。

sql
SELECT jsonb_build_object(
         'id',        1001,                       -- 数字
         'name',      '机械键盘 Pro',              -- 字符串
         'active',    true,                       -- 布尔
         'price',     349.00,                     -- numeric
         'tags',      jsonb_build_array('hot', 'new'),
         'spec',      jsonb_build_object('switch', 'red', 'layout', '87键')
       ) AS product;
text
                                         product
------------------------------------------------------------------------------------------
 {"id": 1001, "name": "机械键盘 Pro", "spec": {"layout": "87键", "switch": "red"}, "tags": ["hot", "new"], "active": true, "price": 349.00}
(1 row)

参数个数必须是偶数，否则报错：

sql
-- 错误！键值不成对
SELECT jsonb_build_object('a', 1, 'b');
text
ERROR:  argument list must have even number of elements
HINT:  The arguments of jsonb_build_object() must consist of alternating keys and values.

键不能是 SQL NULL：

sql
-- 错误！键不能为 NULL
SELECT jsonb_build_object(NULL, 1);
text
ERROR:  null value not allowed for object key

jsonb_build_object 允许重复键，后者覆盖前者（与 JSONB 存储语义一致）：

sql
SELECT jsonb_build_object('a', 1, 'a', 2) AS dup_key;
text
 dup_key
---------
 {"a": 2}
(1 row)

jsonb_build_array 则是把 SQL NULL 转成 JSON null，不会跳过：

sql
SELECT
  jsonb_build_array(1, NULL, 'x')     AS arr_with_null,
  jsonb_build_array()                 AS empty_arr;
text
   arr_with_null   | empty_arr
-------------------+-----------
 [1, null, "x"]    | []
(1 row)

to_jsonb 与 row_to_json：把整行转成 JSON ​

数据已经在表里时，用 to_jsonb(表别名) 可以一行代码把整行转成对象，键名就是列名：

sql
DROP TABLE IF EXISTS users_demo;

CREATE TABLE users_demo (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username varchar(32) NOT NULL,
  email    varchar(128) NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  profile  jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO users_demo (username, email, profile) VALUES
  ('alice', 'alice@example.com', '{"city": "上海", "level": 3}'),
  ('bob',   'bob@example.com',   '{"city": "北京", "level": 1}');
text
DROP TABLE
CREATE TABLE
INSERT 0 2
sql
SELECT to_jsonb(u) AS row_as_jsonb
FROM users_demo u
WHERE username = 'alice';
text
                                                row_as_jsonb
-------------------------------------------------------------------------------------------------------------
 {"id": 1, "username": "alice", "email": "alice@example.com", "is_active": true, "profile": {"city": "上海", "level": 3}}
(1 row)

注意 to_jsonb(u) 会把 profile 内嵌为嵌套对象而不是字符串——这正是 to_json 系列的递归转换规则：数组与复合类型递归转换，jsonb 值保持 jsonb。相比 row_to_json，to_jsonb 少了一次「先转 json 再转 jsonb」的往返。

sql
-- array_to_json：数组转 JSON 数组，二维数组转嵌套数组
SELECT array_to_json('{{1,5},{99,100}}'::int[]) AS matrix;
text
      matrix
------------------
 [[1,5],[99,100]]
(1 row)

array_to_json / row_to_json 的第二个布尔参数为 true 时会在顶层元素间插入换行，纯粹为了可读性：

sql
SELECT row_to_json(t, true)
FROM (SELECT 1 AS a, 2 AS b) t;
text
     row_to_json
----------------------
 {                   +
     "a":1,          +
     "b":2           +
 }
(1 row)

jsonb_object：从文本数组构造 ​

jsonb_object 与 jsonb_build_object 的差别是输入是数组而非可变参数，且所有值都会被转成 JSON 字符串。这在从 array_agg 或二维数组构造时很方便。

sql
-- 一维数组，元素数必须为偶数
SELECT jsonb_object('{a, 1, b, def, c, 3.5}'::text[]) AS from_1d;
text
                        from_1d
--------------------------------------------------------
 {"a": "1", "b": "def", "c": "3.5"}
(1 row)

注意所有值都成了字符串（"1" 而非 1），因为 jsonb_object 的输入被声明为 text[]。需要保留数值类型时应当用 jsonb_build_object 或 jsonb_object_agg。

sql
-- 二维数组，每个内层数组恰好两个元素
SELECT jsonb_object('{{a, 1}, {b, "def"}}'::text[]) AS from_2d;

-- 分离的键数组与值数组
SELECT jsonb_object('{a, b}'::text[], '{1, 2}'::text[]) AS from_pair;
text
              from_2d
------------------------------------
 {"a": "1", "b": "def"}
(1 row)

             from_pair
------------------------------------
 {"a": "1", "b": "2"}
(1 row)

常见的三个错误：

sql
-- 错误！一维数组元素个数为奇数
SELECT jsonb_object('{a, 1, b}'::text[]);
text
ERROR:  array must have even number of elements
sql
-- 错误！二维数组的内层不是两个元素
SELECT jsonb_object('{{a, 1}, {b, 2, 3}}'::text[]);
text
ERROR:  array must have two columns
sql
-- 错误！键数组中存在 NULL
SELECT jsonb_object('{a, NULL}'::text[], '{1, 2}'::text[]);
text
ERROR:  null value not allowed for object key

SQL/JSON 标准构造函数（PostgreSQL 16+） ​

PostgreSQL 16 引入了 SQL/JSON 标准的构造函数族，语法更贴近标准，且支持 RETURNING 指定输出类型。

sql
-- PostgreSQL 16+ 才开始支持
SELECT
  json_object('code' VALUE 'P123', 'title': 'Jaws')  AS obj,
  json_array(1, true, json '{"a": null}')            AS arr,
  json_scalar(123.45)                                AS scalar;
text
                      obj                       |        arr         | scalar
------------------------------------------------+--------------------+---------
 {"code" : "P123", "title" : "Jaws"}            | [1, true, {"a":null}] | 123.45
(1 row)

json_object 支持 NULL ON NULL / ABSENT ON NULL 与唯一性检查：

子句含义
NULL ON NULL值为 NULL 时保留键，值为 JSON null（默认）
ABSENT ON NULL值为 NULL 时整个键值对省略
WITH UNIQUE KEYS出现重复键时报错
RETURNING jsonb直接返回 jsonb 而非默认的 json
sql
-- 值为 NULL 时的两种行为
SELECT
  json_object('a' VALUE NULL, ABSENT ON NULL)  AS absent_on_null,
  json_object('a' VALUE NULL, NULL ON NULL)    AS null_on_null;
text
 absent_on_null | null_on_null
----------------+--------------
 {}             | {"a" : null}
(1 row)

输入校验：IS JSON 与检查约束（PostgreSQL 16+） ​

如果列类型是 text、bytea 或 json，可以用 IS JSON 谓词在 SQL 层面做校验。jsonb 列本身已由输入函数保证合法，无需额外校验。

谓词检查内容
x IS JSON能否解析为任意 JSON
x IS JSON VALUE同 IS JSON（完整 JSON 值）
x IS JSON SCALAR是否标量（数字/字符串/布尔/null）
x IS JSON ARRAY是否数组
x IS JSON OBJECT是否对象
x IS JSON OBJECT WITH UNIQUE KEYS对象且无重复键
x IS NOT JSON取反
sql
SELECT js,
       js IS JSON          AS "json?",
       js IS JSON SCALAR   AS "scalar?",
       js IS JSON OBJECT   AS "object?",
       js IS JSON ARRAY    AS "array?"
FROM (VALUES
  ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'), ('abc')
) AS foo(js);
text
     js     | json? | scalar? | object? | array?
------------+-------+---------+---------+--------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f
(5 rows)

对 jsonb 列做结构约束则用 jsonb_typeof：

sql
ALTER TABLE users_demo
  ADD CONSTRAINT ck_profile_is_object
  CHECK (jsonb_typeof(profile) = 'object');
text
ALTER TABLE

从查询结果批量构造文档 ​

把关系数据聚合为 JSON 文档是 JSONB 最典型的写入场景。jsonb_agg + GROUP BY 可以一次构造出嵌套结构。

sql
DROP TABLE IF EXISTS order_items_demo;

CREATE TABLE order_items_demo (
  order_no  varchar(16)  NOT NULL,
  sku       varchar(32)  NOT NULL,
  qty       integer      NOT NULL,
  price     numeric(10,2) NOT NULL
);

INSERT INTO order_items_demo VALUES
  ('SO-1001', 'SKU-1', 2, 349.00),
  ('SO-1001', 'SKU-2', 1, 119.00),
  ('SO-1002', 'SKU-3', 5,  39.00);
text
DROP TABLE
CREATE TABLE
INSERT 0 3
sql
-- 每个订单构造一个文档，明细作为嵌套数组
SELECT jsonb_build_object(
         'order_no', order_no,
         'items',    jsonb_agg(
                       jsonb_build_object('sku', sku, 'qty', qty, 'price', price)
                       ORDER BY sku          -- 聚合内排序，保证输出稳定
                     ),
         'total',    sum(qty * price)
       ) AS order_doc
FROM order_items_demo
GROUP BY order_no
ORDER BY order_no;
text
                                                    order_doc
-----------------------------------------------------------------------------------------------------------------
 {"total": 817.00, "items": [{"qty": 2, "sku": "SKU-1", "price": 349.00}, {"qty": 1, "sku": "SKU-2", "price": 119.00}], "order_no": "SO-1001"}
 {"total": 195.00, "items": [{"qty": 5, "sku": "SKU-3", "price": 39.00}], "order_no": "SO-1002"}
(2 rows)

聚合函数内部的 ORDER BY 是必须的——不加的话数组元素顺序不确定，相同输入可能产出不同文档，导致 = 比较与缓存失效：

sql
-- 不推荐：数组顺序不确定
SELECT jsonb_agg(jsonb_build_object('sku', sku)) FROM order_items_demo;
text
-- 推荐：显式排序
SELECT jsonb_agg(jsonb_build_object('sku', sku) ORDER BY sku) FROM order_items_demo;

jsonb_agg 会保留 NULL 元素，如果要跳过 NULL 用 jsonb_agg_strict：

sql
SELECT
  jsonb_agg(v)         AS keeps_null,
  jsonb_agg_strict(v)  AS skips_null
FROM (VALUES (1), (NULL), (3)) AS t(v);
text
  keeps_null   | skips_null
---------------+------------
 [1, null, 3]  | [1, 3]
(1 row)

应用层传入：参数绑定与 PHP 示例 ​

生产代码里 JSON 永远应该通过参数绑定传入，而不是拼进 SQL 文本。参数绑定有两个好处：不需要关心任何转义，且驱动会按类型发送。

下面是 PHP 8 + PDO 的写法（假定已连接 demo 库）：

php
<?php
// JSONB 写入：把 PHP 数组序列化后作为参数绑定，绝不要拼进 SQL
$pdo = new PDO('pgsql:host=127.0.0.1;dbname=demo', 'app_user', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$profile = ['city' => "Xi'an", 'level' => 3, 'note' => '含 "引号" 与 \'单引号\''];

$stmt = $pdo->prepare(
    'INSERT INTO users_demo (username, email, profile)
     VALUES (:username, :email, :profile::jsonb)
     RETURNING id'
);
$stmt->execute([
    ':username' => 'carol',
    ':email'    => 'carol@example.com',
    // 用 json_encode 保证是合法 JSON，flag 保证非 ASCII 不被转义成 \uXXXX
    ':profile'  => json_encode($profile, JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR),
]);
echo $stmt->fetchColumn(), PHP_EOL;   // 输出新行 id

查询侧同理，把 JSON 当普通参数传：

php
<?php
// 按 JSONB 包含查询：条件本身也走绑定
$stmt = $pdo->prepare('SELECT id, username FROM users_demo WHERE profile @> :cond');
$stmt->execute([':cond' => json_encode(['city' => '上海'])]);
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

注意事项：

  • json_encode 遇到非法 UTF-8 会返回 false，所以一定要加 JSON_THROW_ON_ERROR，否则会写入空字符串。
  • 不要为了「省事」把 JSON 用 addslashes 处理后拼进 SQL，这对 \u0000、控制字符等场景无效。
  • 如果列是 jsonb，PostgreSQL 会自动规范化（去键重复、重排），不要在应用层假设写入什么就能读回什么。
  • \u0000 无法写入：json_encode 会把 PHP 字符串里的 NUL 编码成 \u0000，PostgreSQL 的 jsonb 会拒绝，必须在应用层过滤。

实战示例 ​

下面完整演示一个「用户行为事件」表的建表、多种写入方式、批量构造、结构校验与错误处理。

第一步：建表，包含约束与默认值

sql
DROP TABLE IF EXISTS user_events;

CREATE TABLE user_events (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint      NOT NULL,
  event_type  varchar(32) NOT NULL,
  payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
  occurred_at timestamptz NOT NULL DEFAULT now(),

  -- 约束一：payload 必须是 JSON 对象，不能是数组或标量
  CONSTRAINT ck_payload_is_object CHECK (jsonb_typeof(payload) = 'object'),
  -- 约束二：payload 体积上限，避免单行无限膨胀
  CONSTRAINT ck_payload_size CHECK (pg_column_size(payload) < 8192)
);

COMMENT ON COLUMN user_events.payload IS '事件负载，结构随 event_type 变化';
text
DROP TABLE
CREATE TABLE
COMMENT

第二步：三种写入方式对照

sql
-- 1) 字面量（美元引用，适合手写固定数据）
INSERT INTO user_events (user_id, event_type, payload)
VALUES (1001, 'page_view', $json${
  "url": "/products/list",
  "referer": null,
  "duration_ms": 1200
}$json$::jsonb);

-- 2) 生成函数（推荐：值来自变量时）
INSERT INTO user_events (user_id, event_type, payload)
VALUES (1001, 'add_to_cart', jsonb_build_object(
  'sku', 'SKU-1',
  'qty', 2,
  'price', 349.00,
  'source', 'detail_page'
));

-- 3) 从查询结果聚合（把批量事件一次性写入）
INSERT INTO user_events (user_id, event_type, payload)
SELECT
  1002,
  'search',
  jsonb_build_object('keyword', kw, 'result_count', cnt, 'page', 1)
FROM (VALUES ('键盘', 42), ('鼠标', 88)) AS t(kw, cnt);
text
INSERT 0 1
INSERT 0 1
INSERT 0 2

第三步：读回验证

sql
SELECT id, user_id, event_type, payload
FROM user_events
ORDER BY id;
text
 id | user_id |  event_type  |                                payload
----+---------+--------------+-----------------------------------------------------------------------
  1 |    1001 | page_view    | {"url": "/products/list", "referer": null, "duration_ms": 1200}
  2 |    1001 | add_to_cart  | {"qty": 2, "sku": "SKU-1", "price": 349.00, "source": "detail_page"}
  3 |    1002 | search       | {"page": 1, "keyword": "键盘", "result_count": 42}
  4 |    1002 | search       | {"page": 1, "keyword": "鼠标", "result_count": 88}
(4 rows)

注意第 1 行：JSON 里的 "referer": null 被完整保留（这是 JSON null），而不是被丢弃——再次印证「JSON null 与 SQL NULL 不同」。

第四步：验证约束确实生效

sql
-- 错误！payload 传了数组，违反 ck_payload_is_object
INSERT INTO user_events (user_id, event_type, payload)
VALUES (1003, 'bad_event', '[1, 2, 3]'::jsonb);
text
ERROR:  new row for relation "user_events" violates check constraint "ck_payload_is_object"
DETAIL:  Failing row contains (5, 1003, bad_event, [1, 2, 3], 2026-09-22 10:00:00+08).
sql
-- 错误！payload 超过 8 KB，违反 ck_payload_size
INSERT INTO user_events (user_id, event_type, payload)
VALUES (1003, 'big_event', jsonb_build_object('blob', repeat('x', 10000)));
text
ERROR:  new row for relation "user_events" violates check constraint "ck_payload_size"

第五步：用 IS JSON 校验外部文本（PostgreSQL 16+）

外部系统送来的原始文本还没进表时，可以先判断合法性：

sql
-- PostgreSQL 16+ 才支持 IS JSON 谓词
SELECT raw,
       raw IS JSON OBJECT          AS is_object,
       raw IS JSON OBJECT WITH UNIQUE KEYS AS no_dup_keys
FROM (VALUES
  ('{"a": 1, "b": 2}'),
  ('{"a": 1, "a": 2}'),
  ('[1,2]')
) AS t(raw);
text
        raw         | is_object | no_dup_keys
--------------------+-----------+-------------
 {"a": 1, "b": 2}   | t         | t
 {"a": 1, "a": 2}   | t         | f
 [1,2]              | f         | f
(3 rows)

第六步：清理

sql
DROP TABLE user_events;
DROP TABLE users_demo;
DROP TABLE order_items_demo;
DROP TABLE kv_demo;
text
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

注意事项 ​

  • JSON 内部必须用双引号。JSON 的字符串定界符是 "，SQL 的是 '，两者不能互换。含单引号的字符串值是 SQL 层的转义问题，含双引号的 JSON 语法是 JSON 层的规则，要分开思考。

  • SQL NULL 与 JSON null 是两回事。jsonb_build_object('a', NULL) 产出 {}，'{"a": null}'::jsonb 产出 {"a": null}。前者丢字段，后者留字段，语义完全不同。

  • jsonb_object 会把所有值变成字符串。它的入参是 text[]，所以 jsonb_object('{a, 1}') 得到 {"a": "1"} 而不是 {"a": 1}。需要保留数值类型就用 jsonb_build_object 或 jsonb_object_agg。

  • row_to_json / array_to_json / json_object / json_array 返回 json 而非 jsonb。写入 jsonb 列时会隐式转换，但显式写 ::jsonb 更清晰，也避免在表达式中意外走 json 的慢路径。

  • 聚合构造文档时必须显式排序。jsonb_agg 不保证元素顺序，同一份数据可能产出不同文档，破坏 = 比较与缓存。统一写 jsonb_agg(... ORDER BY 排序键)。

  • \u0000 永远写不进 jsonb。应用层要先过滤 NUL 字节，否则报 unsupported Unicode escape sequence。

  • jsonb 列别忘加 NOT NULL DEFAULT '{}'::jsonb。否则下游每条查询都要判空，jsonb_set 之类函数遇到 SQL NULL 也只会返回 NULL，静默失败。

  • 检查约束里的 jsonb_typeof 只在值为 SQL NULL 时返回 NULL，而 CHECK 对 NULL 结果视为通过。所以「必须为对象」的约束要配合 NOT NULL，或者写成 payload IS NULL OR jsonb_typeof(payload) = 'object'。

  • 不要给 jsonb 列再套一层 text。它已经是二进制结构，再转 text 存储会丧失所有索引与操作符能力。

  • 大文档的插入代价明显高于小文档。jsonb 写入需要完整解析并转二进制，几 MB 的文档单次插入可能耗时几百毫秒，能拆就拆。

最佳实践 ​

  • 手写固定数据用美元引用，动态数据用生成函数。这是最省心的组合：

    sql
    -- 固定演示/初始化数据
INSERT INTO t (doc) VALUES ($json${"a": 1, "note": "O'Brien"}$json$::jsonb);

-- 动态值
INSERT INTO t (doc) VALUES (jsonb_build_object('name', v_name, 'qty', v_qty));

  • 建表时给默认值与结构约束：

    sql
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
CONSTRAINT ck_payload_is_object CHECK (jsonb_typeof(payload) = 'object')

  • 生产代码一律用参数绑定，并在序列化时打开严格模式（PHP 的 JSON_THROW_ON_ERROR、Python 的 json.dumps 后再让驱动处理），不要让非法 JSON 静默变成 NULL。

  • 批量写入用 INSERT ... SELECT + 聚合函数，一次网络往返写入多行，比逐行 INSERT 快一个数量级：

    sql
    INSERT INTO user_events (user_id, event_type, payload)
SELECT user_id, event_type, jsonb_build_object(...)
FROM staging_events;

  • 用 COPY 加载大批量 JSON 数据。把每行 JSON 放一列，COPY ... FROM STDIN 是 PostgreSQL 最快的导入路径，比 INSERT 快数倍。

  • 写入前在应用层先校验 JSON 结构。数据库约束是最后一道防线，但报错发生在事务中间会拖累整个批次；应用层先过滤能显著降低失败重试成本。

  • 需要保留原文时用双列设计：payload_raw json 存原文用于验签，payload jsonb 存规范化版本用于查询。用生成列可以自动同步：

    sql
    payload_raw json  NOT NULL,
payload_parsed jsonb GENERATED ALWAYS AS (payload_raw::jsonb) STORED

  • 给 JSONB 文档的字段名用短名。键名在每一行都重复存储，uid 比 user_identifier 省下大量空间。

  • 不要用 JSONB 存「本该是列」的数据。id、created_at、status 这类高频筛选字段应当独立成列，JSONB 只承载结构不定的扩展部分。

下一节

继续学习：JSONB 的查询操作

参考链接 ​