JSON 函数和操作符

PostgreSQL 提供 json（保留输入文本）和 jsonb（适合查询及索引）。以下重点使用 jsonb；JSON 适合结构可变字段，但并不替代普通列上的类型、外键和约束。

读取与判断

SELECT '{"name":"Ada","tags":["sql"]}'::jsonb -> 'tags';
-- ["sql"]，结果仍是 jsonb
SELECT '{"name":"Ada"}'::jsonb ->> 'name'; -- Ada，结果是 text
SELECT '{"profile":{"city":"上海"}}'::jsonb #>> '{profile,city}';

->、#> 返回 JSON 值，->>、#>> 返回文本；数组索引从 0 开始。路径不存在时得到 SQL NULL。@> 判断包含，? 判断对象是否含顶层键（也可用于数组中的字符串元素）；它不会递归查找任意层级键。

构造、展开和更新

SELECT jsonb_build_object('id', 1, 'active', true);
SELECT key, value FROM jsonb_each('{"a":1,"b":2}'::jsonb);
SELECT jsonb_set('{"a":1}'::jsonb, '{a}', '2'::jsonb); -- {"a": 2}
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb;

jsonb_array_elements 将数组拆成多行，jsonb_to_record/jsonb_to_recordset 可按列定义转换对象。jsonb_set 需要指定路径，默认只允许最后一层新建，已有的上级路径必须存在；|| 只做顶层合并，重名键由右侧覆盖。- 可删除键或数组元素，#- 删除路径。更新会形成新的 JSON 值，不是原地修改某几个字节。

null 与查询

'{"a":null}'::jsonb -> 'a' 是 JSON null，而 ->> 'a' 得到 SQL NULL；缺失键也可产生 SQL NULL，必要时用 ? 或 jsonb_typeof 区分。@?、@@ 与 jsonb_path_query 提供 SQL/JSON 路径查询，路径规则不同于 #> 的文本路径数组。

常见 @>、? 查询可考虑 GIN 索引；固定标量路径的筛选可考虑表达式索引。索引操作符覆盖范围取决于所用操作符类，实际计划用 EXPLAIN 核对。