最近做项目跟Postgresql数据库里的Json类型字段打交道特别多翻了无数遍官方文档也踩了不少坑。很多朋友一提到 JSON 就想到 MySQL其实 PostgreSQL 在这块的支持相当成熟而且很多细节在官方文档里写得明明白白只是文档是英文的又有一定厚度不少人懒得细看。这篇东西我就把官方文档里的关键点、我自己实操总结的经验、以及常见的坑一次性聊透适合刚接触 PG 中 JSON 字段的人也适合已经在用但想优化查询或索引的人参考。先说结论PostgreSQL 的 JSON 支持从 9.2 版本引入但真正好用是从 9.4 版本加入jsonb类型开始的。如果你还在用json类型存数据除非有特殊理由否则我强烈建议你切换到jsonb。为什么往下看就知道了。1. JSON字段的使用背景与json/jsonb选型1.1 为什么业务里需要JSON字段在实际项目里关系型数据库的范式设计有时候会显得特别死板。比如一个商品表不同品类的商品属性完全不一样衣服有尺码和颜色电子产品有电池容量和屏幕尺寸食品有保质期和配料表。如果为了这些差异化属性都去建列表结构会膨胀到没法维护。这时候 JSON 字段就是很自然的解决方案。把不确定的、会变化的、多层的属性塞进一个 JSON 字段里既不用频繁 ALTER TABLE查询时又能利用 JSON 的查询语法直接过滤。我参与过的几个项目里JSON 字段最常见的几个使用场景就是动态表单字段由运营后台配置用户填写的内容无法预先定义表结构。第三方接口数据落地外部 API 返回的数据字段会变化直接存 JSON 省去一层映射。配置项存储本来用配置文件后来搬进数据库用 JSON 格式存最灵活。事件埋点/日志类数据不同事件带的参数集完全不同存 JSON 再合适不过。但反过来说JSON 字段也不是拿来当万能膏药用的。如果一个字段在你的业务逻辑里会被高频用于 WHERE 过滤、JOIN 关联、排序那么它更适合建成独立列。JSON 适合的是存储为主、查询为辅的数据。1.2 json和jsonb到底怎么选官方文档在 JSON 类型那一章里开头就放了一个对比表。我第一次看到这个表的时候毫不犹豫就决定用jsonb了。这里把官方文档的核心对比点翻译并整理一下对比维度jsonjsonb存储格式文本原样保存包含空格、缩进、重复键二进制格式键值已解析删除重复键查询性能每次查询需要重新解析直接操作二进制查询更快索引支持不支持直接创建索引支持 GIN 索引写入性能快几乎不改动原文本稍慢需要解析和转换语义区别保留键顺序、保留重复键不保证键顺序重复键只保留最后一个全文检索等高级能力部分支持但不方便完整支持官方文档很明确地写了一句一般应用场景下推荐使用jsonb。我也非常认同。json类型最大的优点就是存得快和保留原样但代价是查询时每次都要重新解析而且不能建索引哪怕你只想对 JSON 里的某个字段做 WHERE 过滤也只能全表扫。这在数据量小的时候无所谓一旦数据量上来性能完全没法看。还有人会问那我用json存字符串用LIKE查询行不行技术上可以但性能极差而且LIKE %name:张三%这种写法你还得提心吊胆地处理空格、键顺序、转义问题。别这么干规范做法就是用jsonb加 GIN 索引。另外注意如果你把json字段迁移到jsonb官方文档也强调了这两个类型在语义上不是 100% 等价的。比如json中{a:1, a:2}是合法的取出来时用-能拿到两个值其实是按序列顺序取而jsonb会直接把重复键干掉只留最后一个。所以做迁移前最好先排查一下存量数据里有没有重复键的情况。2. 官方文档要点拆解操作符、函数与查询语法2.1 官方文档怎么高效地查PostgreSQL 官方文档是出了名的全但也是出了名的大。直接打开首页找 JSON 相关内容很容易迷路。我一般直接记住这几个位置JSON 类型说明在Documentation → Chapter 8. Data Types → 8.14. JSON TypesJSON 函数和操作符在Chapter 9. Functions and Operators → 9.16. JSON Functions and OperatorsGIN 索引说明在Chapter 70. Index Access Methods → 70.5. GIN Indexes如果你想省事直接在搜索引擎里搜postgresql json functions 当前版本号一般第一条就是官方文档对应章节。注意版本号很重要不同版本之间函数的实现和返回类型有细微差别比如 PostgreSQL 14 新增了jsonb下标语法15 又改进了json某些函数的性能。我的经验是参考文档时认准你正在用的 PG 大版本不要拿 13 的文档去指导 15 的环境。2.2 高频操作符详解-、-、#、#、、?官方文档里列出的 JSON 操作符大概有二十来个但日常开发用到的基本上就十几个。我把最常用的整理出来对照着代码讲比死记硬背文档高效得多。第一组键值提取类-返回 JSON 类型的值返回结果仍然是 JSON 格式字符串值会带引号。-返回文本类型text字符串值不带引号。这两个是最容易混淆的。看个例子-- 建一张测试表 CREATE TABLE test_json ( id serial PRIMARY KEY, info jsonb ); -- 插入一条数据 INSERT INTO test_json (info) VALUES ({name: 张三, age: 18, tags: [开发, 后端]}); -- - 返回的是 JSON 类型 SELECT info-name FROM test_json; -- 结果: 张三 -- - 返回的是文本类型 SELECT info-name FROM test_json; -- 结果: 张三你用info-name拿到的值在 PostgreSQL 里是jsonb类型如果你要去做字符串拼接需要先把它转成 text不然会报错。而-拿到的就是纯文本多数场景下更顺手。第二组路径提取类#按路径提取返回 JSON 类型#按路径提取返回文本类型比如上面的测试数据我想取tags数组里的第一个元素SELECT info#{tags,0} FROM test_json; -- 结果: 开发 SELECT info#{tags,0} FROM test_json; -- 结果: 开发路径写法是用花括号括起来的数组数组里的数字默认是下标从 0 开始。这里的0不需要加引号PG 会自动识别成数组下标。如果路径深比如{a,b,c}一层一层往下走。在实际项目里嵌套三层以上的 JSON 并不少见用#会比连续用多个-简洁很多可读性也好很多。第三组包含判断类判断左侧 JSON 是否包含右侧 JSON判断右侧 JSON 是否包含左侧 JSON是的反向操作这个非常强大相当于直接把JSON 里有没有这个结构变成了一条 SQL-- 查 name 为张三的记录 SELECT * FROM test_json WHERE info {name: 张三}; -- 查包含某个 tag 的记录 SELECT * FROM test_json WHERE info {tags: [开发]};注意的右侧必须是一个合法的 JSON 字符串。如果写成info {name: 张三少一个大括号直接报语法错误。这个操作符配合 GIN 索引就是你想实现根据 JSON 内某字段查数据的最佳姿势。后面索引部分会详细讲。第四组存在性检查类?判断键是否存在?|判断任一键存在?判断所有键都存在-- name 键是否存在 SELECT * FROM test_json WHERE info ? name; -- tags 或 age 键是否存在 SELECT * FROM test_json WHERE info ?| array[tags, age]; -- name 和 age 键必须都存在 SELECT * FROM test_json WHERE info ? array[name, age];这三个操作符只适用于jsonb类型json类型不能用。这也是很多人从json切到jsonb后才发现原来还有这种操作。2.3 常用函数与JSON数组处理jsonb的函数是重头戏官方文档在 JSON Functions and Operators 一节里列了几十个。我把开发中最有用的挑出来讲。jsonb_set更新 JSON 中某个字段这是最常用的一个。之前很多人在更新 JSON 字段时是先把整个 JSON 取出来在业务代码里改完再整个写回去。这样不仅麻烦而且容易产生并发覆盖问题。jsonb_set可以做到数据库层面定点更新-- 语法 jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) -- 示例把 age 改成 20 UPDATE test_json SET info jsonb_set(info, {age}, 20) WHERE id 1;这里有个大坑如果路径不存在默认create_missing是 true会自动创建这个字段如果设成 false路径不存在就什么都不改。官方文档特意说明了这个参数的含义。我们生产环境里有个配置表就因为这个默认行为出现过新增了一条不想要的字段的情况后来统一加上了第四个参数。jsonb_array_elements把 JSON 数组拆成多行这算是炸开操作类似把数组变成一张临时表SELECT jsonb_array_elements(info-tags) AS tag FROM test_json; -- 结果两行 开发、后端这个函数特别适合做数组内的统计比如统计所有商品里有哪些品牌标签。jsonb_each把整个 JSON 对象拆成两列jsonb_each会把{a:1, b:2}变成两行每行两列key 和 value。这个函数在实现动态 KV 表逻辑的时候特别好用配合jsonb_object_keys可以拿到所有的键集合。row_to_json把查询结果转成 JSON有时候后端接口需要的不是一行行的字段而是一个嵌套的 JSON 对象。用row_to_json可以省去在代码里拼 JSON 的功夫SELECT row_to_json(t) FROM ( SELECT id, info-name AS name, info-age AS age FROM test_json ) t;row_to_json是把整行转成 JSONjson_build_object则是自己指定 key-value 对来构造 JSON。比如SELECT json_build_object(name, info-name, age, info-age) FROM test_json;这两个函数在实际 API 开发中非常常用尤其是在做报表和对接前端的时候少写很多序列化代码。3. 索引设计、性能优化与跨数据库差异3.1 GIN索引与jsonb_path_ops速度提升的关键官方文档在JSON Indexes小节讲得很清楚jsonb类型可以创建 GIN 索引json类型不行。这也是我强烈推荐jsonb的最重要原因之一。GIN 索引是 PostgreSQL 里专门处理一个值包含多个子元素这种场景的索引类型它的核心思想是倒排把 JSON 里的每个键和值都作为索引项查询时直接通过索引项定位到对应的行而不是一行一行全扫。创建方式很简单CREATE INDEX idx_test_json_info_gin ON test_json USING gin (info);创建之后像info {name: 张三}、info ? name这类查询就能走索引了。官方文档还提到了一个优化选项jsonb_path_ops。默认的 GIN 索引是把每个键值对name: 张三作为一个独立的索引项而jsonb_path_ops是把整个 JSON 的完整路径哈希成一个整数值来建索引。这意味着查询更快、索引更小。但代价是?、?|、?这三个操作符就不支持走索引了。所以选型时要想清楚自己主要用什么查询。我个人的选择是大多数场景用jsonb_path_ops因为用的频率远比?高如果代码里大量用?判断某个键是否存在那就用普通 GIN。-- 使用 jsonb_path_ops CREATE INDEX idx_test_json_info_gin ON test_json USING gin (info jsonb_path_ops);还有一点经验如果 JSON 里某个字段的值区分度很高比如商品 ID、订单号之类的与其盲目标配 GIN不如单独建一个表达式索引性能更好。这就引出第二类索引。3.2 表达式索引与普通索引针对特定字段的精准优化假设我有一张订单表订单数据存在dataJSONB 字段里其中有一个order_no字段我要经常根据它来查记录CREATE INDEX idx_orders_order_no ON orders ((data-order_no));查询的时候要确保 WHERE 子句里的表达式和索引表达式完全一致SELECT * FROM orders WHERE>EXPLAIN ANALYZE SELECT * FROM orders WHERE>CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status SMALLINT NOT NULL DEFAULT 0, total_amount NUMERIC(10,2) NOT NULL, items JSONB NOT NULL, user_ext JSONB, logistics JSONB, created_at TIMESTAMP WITH TIME ZONE DEFAULT now() ); -- 顺手建好我们需要的索引 CREATE UNIQUE INDEX idx_orders_order_no ON orders (order_no); CREATE INDEX idx_orders_user_id ON orders (user_id); CREATE INDEX idx_orders_status ON orders (status); -- 为 JSONB 建 GIN 索引方便根据物品信息查询 CREATE INDEX idx_orders_items_gin ON orders USING gin (items jsonb_path_ops); -- 为 user_ext 中的 vip_level 建表达式索引方便按会员等级筛选 CREATE INDEX idx_orders_vip_level ON orders ((user_ext-vip_level));插入一条带 JSON 的订单数据INSERT INTO orders (order_no, user_id, status, total_amount, items, user_ext, logistics) VALUES ( 20250101001, 1001, 1, 299.00, [{sku_id: P001, name: 无线键鼠套装, price: 199.00, qty: 1}, {sku_id: P002, name: 显示器支架, price: 100.00, qty: 1}]::jsonb, {vip_level: 3, source: 小程序, tags: [老客, 高潜]}::jsonb, {company: 顺丰, tracking_no: SF123456789, status: 已发货}::jsonb );注意::jsonb这个写法它的作用是字符串转 jsonb。在INSERT语句里即使类型匹配我也习惯显式写一下这个转换遇到非法 JSON 时能第一时间在数据库这一层暴露出来而不是等业务代码运行到中途才报错。4.2 查询与过滤的实战写法现在假设运营想要查买了无线键鼠套装的高等级会员订单SQL 可以这么写SELECT order_no, user_id, total_amount, items FROM orders WHERE items [{name: 无线键鼠套装}] AND (user_ext-vip_level)::int 2;这里有两个值得说的点。第一对数组的包含查询必须写成[{...}]这种数组形式的 JSON不能只写{name: 无线键鼠套装}因为items字段本身是数组类型。第二user_ext-vip_level拿到的是text类型跟整型比较时要显式::int我在生产环境见过因为类型不一致导致查询错误或者索引失效的情况所以这类比较我一般会写清楚。再比如查物流公司是顺丰且已发货的订单SELECT order_no, logistics-tracking_no AS tracking_no FROM orders WHERE logistics {company: 顺丰} AND logistics-status 已发货;4.3 更新与聚合的高级操作更新 JSON 里的单点值用jsonb_set就是最优解-- 把订单里所有 显示器支架 的价格改成 120.00 -- 但 jsonb_set 一次只能改一个路径这里示例先处理下标为1的元素 UPDATE orders SET items jsonb_set(items, {1, price}, 120.00, false) WHERE order_no 20250101001;如果想在数组中插入新元素可以用jsonb_insert-- 在 items 数组的第 2 个位置下标 1插入一个新商品 UPDATE orders SET items jsonb_insert(items, {1}, {sku_id: P003, name: 手机支架, price: 30.00, qty: 2}) WHERE order_no 20250101001;聚合统计 JSON 中的数组数据典型的做法是先jsonb_array_elements炸开再按字段分组-- 统计每个商品被下单的总数量 SELECT item-name AS product_name, SUM((item-qty)::int) AS total_qty FROM orders, jsonb_array_elements(items) AS item GROUP BY item-name ORDER BY total_qty DESC;这里的FROM orders, jsonb_array_elements(items) AS item是隐式 CROSS JOIN相当于对每个订单里的每个商品拆成一行然后按商品名分组。这是一种非常典型的JSON 数组统计写法建议直接背下来。如果想要把某个查询的结果整体作为 JSON 返回给前端就用前面提到的row_to_jsonSELECT row_to_json(t) FROM ( SELECT order_no, user_id, total_amount, items, user_ext FROM orders WHERE order_no 20250101001 ) t;这样接口层拿到这一段 JSON 之后直接丢给前端就行不需要再自己拼一次。5. 常见问题与排查技巧实录5.1 查询慢、走不上索引怎么排查这是我在社区里被问得最多的问题。很多人的情况是JSONB 字段建了 GIN 索引但查询EXPLAIN一看仍然是Seq Scan顺序扫描。首先确认你用的类型是不是jsonb。如果你表里是json类型建索引时 PostgreSQL 根本不会让你成功只能建表达式索引且效果有限。其次确认查询操作符是不是、?这一族。如果用的是-提取文本后做等值匹配GIN 索引不会直接生效你需要建的是表达式索引。最后确认查询条件里是否有函数包裹了 JSONB 字段。比如WHERE lower(info-name) 张三这就变成了对lower()的结果做比较和普通表达式索引不一定匹配上。解决办法是建lower((info-name))的函数表达式索引。-- 查询常见写法 EXPLAIN ANALYZE SELECT * FROM orders WHERE items [{name: 无线键鼠套装}]; -- 如果走了索引执行计划里应该能看到 Bitmap Index Scan 或 Index Scan5.2 jsonb_set路径不存在时到底会不会新增官方文档对这个问题的说明很关键jsonb_set的第四个参数create_missing默认是true也就是路径不存在时自动创建。- 如果 create_missing 是 true默认且路径中某个中间对象不存在则创建 - 如果 create_missing 是 false目标路径不存在则函数直接返回原值不会报错。这句话翻译成人话你要是没有把create_missing显式设成false那么你本意是改一下已存在的字段但如果这个字段不存在数据库会悄悄帮你把它加上。这个行为在用到更新日志类 JSON时还好但如果是面向用户的配置表就会多出一条你压根没打算加的配置项最后接口返回的数据就会莫名其妙多出字段。我这边的处理习惯是所有更新已存在字段的场景一律显式传falseUPDATE orders SET logistics jsonb_set(logistics, {status}, 已签收, false) WHERE order_no 20250101001;5.3 NULL与JSON null是两个不同的概念这个坑我踩得很深。PostgreSQL 里的NULL和 JSON 里的null是完全不同的东西SQL 里的NULL表示没有值参与计算时通常结果是NULL。JSON 里的null是一个真实存在的值对应 JSONB 文档里的key: null。如果 JSON 里有{a: null}你用info-a取出来会得到 SQL 的NULL不是字符串null也不是 JSON 的null。于是问题来了你在 WHERE 里写info-a IS NULL查到的是键存在但值为 JSON null的记录同时也会查到键根本不存在的记录。如果你想区分这两种情况需要这样写-- 键存在且值为 JSON null SELECT * FROM test_json WHERE info ? a AND info-a IS NULL; -- 键不存在 SELECT * FROM test_json WHERE NOT (info ? a);这两条 SQL 看着像废话但在线上排障时因为 NULL 语义不清而整天报数据对不上的人太多了。记住一个原则JSON 字段取出来的文本值如果为空先分清楚是键不存在、值为 null还是值为空字符串再决定业务判断怎么写。5.4 从 MySQL 或 Oracle 迁移过来的注意点从其他数据库迁到 PostgreSQLJSON 部分最容易出问题的有三个地方第一个是路径写法。MySQL/Oracle 用$.a.bPostgreSQL 用{a,b}。这个在写代码的时候容易惯性用错建议提前组内统一封装一层工具函数避免每个开发都踩一遍。第二个是函数名差异。MySQL 的JSON_SET、JSON_EXTRACT和 PostgreSQL 的jsonb_set、-不能一一对应尤其嵌套数组的下标语义要仔细对。第三个是数据迁移时的格式校验。PostgreSQL 对 JSON 的严格程度高于 MySQL 5.7 早期版本如果源库里有不合规的字符串比如单引号、结尾多逗号到 PG 里::jsonb转换会直接报错。我建议迁移前先做一遍全量校验SELECT id, info FROM 旧表 WHERE NOT (info::jsonb IS NOT NULL); -- 这个写法不严谨更推荐自写函数更标准的做法是写个 DO 块循环检测或者用jsonb的目标表直接插入报错的记录捞出来手工处理。反正不要想着一次导完数据多时分批导遇到不符合 JSON 规范的记录单独成文件处理。还有个容易忽略的点navicat等客户端工具导出 PG 表的 JSONB 数据时可能会自动加上不少转义符导致导出的 SQL 文件导入时 JSON 结构出问题。我的经验是导出时选择包含 INSERT 语句然后人工抽查几条尤其关注{、、\的转义是否正常。如果你发现导出后 JSON 数据格式不对优先检查客户端版本和连接驱动版本老版本的驱动对 jsonb 的支持是有 bug 的。5.5 JSON校验、格式化与外部工具配合开发时最常做的事之一就是校验一段 JSON 到底哪里错了。直接丢进数据库::jsonb如果报错PostgreSQL 给出的错误信息有时候很隐晦只告诉你invalid input syntax for type json不说具体哪一行。这时候我一般先放到格式化工具里跑一遍。网上搜json 格式化工具随便找一个把 JSON 贴进去格式化工具会提示你第几行第几个字符出了问题比直接问数据库高效得多。另外如果你用 AI 工具做开发比如跟某个大模型对话让它生成 JSON 配置返回的内容偶尔会出现前有解释文字后有多余符号的情况这时候把内容直接粘到格式化工具里做一次合法性校验能节省非常多联调时间。5.6 版本差异12、13、15、16等版本怎么选最后聊一下版本差异。PostgreSQL 的 JSON 功能在 9.4 引入jsonb9.5 加了jsonb_set12 开始 JSONB 的路径操作有较大增强14 的时候支持了下标语法15、16 更多是性能和 JSON 生成函数上的改进。以jsonb下标语法为例14 可以直接这样改UPDATE test_json SET info[age] 20 WHERE id 1;这在低版本里是语法错误。所以如果你在文档上看到某个写法在自己版本上报错先确认版本是否支持。从我的建议来说新项目直接上 14 以上的版本最好 15 或 16JSON 相关功能成熟性能和生态都更稳。另外安装方面Windows 下如果不想用官方安装包网上也能找到离线安装包但一定要认准 EDB 官方编译的版本别用来路不明的第三方编译包。Linux 下用发行版的官方仓库装最省事或者用 PostgreSQL 官方提供的 APT/Yum 仓库。社区版和商业版在 JSON 功能上没有区别不用担心功能阉割问题。我可以负责任地说JSONB 是 PostgreSQL 所有数据类型里投入产出比最高的一个。你不需要额外部署任何组件不需要改表结构就能获得一个相当完整的文档数据库能力。把上面这些操作符、函数和索引方案用熟大部分业务里的灵活存储需求都可以用它解决得干干净净。我在实际项目里的体会是JSONB 的最大价值不是替代关系型建模而是给关系型建模留了一个弹性出口。凡是模型稳定、查询频繁的字段老老实实建成列凡是模型易变、低频过滤的字段丢进 JSONB。用这种混合策略表结构清晰查询性能也有保障。最后再分享一个小技巧给 JSONB 字段设计的时候尽可能用统一的键名风格比如全部小写加下划线。不要今天存userName明天存user_name因为 JSONB 里这两个键是完全不同的不统一的话排查数据问题时会非常痛苦。定好规范大家一起守这个字段用起来就舒服多了。