PostgreSQL里的JSON字段官方文档其实写得已经很系统了但几乎每隔一段时间就会有人在技术群里把同样的问题再问一遍json和jsonb到底选哪个为什么我用json建不了GIN索引官方文档里说jsonb比json好那json存在的意义是什么这些问题如果只抱着文档啃确实容易绕晕。我花了大半年时间把JSON类型用在一个日增百万条事件数据的生产系统里期间反复查过8.14节JSON类型和9.16节JSON函数与操作符今天就把这些官方文档说明梳理成一份能直接落地的实操笔记从类型差异到索引机制再到真实业务里的坑一次说清楚。1. 官方文档为什么要把JSON拆成两种类型json和jsonb的差异全解很多第一次看PostgreSQL官方文档的人都会产生一个疑惑明明支持JSON为什么要搞出json和jsonb两个类型这不是给自己找麻烦吗官方文档给的解释很直白两者接受几乎相同的输入但在存储方式、查询效率、功能覆盖上有着本质差别。理解这一点所有后续的选型和踩坑都有了解释。1.1 从一次线上事故说起乱码字符与重复键去年我们团队接手了一个老项目里面用json类型存配置数据运行了两年一直没出大问题。直到一次接口排查我们发现某条记录里的配置项出现了重复键而且查询时返回的顺序和插入时不一样。当时第一反应是代码有bug翻了一下午日志最后才意识到是json类型在作怪。这里要先说清楚一个非常容易忽略的文档细节json类型保存的是输入文本的精确拷贝每次处理时都需要重新解析。它保留了键的原始顺序也保留了重复键——同一个键出现两次两条都留着。jsonb则完全不同它在输入时就把文本解析成二进制格式重复键只保留最后一个键的顺序也不保证内部会按长度和字节序重新排序。所以官方文档里那句话含义很深“在大多数应用场景下几乎总是应该优先使用jsonb除非你确实对键的顺序存在依赖。”我们当时踩的坑正是因为老项目为了避免重复解析性能问题用了json结果被重复键和顺序问题反噬。文档第8.14节明确列出了这两种类型的取舍关系这不是一个简单的“哪个好”的问题而是“你的业务到底能不能放弃文本原貌”。1.2 json与jsonb的底层存储差异逐条对照我按照官方文档的说明把两者的核心差异整理成了一个对照表基本上涵盖了面试和实际选型最关心的几个维度特性jsonjsonb存储方式输入文本的精确拷贝解析后的二进制格式空格、缩进、格式完整保留不保留输入即丢弃重复键保留所有重复项只保留最后一个值键顺序保留原始顺序不保证按长度和字节序排序数值格式保留原始格式如1.0存储为数值比较时1和1.0相等索引支持不支持直接建索引支持GIN索引查询时解析每次都要重新解析直接读取已解析结构NaN和Infinity允许不允许大小通常较小通常较大这个表里最容易被忽略的是最后几行。NaN和Infinity这个问题官方文档是用一句话带过的——jsonb不支持这两个数值。但如果你真的在业务里插入了一个InfinityPostgreSQL会直接报错生产环境遇到这种问题往往很突然。数值格式这块也值得一提json保留1.0这样的写法jsonb会比较时把1.0和1视为相同数值但如果你依赖“查询出来必须是1.0”这样的文本格式jsonb会给你的展示层造成麻烦。还有一个性能层面的差异json因为不解析插入时更快但查询时每次都要解析所以频繁读取的场景反而是jsonb占优。文档里虽然没有明确给出jsonb一定更快的结论但从GIN索引、包含操作符、路径提取这些功能的支持情况来看官方对jsonb的偏爱是明摆着的。1.3 官方的倾向性建议到底该选谁官方文档有一段话虽然表述比较含蓄但指向性非常明确“如果需要依赖键顺序或需要保留重复键或需要保留原始文本的精确格式用json否则用jsonb。”这段话其实已经给99%的业务定了方向——大部分业务查询、索引、统计、聚合都需要jsonb的能力。我个人的经验是几乎不用json类型除非两个极端场景第一是ETL管道中从外部系统拿到的JSON文本需要原封不动转发到下游中间不允许有任何键顺序或数值格式的改动第二是日志归档表JSON只是被存起来常年没人查万一以后要还原当时的原始报文json比jsonb更保真。就算遇到这两种场景我也会在建表时把json字段和jsonb字段并存一个做原始归档另一个做查询分析。这样既保留原文又能享受jsonb的索引和查询能力代价只是多一份存储。官方文档虽然没给这种方案但这是在“原文保留”和“查询性能”两难之间最实用的折中。2. 官方文档中的JSON操作符与函数查询、聚合、构造的完整地图JSON类型真正拉开和其他数据库同功能差距的是操作符体系和函数库。官方文档第9.16节列出的JSON函数与操作符数量很大但很多人在实际开发中只用过取字段的箭头操作符这远远没有发挥PostgreSQL JSON能力的上限。2.1 取字段的三板斧- 、-和#路径查询先讲最基础的取值操作符。-按键取JSON字段或按下标取数组元素返回的是JSON类型-干同样的事但返回的是text文本。这个区别看似不起眼实际影响极大。-- 返回JSON类型可用作进一步JSON操作 SELECT ({name: 张三, age: 30}::jsonb)-name; -- 返回text类型适合直接用于比较或拼接 SELECT ({name: 张三, age: 30}::jsonb)-name;如果字段嵌套层级比较深-可以连续调用但表达式会变得很长。这时候#和#就派上用场了它们接受一个路径数组-- 等价的两种写法 SELECT ({user: {address: {city: 上海}}}::jsonb) # {user, address, city}; SELECT ({user: {address: {city: 上海}}}::jsonb) - user - address - city;官方文档里的路径数组是一个很优雅的设计。# {user, address, city}返回JSON类型# {user, address, city}返回text类型。我推荐在函数内部或动态SQL中尽量用#因为它能避免-链式调用中的类型混淆也更容易和jsonpath表达式对齐。2.2 判断与包含jsonb独有的四大关系操作符遇到“这个JSON里有没有某个键”“这个数组是否包含那个对象”这类需求很多人的第一反应是把JSON解析成text然后用LIKE去匹配。这是一个非常危险的习惯效率低不说还容易误判——比如你要查has_key true用LIKE可能匹配到has_key true以外的字符串。jsonb提供了四个官方文档重点介绍的关系操作符操作符含义示例左侧是否包含右侧{a:1}::jsonb {a:1}::jsonb右侧是否包含左侧{a:1}::jsonb {a:1}::jsonb?键是否存在{a:1}::jsonb ? a?任一键存在?所有键都存在{a:1}::jsonb ? array[a,b]这里面最有价值的是它支持嵌套对象的包含判断。比如上面表里写的?|和?是做标签系统、权限系统时的利器。我做过一个用户标签筛选功能标签存储在用户的jsonb字段里筛选逻辑就是一个?操作符一条SQL搞定连表都不用拆。关于和的包含语义官方文档特别强调了一条边界当右侧是数组时检查的是数组是否包含某个元素而不是子数组。这个边缘情况如果不看文档很容易写出错误的筛选条件。2.3 生产级操作函数jsonb_set、jsonb_each、jsonb_agg取和判是“读”改和展开是“写”与“分析”。官方文档里这几个函数是我在业务中使用频率最高的给每个都配了实战场景。jsonb_set修改JSON中的某个字段UPDATE products SET attributes jsonb_set(attributes, {color}, red, false) WHERE id 100;第四个参数create_missing决定字段不存在时是否自动创建。官方文档对这个参数的说明是“如果为true且字段不存在则添加新字段”这个参数在生产中很容易被忽略。我见过一个同事写存储过程时把false给漏了结果字段不存在时直接保持原值不动排查了很久才发现是jsonb_set没生效。jsonb_each把JSON对象展开成行SELECT key, value FROM products, jsonb_each(attributes) WHERE id 100;这个函数在动态属性归档、统计键值分布时特别有用。配合GROUP BY可以统计所有属性的出现频率SELECT key, count(*) FROM events, jsonb_each(payload) GROUP BY key ORDER BY count(*) DESC;jsonb_agg / jsonb_object_agg聚合构造JSONSELECT category, jsonb_agg(name ORDER BY name) AS names FROM products GROUP BY category;这类聚合函数在很多报表需求中能省掉大量的编程循环。官方文档还提供了jsonb_build_object、jsonb_build_array等构造函数适合在SQL里动态组装JSON而不是先查出来在应用层拼。文档里还有一组容易和jsonb_each混淆的函数jsonb_array_elements和jsonb_array_elements_text它们是把JSON数组展开成行。LATERAL连接配合这组函数可以实现“JSON数组里的每个元素去关联另一张表”这种写法比应用层遍历再查数据库要高效得多SELECT t.id, item-sku AS sku FROM orders t, LATERAL jsonb_array_elements(t.items) AS item WHERE item-sku A001;2.4 SQL/JSON路径表达式jsonpath带来的新玩法PostgreSQL 12开始引入jsonpath官方文档对此的篇幅非常大。简单说jsonpath是一种类XPath的表达式语言用来在JSON内部做模式匹配和提取。-- 提取books数组里价格低于10的书名 SELECT jsonb_path_query( {store: {book: [{title: A, price: 8.95}, {title: B, price: 12.99}]}}, $.store.book[*] ? (.price 10) );这个能力在最开始用的时候会有豁然开朗的感觉。以前要写复杂的PL/pgSQL循环遍历数组现在一个路径表达式就搞定了。jsonpath的类型感知也做得好? (.price 10)里的price会比较成数值不会因为字符串和数值混在一起出问题。但jsonpath也有明显的性能陷阱如果在一个没有GIN索引的jsonb字段上执行jsonb_path_query会做全表扫描比-加上普通B-tree索引慢得多。我的建议是jsonpath适合做探索性分析和复杂结构提取如果是高频查询尽量用操作符加索引的方式把jsonpath留给那些动态条件特别复杂的场景。3. jsonb字段的GIN索引机制官方文档里那几张索引对比图官方文档在介绍GIN索引时专门用jsonb举过例子。很多人以为给JSON建索引就是把整个字段放进GIN这没错但文档里还有更深一层的内容——默认的GIN操作符类和jsonb_path_ops操作符类到底怎么选直接影响索引大小和能支持的查询。3.1 默认GIN索引能加速哪些操作CREATE INDEX idx_gin_attrs ON products USING gin (attributes);默认的GIN索引jsonb_ops操作符类支持、?、?|、?四种操作符。也就是说建了这棵索引之后判断“字段存在”“任一键存在”“包含某个JSON片段”都会走索引。我在实际使用中总结的经验是键存在性查询适合用GIN索引而值比较查询更适合用表达式索引。原因很简单GIN索引的核心数据结构是倒排表它对“某个键是否出现”这类布尔性质的判定有天然优势但如果要查的是attributes-brand Apple这种等值或范围查询GIN索引帮不上忙只有表达式B-tree索引能效最大化。3.2 jsonb_path_ops更小更快的替代方案官方文档有一组对比数据原话大致是“jsonb_path_ops索引通常是jsonb_ops索引四分之一大小性能也更好”。我第一次在测试环境对比时同样的100万行数据默认GIN索引占地480MBjsonb_path_ops只有110MB差距相当可观。CREATE INDEX idx_gin_attrs_path_ops ON products USING gin (attributes jsonb_path_ops);代价是jsonb_path_ops只支持操作符不支持?、?|、?。这意味着如果你做了一个标签筛选功能核心查询是?操作符那就不能用jsonb_path_ops。我的建议是分场景处理如果字段主要是嵌套对象且查询主要是包含判断直接用jsonb_path_ops如果既有包含判断又有键存在判断默认GIN更稳妥。也可以两个索引都建PostgreSQL会自动选择代价低的那个——代价是写放大插入和更新会慢一些生产环境需要权衡。3.3 表达式索引给特定键加索引的正确姿势最常被问到的问题是我想让attributes-brand这个查询走索引怎么做答案是表达式索引CREATE INDEX idx_products_brand ON products ((attributes-brand)); -- 查询时写法必须与表达式完全一致 SELECT * FROM products WHERE attributes-brand Apple;这里有一个非常容易掉进去的坑如果查询时写了(attributes-brand) Apple而索引表达式是attributes-brandPostgreSQL优化器大多数情况下都能匹配但如果你在-和-之间来回切换类型比如attributes-brand Apple::jsonb索引大概率就不会被命中。我的实践经验是统一使用-表达式建索引查询条件也统一使用-不要混合写。这看起来像个风格问题实际上决定了索引是否命中。之前我做过一次性能优化把某个报表接口从全表扫描优化到索引扫描核心改动就是统一了几处-和-的写法耗时从3秒降到200毫秒。4. 从官方文档到生产落地JSON字段设计的完整示例光讲函数和索引还是太散我直接用一个产品目录事件流的完整设计案例把前面提到的所有知识点串起来。这个案例是我在生产环境真实用过的结构稍作简化后分享出来照着设计基本不会有大问题。4.1 动态属性表怎么建才不后悔电商系统的商品有大量非固定属性手机有颜色、内存、芯片衣服有尺码、材质、洗涤方式。把这些属性全部做成关系型字段表结构会无限膨胀而且每个分类都是稀疏矩阵——大量字段为空。用jsonb做动态属性是最合理的方案。CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, name text NOT NULL, category text NOT NULL, attributes jsonb NOT NULL DEFAULT {}::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); -- 高频属性走B-tree表达式索引 CREATE INDEX idx_products_brand ON products ((attributes-brand)); -- 低频但需要包含查询的属性走GIN索引 CREATE INDEX idx_products_attrs_gin ON products USING gin (attributes);这张表的核心设计思路是把访问频率高、数据类型稳定的字段品牌、价格抽取出来建表达式索引把探索性强的、结构高度自由的字段放进GIN索引统一检索。建表看似简单但索引策略直接决定了后续两年业务迭代时查询性能的上限。4.2 更新JSON字段的三种姿势与性能差异更新JSON字段不是一个统一的动作不同姿势的代价完全不同。骨子里要知道对jsonb字段的任意更新实际都是整行替换而不是局部修改。所以更新频率越高的业务越应该掂量JSON字段值的大小。第一种整体覆盖UPDATE products SET attributes {brand: Apple, color: black} WHERE id 1;最直接但只适合字段值很小的场景。第二种jsonb_set局部更新UPDATE products SET attributes jsonb_set( attributes, {color}, black, false ) WHERE id 1;适合只改一两个子键的场景虽然底层仍然是整行替换但SQL层面表达清晰是日常开发中效率最高的写法。第三种删除键UPDATE products SET attributes attributes - old_key WHERE id 1; -- 数组元素删除用下标或路径 UPDATE products SET attributes attributes #- {tags, 0} WHERE id 1;官方文档中-操作符用来删除键#-用来删除路径指定位置。这个操作符容易被忽略很多人删除键时还在用jsonb_set把值设成null其实需求是“去掉这个键”这完全是两回事。设成null保留键删掉键用-语义必须区分清楚。性能上要特别注意jsonb字段如果比较庞大几百KB甚至几MB频繁局部更新会导致表膨胀和索引维护开销上升。官方文档建议对大对象设置恰当的toast压缩阈值实际中我建议把超过10MB的JSON拆到独立的扩展表按主表ID一对一关联避免每次更新都拖着大字段扫描。4.3 CHECK约束给JSON结构上保险jsonb是弱结构的但这不是我们随便往里塞数据的理由。官方文档明确提到可以对jsonb字段加CHECK约束确保核心结构在数据库层不跑偏。ALTER TABLE products ADD CONSTRAINT products_attrs_price_is_number CHECK (jsonb_typeof(attributes-price) number); ALTER TABLE products ADD CONSTRAINT products_attrs_brand_is_text CHECK (jsonb_typeof(attributes-brand) string);这样做的好处是应用层漏校验的脏数据会在数据库层被直接拦截。我曾经在一个用户画像系统里加过类似的CHECK约束上线第三天就拦下一批空字段订单。这些约束的价值平时看不见但一旦出问题能省下你一个通宵的排查时间。同时从PostgreSQL 12开始生成列GENERATED ALWAYS AS也是一个好伙伴它能把JSON字段里的高频键自动映射成普通字段对外提供稳定的列访问方式CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, payload jsonb NOT NULL, event_type text GENERATED ALWAYS AS (payload-event_type) STORED, occurred_at timestamptz GENERATED ALWAYS AS ((payload-occurred_at)::timestamptz) STORED );生成列不能写在JSON和普通关系型字段之间架一座桥让外部系统不感知JSON内部结构同时内部还能享受jsonb的灵活性。我在好几个项目里都是这么设计的效果非常好。5. 官方文档没明说但实战中反复踩的坑官方文档把功能写得清清楚楚但真正到了生产环境有一些边界情况是文档没展开、或者藏在小字里的。这部分我用自己的真实经历说话每一个都是花钱买来的教训。5.1 索引命中失败表达式不匹配前面在表达式索引那节提到过索引是否命中取决于查询表达式是否和索引表达式匹配。这里给一个更完整的例子-- 这种情况索引可能失效 CREATE INDEX idx_events_user_id ON events ((payload-user_id)); SELECT * FROM events WHERE payload-user_id 1001; -- 正确写法 SELECT * FROM events WHERE payload-user_id 1001;payload-user_id返回JSON类型payload-user_id返回text类型两者的比较语义完全不同优化器也不能混用索引。类似的坑还出现在模糊查询里——LIKE %xxx%在GIN索引的text_pattern_opclass下有一定支持但如果你用了ILIKE索引命中路径又会变。总之表达式的匹配是“写法必须完全一致”差一个符号都不行。5.2 jsonb的数值精度与NaN/Infinity限制官方文档在jsonb类型说明里有一句话很多人看到就跳过去了jsonb不允许NaN和Infinity。意思是-- 这个会报错 SELECT {value: Infinity}::jsonb;生产上一个监控系统推送指标时某个指标恰好算出Infinity直接导致写入任务失败整条管道阻塞了半小时。排查时才发现是jsonb的限制。json类型反而可以存因为它本质上只是文本。对于数值精度jsonb存储时按照numeric规则处理高精度数值可能和你期望的IEEE 754浮点数结果有差异。我的建议是不要在JSON字段里存储需要复杂运算的高精度数值把金额、比例等对精度敏感的数据抽成专门的numeric列JSON字段只存展示用的近似值。5.3 大JSON文档更新导致表膨胀这是最隐蔽的一个坑。前面提到jsonb更新是整行替换如果一行数据有几百KB的JSON字段那么每一次更新都会产生大量WAL日志频繁更新后表的膨胀速度快得惊人。我曾经维护过一张订单扩展表一天更新几十万次结果磁盘占用在两周内翻了三倍。官方文档对TOAST和VACUUM有原理性说明但没直接告诉你“不要在JSON字段上做高频更新”。解决思路有两个要么把不常变的部分和常变部分拆到两个JSON字段要么把更新操作批量合并降低更新频率。热点数据在JSON里低频更新、把频繁变化的数据抽成独立列是治本的方向。5.4 重复键和键顺序引发的“灵异现象”第1章提过的重复键问题实际发生时的表现很像代码bug。比如外部系统推送的JSON里有重复键你用jsonb接收最后只保留最后一个用户反馈拿到的是旧值你反复查接口没问题最后发现是你用jsonb解析时丢了前面的值。如果是用json类型接收再显式转成jsonb情况更迷惑-- 先存成json再转jsonb重复键在这里静默丢失 SELECT {a: 1, a: 2}::json::jsonb;结果只有{a: 2}。官方文档对这种丢失是有提示的但它出现在描述jsonb“唯一键约束”的上下文里不仔细看根本意识不到这是丢数据。处理外部系统对接时我会在接入层用json类型先接收原始报文然后用jsonb_path_query等函数主动检查重复键确认无冲突后再写进jsonb业务字段。还有一个顺序问题jsonb输出时不保证键顺序PostgreSQL内部会按长度排序。如果你的接口对字段顺序有严格约定比如签名校验要求按原始顺序拼接字符串用jsonb存储就是给自己挖坑。正确做法是签名校验放在接入层用json原始文本完成数据库层用jsonb存解析后的数据两者互不混淆。5.5 NULL和JSON null之间的语义混淆最后提一个所有JSON开发都该知道的细节SQL里的NULL和JSON里的null是完全不同的概念。官方文档在描述jsonb_typeof函数时顺带提过这一点但实际踩到的概率极高。-- payload里的是个JSON null不是SQL NULL SELECT * FROM events WHERE payload-key IS NULL;这个查询查不到任何东西因为payload-key返回的是JSON null而不是SQL NULL。要判断JSON字段是否为null正确写法是SELECT * FROM events WHERE jsonb_typeof(payload-key) null;从PostgreSQL 16开始官方提供了IS JSON NULL语法算是官方对这类语义混淆的正式回应。但在16版本全面普及前jsonb_typeof仍是最稳妥的判断方式。这类问题在业务上非常致命——它不会报错只是静默地查不出数据等你发现异常时数据链路已经被污染很久了。用一句话概括我这段时间的使用体会PostgreSQL的JSON字段是关系模型和半结构化数据之间的一座桥梁但它不是万能的。把json和jsonb的分工搞清把GIN索引、表达式索引、jsonpath的适用边界搞清把更新和约束的设计搞清你才能在这座桥上跑得又稳又快。官方文档永远是最权威的参照系第二优先的就是多看几个真实场景下的反面案例——这篇文章里的每个坑都是我先替你踩过的。