手头各种数据七七八八塞进PostgreSQL最后发现关系表怎么设计都别扭——字段要么提前预留一堆空列要么为了兼容不同业务含义搞出几十个扩展字段——这时候你就该认真考虑用JSON字段了。我在项目里把大量灵活属性、接口原始报文、配置快照都迁移到了JSON字段上配合PostgreSQL的JSONB类型和GIN索引既保住了灵活性又没牺牲查询效率。这篇就把我在PostgreSql里用JSON字段的完整经验拆开讲清楚从数据类型选型、日常增删改查、索引优化到和MySQL的差异对比、排错实录一次性给你梳理明白。1. 先泼盆冷水JSON字段从来不是用来替代关系模型的很多初学者一看到PostgreSQL支持JSON就像拿到新玩具一样恨不得把所有表都加一个JSON字段把本该拆表的业务数据全部塞进去。我在实际项目里见过最夸张的情况是有人把订单明细全部塞进一个JSON数组单条记录几十KB查询时靠应用层遍历性能直接崩掉。所以开篇必须先把这个认知掰正。1.1 什么时候用JSON字段是合理的设计从我的实践经验看JSON字段真正擅长解决的是三类问题。第一类是动态属性。比如电商的商品表不同品类商品的属性完全不一样——手机要存运存、屏幕尺寸、电池容量衣服要存尺码、面料、洗涤方式。如果建关系表要么搞EAV模式实体-属性-值查询灾难要么为每个品类建单独的表表结构变更频繁。这时候用JSON字段存attributes查询用GIN索引反而最干净。第二类是异构数据存储。比如第三方接口的原始报文、爬虫抓取的页面结构化数据、配置文件的快照这类数据你可能只是完整存下来备查偶尔按某个键过滤对结构完整性要求低但灵活性要求高。关系表无法在不频繁迁移的情况下容纳这样的数据JSON字段天然合适。第三类是软模式演进。早期需求不明确字段随时可能增加或调整用JSON字段能减少ALTER TABLE的操作次数。我习惯在核心关系表里保留一个extra JSON字段专门承接业务方后来加的散装需求等某类属性稳定下架再把它提升为正式列。1.2 什么时候千万别用JSON字段判断标准其实很简单如果你要基于JSON里的字段做频繁关联、排序、聚合计算或者数据量很大且需要强约束那就别用JSON字段。例如订单表如果你的业务经常需要按订单里的收货人姓名做JOIN查询、做分组统计、建立外键关系这些业务放在JSON字段里就会非常痛苦。虽然PostgreSQL支持表达式索引理论上可以对JSON里的字段建索引但它的类型检查和约束能力远不如真正的列字段。在涉及资金、库存这类需要严格一致性的核心链路能用强类型列就用强类型列。还有一点我吃过亏的JSON字段的变更历史是隐形的。关系表的列有注释、有类型约束、有迁移记录JSON字段里的键却没有任何注释和约束时间一长根本没人知道某个键代表什么、值是什么格式。我曾经接手过一个系统JSON字段里同一个含义的键名有四种写法数据质量惨不忍睹。所以如果要用JSON字段一定要在应用层维护一份字段字典文档把每个键的含义、类型、取值范围写清楚否则迟早还债。2. json和jsonb到底选哪个这一步错了后面全白搭PostgreSQL提供了两种JSON数据类型json和jsonb。很多人不太在意随便选一个结果后面索引建不上、查询慢、结果顺序和预期不一致各种问题翻车。我直接给结论99%的场景用jsonbjson类型只在你需要保留原始输入文本原样输出时才有存在价值。2.1 存储机制的核心差异json类型是原样存储的。你传入什么文本数据库就原样保存空格、键的顺序、重复的键、数字的表示法1还是1.0都会保留。每次查询和操作时数据库需要现场解析文本把JSON文本解析成内部结构再操作所以json类型的查询效率天然低于jsonb。但好处是输出结果和你存入时一模一样。jsonb类型则完全不同。输入时会做一次解析和规范化处理将JSON文本转换成一种分解的二进制格式存储。这个处理过程会去掉键之间的冗余空格、重新排序键按长度和字节顺序调整、移除重复键保留最后一个值、把数字规范化1和1.0会统一为1。存储成本稍高一点但换来的是查询时无需重复解析还支持GIN索引加速这是json对标的优势所在。我举一个特别容易踩的例子你插入字符串{b: 1, a: 2}到jsonb字段再SELECT出来结果会变成{a: 2, b: 1}。键的顺序变了如果你有地方是拿字段值做字符串拼接或做签名校验的这里的差异会让你排查到怀疑人生。2.2 功能支持的差异jsonb的功能全面领先能力项jsonjsonb存储格式文本原样二进制解析后GIN索引支持不支持支持删除键/更新键不支持支持、?、?、?操作符不支持jsonb_set更新函数无支持键顺序保留自动排序不保证输入解析即时解析写入时解析在PostgreSQL 12以后json类型也支持了部分JSONPATH查询但索引支持依然只有jsonb才有。所以结论很简单默认选jsonb。2.3 类型转换的几个实用写法日常开发中json和jsonb之间的转换很常见直接CAST就可以-- json 转 jsonb SELECT {name: postgres}::json::jsonb; -- jsonb 转 json基本不常用除非对接接口要求原样输出 SELECT {name: postgres}::jsonb::json; -- 格式化输出排查问题时很好用 SELECT jsonb_pretty({name: postgres, info: {port: 5432}}::jsonb);jsonb_pretty这个函数我强烈推荐配合psql或者数据库客户端查看大JSON时非常直观。3. 查询JSON字段-、-、#这些操作符怎么用才不出错JSON字段的查询占据日常操作80%的内容。PostgreSQL提供了一组强大的操作符但很多开发者经常因为返回类型搞混而写出报错SQL。我按使用频率逐一说明。3.1 - 和 -取单个键值的两个层次这是最常用的两个操作符也是很多人一开始最容易搞混的-- - 返回JSON类型保留类型信息取出来的值还是JSON SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三注意带双引号类型是JSON字符串 -- - 返回文本类型 SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三不带引号类型是text这个区别在WHERE条件里特别致命。比如你要查age大于18的记录-- 错误写法- 返回jsonb类型无法和整数比较 SELECT * FROM users WHERE info - age 18; -- ERROR: operator does not exist: jsonb integer -- 正确写法- 返回text必须隐式转成数值 SELECT * FROM users WHERE (info - age)::int 18;类似的在SELECT投影列表中如果你在外面做字符串拼接、做大小写转换、做区间比较基本都要用-。还有一种场景是从JSON数组里取元素同样可以用-和--- 取数组第一个元素 SELECT [{name: A}, {name: B}]::jsonb - 0; -- 结果: {name: A} -- 取数组第一个元素后取name字段 SELECT [{name: A}, {name: B}]::jsonb - 0 - name; -- 结果: A3.2 # 和 #路径查询省掉一串级联箭头当JSON嵌套层级深的时候连续使用-会把SQL写得又长又容易错。PostgreSQL提供了路径操作符-- # 按路径返回JSON类型 SELECT {a: {b: {c: [10, 20]}}}::jsonb # {a,b,c}; -- 结果: [10, 20] -- # 按路径返回text类型 SELECT {a: {b: {c: [10, 20]}}}::jsonb # {a,b,c,0}; -- 结果: 10路径语法用大括号或方括号里的字符串数组表示每一层的键名或数组下标。注意用这种写法时路径中间任何一级不存在返回的是NULL不是报错。在写一些防御性查询时反而方便。3.3 存在性判断、?、?|、?这是jsonb查询最爽的部分也是关系表很难做到的地方。这几个操作符专门用于判断JSON里是否存在指定的键或值配合GIN索引性能极佳。-- ? 判断某个键是否存在于顶层 SELECT {name: 张三, age: 30}::jsonb ? name; -- 结果: true -- ?| 判断键数组中任意一个存在 SELECT {name: 张三}::jsonb ?| array[name, email]; -- 结果: true -- ? 判断键数组中全部存在 SELECT {name: 张三, age: 30}::jsonb ? array[name, age]; -- 结果: true -- 判断左侧JSON是否包含右侧JSON支持嵌套 SELECT {name: 张三, address: {city: 北京}}::jsonb {address: {city: 北京}}; -- 结果: true这个操作符是包含语义右侧必须是左侧的子集。它不仅能判断键是否存在还能判断嵌套值是否相等。这个特性在做标签筛选、条件匹配、权限校验时非常好用。比如用户表里有一个tags JSON字段需要筛选同时带有VIP和高活跃标签的用户SELECT * FROM users WHERE tags [VIP, 高活跃]::jsonb;3.4 在表和表之间用JSON字段做关联很多人在 JOIN 场景里遇到JSON字段就犯难其实思路和普通列一样只是要把键值解析出来再关联。我做过一个比较典型的案例订单表里有JSON字段存了用户手机号用户表里有手机号列要查出订单对应的用户信息SELECT o.order_no, u.nickname FROM orders o JOIN users u ON u.phone o.extra_info - phone WHERE o.created_at 2024-01-01;这种关联查询能用但需要注意它本质上相当于在关联条件里对JSON字段做了解析计算如果订单表很大性能瓶颈会很明显。这时候就应该考虑对(extra_info - phone)这种表达式建索引后面索引部分会详细说。4. 修改JSON字段jsonb_set和删除键的正确姿势PostgreSQL里JSON字段的更新和普通行更新有本质区别。因为JSONB是整体存储在行里的你每次修改JSON里的某个子字段实际上都是取出整个JSON、修改后再整体写回。理解这一点你就能明白为什么频繁更新大JSON字段会导致表膨胀和性能下降。4.1 用jsonb_set更新嵌套键值jsonb_set的完整语法jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true)path表示要更新或创建的键路径new_value是要设置的新值必须是json/jsonb类型create_missing控制当路径不存在时是否自动创建。一个典型的更新例子-- 更新specs.ram的值 UPDATE products SET attributes jsonb_set(attributes, {specs,ram}, 12GB::jsonb, true) WHERE id 1001;注意一个常见的坑new_value参数必须是JSON格式。如果你想写字符串必须带引号即12GB::jsonb如果你写成12GB::jsonb数据库会报错因为12GB不是合法的JSON文本。我在项目里经常看到有人在这里困惑报错信息是invalid input syntax for type json。数字类型可以直接写18::jsonb布尔类型写true::jsonb对象和数组写{key:value}::jsonb。如果你要更新的字段可能存在也可能不存在把第四个参数设为true是最省事的。但如果你希望严格模式下报错可以设为false。4.2 删除键- 和 #- 操作符删除JSON字段的键用-操作符-- 删除顶层键 UPDATE products SET attributes attributes - color WHERE id 1001; -- 数组按下标删除删除数组第二个元素 UPDATE products SET attributes attributes - 1 WHERE id 1001;删除深层键要用#-操作符配路径-- 删除specs下的old_key UPDATE products SET attributes attributes #- {specs,old_key} WHERE id 1001;4.3 数组追加和对象合并的几个便利写法JSONB在拼接方面也提供了一些操作符极大简化了修改逻辑-- 数组追加元素 UPDATE products SET attributes attributes || [新标签]::jsonb WHERE id 1001; -- 对象合并多个键合并更新 UPDATE products SET attributes attributes || {warranty: 1年, color: 黑色}::jsonb WHERE id 1001;注意||对于对象类型是浅层合并只合并第一层键嵌套对象会整体替换而不是递归合并。比如attributes里原本有{specs: {ram: 8GB}}你用|| {specs: {storage: 256GB}}去合并结果是{specs: {storage: 256GB}}ram丢了。如果要做深层递归合并还是得用jsonb_set逐层设置或者写一个递归合并的辅助函数。4.4 更新大JSON的性能注意事项前面提过更新JSON字段是整行读改写。如果一条记录的JSON字段有几十KB甚至更大那么每次只更新里面一个键整个大JSON都会经历序列化、修改、再序列化写回。如果有高并发大对象更新需求会造成行锁竞争、WAL日志写入量暴增、表膨胀最终拖垮性能。我的实践经验是JSON字段控制在KB级别是正常的如果单条要上MB就该考虑JSON字段是否适合存储这份数据了。比如日志明细与其塞在一个JSON数组字段里不如拆出去单独建表一行一条日志利用PostgreSQL的列式存储面向OLAP场景或普通行存做聚合查询。JSON字段做低频更新、低频写入的高价值小对象是它的舒适区。5. 索引优化让JSON字段查询快起来的核心手段在数据量小的时候JSON字段怎么查都很快。但数据量一涨比如到了百万级没有索引的JSON条件查询可能就是一次全表扫描性能直接不可用。PostgreSQL给jsonb提供了专用索引但在实际使用中很多人建了索引却发现没走本质原因是不够了解索引的作用域。5.1 GIN索引jsonb的默认高性能查询方案GINGeneralized Inverted Index索引适用于全文搜索、数组包含等场景对jsonb的、?、?|、?这类存在性操作符有显著的加速效果。创建方式CREATE INDEX idx_products_attributes ON products USING GIN (attributes);我的测试环境里200万条数据中按attributes {brand: Xiaomi}筛选没有GIN索引时全表扫描耗时约1.8秒建了GIN索引后降到40毫秒左右提升非常明显。GIN索引还有一个细化选项jsonb_path_opsCREATE INDEX idx_products_attributes ON products USING GIN (attributes jsonb_path_ops);jsonb_path_ops索引比普通GIN索引更小也更高效但代价是它只支持操作符不支持?、?|、?。如果你的查询模式主要是包含关系判断比如按JSON里的属性组合筛选用jsonb_path_ops是更优选择如果还需要频繁判断键是否存在那就用普通GIN。5.2 表达式索引给JSON内的某个字段建索引GIN索引能解决存在性查询但对排序、范围查询, , BETWEEN、等值匹配-取值后比较就无能为力了。比如我们前面讲的WHERE (attributes - price)::numeric 100这属于在表达式上做范围比较需要建表达式索引CREATE INDEX idx_products_price ON products ((attributes - price));如果你在WHERE条件里经常用(attributes - price)::numeric转成数值再比较建议索引直接建在数值类型上CREATE INDEX idx_products_price_numeric ON products (((attributes - price)::numeric));注意这里两侧的括号别省创建时如果有问题可以检查一下CAST的写法。表达式索引的代价是必须先明确表达式形态在WHERE里写的表达式和索引表达式必须匹配否则不会走索引。5.3 用EXPLAIN确认索引真的生效了索引建了不等于一定生效。操作符比较特殊只有当左侧列上有GIN索引且右侧是常量表达式时优化器才会考虑走索引。如果你的右侧又是一个表的列值情况会变得复杂优化器可能选择全表扫描。我建议每次建完索引后用EXPLAIN ANALYZE实际验证一下EXPLAIN ANALYZE SELECT * FROM products WHERE attributes {brand: Xiaomi};如果看到Seq Scan on products说明没走索引要检查查询语句条件是否匹配索引范围或者数据量太小导致优化器认为全表扫描更快。如果看到Bitmap Index Scan on idx_products_attributes和Bitmap Heap Scan说明索引生效了。我积累的一个经验是数据量低于几万行时全表扫描和索引查询的差距并不大但数据量过百万后索引的价值才会体现出来。所以判断是否建索引先看数据量再看实际执行计划别盲目建一堆索引徒增写入成本。6. 和MySQL的JSON较量从Oracle/MySQL迁过来的人最容易踩哪些坑如果之前主要用MySQL或者Oracle转到PostgreSQL的JSON字段时你会发现概念类似但具体操作方式差异不小。这个对比对团队迁移决策特别重要。6.1 存储和类型的底层差异MySQL从5.7开始支持JSON类型存储上也是解析后的二进制格式这点和jsonb有点像。但MySQL的JSON字段不能有默认值8.0.13之前而且JSON列不能直接建普通索引必须用函数索引MySQL 8.0.13开始才支持对JSON列的表达式索引。PostgreSQL在这方面灵活得多——jsonb支持BTREE、GIN等多种索引策略jsonb字段也允许设置默认值。在查询操作符上差异更明显能力项PostgreSQL (jsonb)MySQL (JSON)包含判断data {a:1}JSON_CONTAINS(data, {a:1})取键值data - aJSON_UNQUOTE(JSON_EXTRACT(data, $.a))键是否存在data ? aJSON_CONTAINS_PATH(data, one, $.a)修改字段jsonb_set(data, {a}, 1)JSON_SET(data, $.a, 1)数组查询原生操作符支持JSON_TABLE8.0以后等MySQL的JSON_EXTRACT用path语法如$.a.bPostgreSQL用花括号路径如{a,b}。从MySQL迁移SQL到PostgreSQL时单是这些操作符的改写就要花不少时间如果团队不熟悉容易出低级错误。6.2 聚合与查询能力差距在复杂场景下会放大PostgreSQL的jsonb在复杂JSON数据处理上的优势在嵌套聚合时尤其明显。比如要把多行数据聚合成JSON数组-- PostgreSQL 的 jsonb_agg 非常丝滑 SELECT jsonb_agg(jsonb_build_object(id, id, name, name)) FROM users WHERE status active;MySQL 8.0也有JSON_ARRAYAGG但灵活性不如PostgreSQL的组合方式多。而且PostgreSQL的jsonb配合jsonb_each、jsonb_to_record等函数做拆行处理时能比较优雅地实现JSON和关系数据之间的双向转换。我在做数据仓库清洗时经常需把JSON里嵌套的数组炸开成多行数据PostgreSQL的jsonb_array_elements配合LATERAL JOIN非常顺手。6.3 从Oracle迁移时的思维差异Oracle的JSON支持是后来加上去的操作风格偏向函数式JSON_VALUE、JSON_QUERY和PostgreSQL的操作符式风格截然不同。如果你在Oracle里习惯写JSON_VALUE(json_col, $.name)转PostgreSQL后很容易写成json_col - name然后奇怪为什么返回的是带引号的字符串——这其实是忘记用-的问题。团队迁移时建议先在测试环境跑一遍两边的JSON查询对照表让所有开发熟悉PostgreSQL的操作符表达习惯。我再重复一次-返回带类型的JSON值-返回纯文本这两者在WHERE、SELECT、JOIN场景下的选择直接决定SQL正确性和性能。7. 排错实录这六个JSON字段相关的坑我都踩过7.1 报错invalid input syntax for type json这个错误在往JSON字段插入或更新数据时非常常见。原因是输入的字符串不是合法的JSON文本。比如-- 错误缺少双引号 UPDATE products SET attributes jsonb_set(attributes, {name}, 张三) WHERE id 1; -- ERROR: invalid input syntax for type json -- DETAIL: Token 张三 is invalid. -- 正确写法字符串值必须用双引号包起来 UPDATE products SET attributes jsonb_set(attributes, {name}, 张三) WHERE id 1;后端语言里如果拼SQL时直接把一个带引号的JSON字符串传进去最外层还要做一个转义容易乱。我的建议是尽量用参数化查询把完整的JSON文本或字符串作为绑定参数传给驱动不要手动拼接SQL。调试时可以用SELECT 张三::jsonb先验证字符串是否合法。7.2 报错unexpected end of json input这个报错一般发生在解析不完整的JSON文本上通常是截断或者拼接时少了大括号。我在一次从日志文件批量导入数据时遇到过因为源文件某行JSON被换行截断了导致整条SQL执行失败。当时排查了半天最后用一个小查询把有问题的文本捞出来SELECT id, raw_text FROM import_log WHERE raw_text NOT LIKE %}%;这种问题没有太好的捷径只能先定位是哪条数据格式不完整。日常写脚本导入JSON时建议先找一个JSON校验库或在线格式化工具校验一遍再入库能省去很多后续排查时间。7.3 查询条件写错-和-的类型混淆这个前面反复提过但还是值得单独列出因为它太常见了。比如下面这类查询经常会报错或者查不到-- 报错场景返回jsonb不能和字符串直接比较 SELECT * FROM users WHERE info - status active; -- 正确用 - 返回text再比较 SELECT * FROM users WHERE info - status active; -- 静默错误的场景取出的值是带引号的字符串被当成JSON文档比较 SELECT * FROM users WHERE info - status active;如果你发现查询结果莫名其妙地为零优先检查是不是把-写成了-。这类问题不像类型报错那么显眼特别容易在代码review时被漏过去。7.4 jsonb里的大小写和空格以及NULL的两种形态JSON的键是区分大小写的。表里存了{Name: 张三}你用WHERE data - name 张三是查不到的。这一点在对接外部接口时尤其要留意接口文档和实际返回字段经常存在大小写不一致的情况。另一个容易混淆的点是JSON里的null和SQL的NULL。看这个例子-- 键存在但值就是null SELECT {name: null}::jsonb - name; -- 结果: NULL 类型是SQL NULL但键在JSON中是存在的 -- 键不存在 SELECT {age: 30}::jsonb - name; -- 结果: NULL 也是SQL NULL -- 但用 判断时两者完全不同 SELECT {name: null}::jsonb {name: null}; -- 结果: true SELECT {age: 30}::jsonb {name: null}; -- 结果: false在应用层判断JSON里某个字段是否为空时要想清楚你要判断的是“键不存在”还是“键的值为null”这两者的业务含义可能差别很大。我一般建议在应用层统一约定要么完全不用null缺省就删键要么键必须存在值为null表示字段无效。两种风格混合使用后面写条件判断会写出很多Bug。7.5 更新了JSON字段之后没反应这种情况多半是jsonb_set的path路径写错或者create_missing参数设为了false。我见过有人写了一晚上的线上排查最后发现是路径大小写和字段实际键名差一个字符。调试时可以先SELECT出来看对不对再改成UPDATE-- 先查 SELECT jsonb_set(attributes, {specs,RAM}, 12GB::jsonb, true) FROM products WHERE id 1001; -- 确认输出正确后再加UPDATE UPDATE products SET attributes jsonb_set(attributes, {specs,RAM}, 12GB::jsonb, true) WHERE id 1001;这个习惯能避免很多无谓的线上事故。UPDATE一条语句直接执行出了问题回滚麻烦先SELECT预览一下成本极低。7.6 Navicat等客户端下JSON字段查着模糊、拷不出来有些人在Navicat里看PostgreSQL的JSON字段发现显示的是一个长文本不能像普通列那样方便地复制某个键的值。其实Navicat支持直接点开JSON查看器但如果没有也可以在SQL里用jsonb_pretty格式化后查看或者直接SELECT attributes - 某个键取出你要的值。从数据导出角度看Excel导出JSON字段时容易因为换行和特殊字符导致列错位需要特别处理我通常会在导出前先把JSON字段转成单行文本再去掉换行符。8. 实用扩展JSON字段在真实项目里还能这么用除了基本的增删改查JSON字段在PostgreSQL里还能玩出一些高级花样。这些用法不是炫技是我在真实业务场景里用过的确实能解决实际问题。8.1 用JSONB数组做主从表关系的临时载体某些一次性报表场景主表和明细表的数据量特别大JOIN查询太慢而且明细数据是一次性生成也不需要长期更新。这时候可以把明细聚合到主表的JSONB数组字段里-- 一次JOIN产生明细并聚合写入主表的jsonb字段 UPDATE report_main m SET details sub.details FROM ( SELECT main_id, jsonb_agg(jsonb_build_object(product, product, amount, amount)) AS details FROM report_detail GROUP BY main_id ) sub WHERE m.id sub.main_id;这样后续报表查询主表时直接取details字段不需要再关联明细表能在报表加载性能上获得非常直观的提升。当然代价是明细数据更新时主表的聚合字段也要跟着更新适合明细基本不会变的场景。8.2 利用jsonb_each做行转列探索在数据探查阶段我们经常不太确定一张JSON表里都有哪些键这个时候用jsonb_each函数把每个键拆成一行来看比一条条记录翻要高效得多SELECT key, count(*) AS cnt FROM products, jsonb_each(attributes) AS attr(key, value) GROUP BY key ORDER BY cnt DESC;这条SQL能统计出所有JSON键的出现次数特别适合初次接触一个脏数据表时先摸清数据分布再决定哪个键可以提升为正式列。8.3 给关键JSON查询封装视图JSON里的键毕竟没有列注释团队里每个人对键名的理解不同写出来的SQL容易各写各的。我习惯为基础的核心JSON数据创建视图把常用键提升为虚拟列并加上注释CREATE VIEW v_products AS SELECT id, name, attributes - brand AS brand, attributes - color AS color, attributes # {specs,ram} AS ram, attributes # {specs,storage} AS storage FROM products; COMMENT ON COLUMN v_products.brand IS 品牌;这样下游的报表开发、数据分析同学就不需要理解JSON的路径语法直接当普通虚拟列使用同时也能规避掉因为键名拼写错误带来的隐性问题。视图方案实际执行效果很好团队成员对JSON字段的接受度会高很多。回到最开始那句话——JSON字段不是万能的但它确实是PostgreSQL提供的一件利器。用对了地方它能让你少写几百行代码、少做几十次表结构变更。用错了地方它就是性能黑洞和数据质量洼地。我这几年的经验归纳起来就三点偏好jsonb、善用索引、维护字段字典。做到这三点PostgreSQL的JSON字段就能稳稳地成为你架构工具箱里的常规选项。最后再分享一个小建议如果你正在把MySQL的老业务迁到PostgreSQL迁移时别急着把JSON字段全部拆成关系表。先跑一遍真实业务查询看看哪些字段是被高频检索的哪些只是存而不用——只把高频检索的键提取出来建索引或建列剩下的留在JSONB里。这样迁移成本低、风险小性能还往往不降反升。
PostgreSQL JSON字段实战：从jsonb选型到索引优化与排错
手头各种数据七七八八塞进PostgreSQL最后发现关系表怎么设计都别扭——字段要么提前预留一堆空列要么为了兼容不同业务含义搞出几十个扩展字段——这时候你就该认真考虑用JSON字段了。我在项目里把大量灵活属性、接口原始报文、配置快照都迁移到了JSON字段上配合PostgreSQL的JSONB类型和GIN索引既保住了灵活性又没牺牲查询效率。这篇就把我在PostgreSql里用JSON字段的完整经验拆开讲清楚从数据类型选型、日常增删改查、索引优化到和MySQL的差异对比、排错实录一次性给你梳理明白。1. 先泼盆冷水JSON字段从来不是用来替代关系模型的很多初学者一看到PostgreSQL支持JSON就像拿到新玩具一样恨不得把所有表都加一个JSON字段把本该拆表的业务数据全部塞进去。我在实际项目里见过最夸张的情况是有人把订单明细全部塞进一个JSON数组单条记录几十KB查询时靠应用层遍历性能直接崩掉。所以开篇必须先把这个认知掰正。1.1 什么时候用JSON字段是合理的设计从我的实践经验看JSON字段真正擅长解决的是三类问题。第一类是动态属性。比如电商的商品表不同品类商品的属性完全不一样——手机要存运存、屏幕尺寸、电池容量衣服要存尺码、面料、洗涤方式。如果建关系表要么搞EAV模式实体-属性-值查询灾难要么为每个品类建单独的表表结构变更频繁。这时候用JSON字段存attributes查询用GIN索引反而最干净。第二类是异构数据存储。比如第三方接口的原始报文、爬虫抓取的页面结构化数据、配置文件的快照这类数据你可能只是完整存下来备查偶尔按某个键过滤对结构完整性要求低但灵活性要求高。关系表无法在不频繁迁移的情况下容纳这样的数据JSON字段天然合适。第三类是软模式演进。早期需求不明确字段随时可能增加或调整用JSON字段能减少ALTER TABLE的操作次数。我习惯在核心关系表里保留一个extra JSON字段专门承接业务方后来加的散装需求等某类属性稳定下架再把它提升为正式列。1.2 什么时候千万别用JSON字段判断标准其实很简单如果你要基于JSON里的字段做频繁关联、排序、聚合计算或者数据量很大且需要强约束那就别用JSON字段。例如订单表如果你的业务经常需要按订单里的收货人姓名做JOIN查询、做分组统计、建立外键关系这些业务放在JSON字段里就会非常痛苦。虽然PostgreSQL支持表达式索引理论上可以对JSON里的字段建索引但它的类型检查和约束能力远不如真正的列字段。在涉及资金、库存这类需要严格一致性的核心链路能用强类型列就用强类型列。还有一点我吃过亏的JSON字段的变更历史是隐形的。关系表的列有注释、有类型约束、有迁移记录JSON字段里的键却没有任何注释和约束时间一长根本没人知道某个键代表什么、值是什么格式。我曾经接手过一个系统JSON字段里同一个含义的键名有四种写法数据质量惨不忍睹。所以如果要用JSON字段一定要在应用层维护一份字段字典文档把每个键的含义、类型、取值范围写清楚否则迟早还债。2. json和jsonb到底选哪个这一步错了后面全白搭PostgreSQL提供了两种JSON数据类型json和jsonb。很多人不太在意随便选一个结果后面索引建不上、查询慢、结果顺序和预期不一致各种问题翻车。我直接给结论99%的场景用jsonbjson类型只在你需要保留原始输入文本原样输出时才有存在价值。2.1 存储机制的核心差异json类型是原样存储的。你传入什么文本数据库就原样保存空格、键的顺序、重复的键、数字的表示法1还是1.0都会保留。每次查询和操作时数据库需要现场解析文本把JSON文本解析成内部结构再操作所以json类型的查询效率天然低于jsonb。但好处是输出结果和你存入时一模一样。jsonb类型则完全不同。输入时会做一次解析和规范化处理将JSON文本转换成一种分解的二进制格式存储。这个处理过程会去掉键之间的冗余空格、重新排序键按长度和字节顺序调整、移除重复键保留最后一个值、把数字规范化1和1.0会统一为1。存储成本稍高一点但换来的是查询时无需重复解析还支持GIN索引加速这是json对标的优势所在。我举一个特别容易踩的例子你插入字符串{b: 1, a: 2}到jsonb字段再SELECT出来结果会变成{a: 2, b: 1}。键的顺序变了如果你有地方是拿字段值做字符串拼接或做签名校验的这里的差异会让你排查到怀疑人生。2.2 功能支持的差异jsonb的功能全面领先能力项jsonjsonb存储格式文本原样二进制解析后GIN索引支持不支持支持删除键/更新键不支持支持、?、?、?操作符不支持jsonb_set更新函数无支持键顺序保留自动排序不保证输入解析即时解析写入时解析在PostgreSQL 12以后json类型也支持了部分JSONPATH查询但索引支持依然只有jsonb才有。所以结论很简单默认选jsonb。2.3 类型转换的几个实用写法日常开发中json和jsonb之间的转换很常见直接CAST就可以-- json 转 jsonb SELECT {name: postgres}::json::jsonb; -- jsonb 转 json基本不常用除非对接接口要求原样输出 SELECT {name: postgres}::jsonb::json; -- 格式化输出排查问题时很好用 SELECT jsonb_pretty({name: postgres, info: {port: 5432}}::jsonb);jsonb_pretty这个函数我强烈推荐配合psql或者数据库客户端查看大JSON时非常直观。3. 查询JSON字段-、-、#这些操作符怎么用才不出错JSON字段的查询占据日常操作80%的内容。PostgreSQL提供了一组强大的操作符但很多开发者经常因为返回类型搞混而写出报错SQL。我按使用频率逐一说明。3.1 - 和 -取单个键值的两个层次这是最常用的两个操作符也是很多人一开始最容易搞混的-- - 返回JSON类型保留类型信息取出来的值还是JSON SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三注意带双引号类型是JSON字符串 -- - 返回文本类型 SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三不带引号类型是text这个区别在WHERE条件里特别致命。比如你要查age大于18的记录-- 错误写法- 返回jsonb类型无法和整数比较 SELECT * FROM users WHERE info - age 18; -- ERROR: operator does not exist: jsonb integer -- 正确写法- 返回text必须隐式转成数值 SELECT * FROM users WHERE (info - age)::int 18;类似的在SELECT投影列表中如果你在外面做字符串拼接、做大小写转换、做区间比较基本都要用-。还有一种场景是从JSON数组里取元素同样可以用-和--- 取数组第一个元素 SELECT [{name: A}, {name: B}]::jsonb - 0; -- 结果: {name: A} -- 取数组第一个元素后取name字段 SELECT [{name: A}, {name: B}]::jsonb - 0 - name; -- 结果: A3.2 # 和 #路径查询省掉一串级联箭头当JSON嵌套层级深的时候连续使用-会把SQL写得又长又容易错。PostgreSQL提供了路径操作符-- # 按路径返回JSON类型 SELECT {a: {b: {c: [10, 20]}}}::jsonb # {a,b,c}; -- 结果: [10, 20] -- # 按路径返回text类型 SELECT {a: {b: {c: [10, 20]}}}::jsonb # {a,b,c,0}; -- 结果: 10路径语法用大括号或方括号里的字符串数组表示每一层的键名或数组下标。注意用这种写法时路径中间任何一级不存在返回的是NULL不是报错。在写一些防御性查询时反而方便。3.3 存在性判断、?、?|、?这是jsonb查询最爽的部分也是关系表很难做到的地方。这几个操作符专门用于判断JSON里是否存在指定的键或值配合GIN索引性能极佳。-- ? 判断某个键是否存在于顶层 SELECT {name: 张三, age: 30}::jsonb ? name; -- 结果: true -- ?| 判断键数组中任意一个存在 SELECT {name: 张三}::jsonb ?| array[name, email]; -- 结果: true -- ? 判断键数组中全部存在 SELECT {name: 张三, age: 30}::jsonb ? array[name, age]; -- 结果: true -- 判断左侧JSON是否包含右侧JSON支持嵌套 SELECT {name: 张三, address: {city: 北京}}::jsonb {address: {city: 北京}}; -- 结果: true这个操作符是包含语义右侧必须是左侧的子集。它不仅能判断键是否存在还能判断嵌套值是否相等。这个特性在做标签筛选、条件匹配、权限校验时非常好用。比如用户表里有一个tags JSON字段需要筛选同时带有VIP和高活跃标签的用户SELECT * FROM users WHERE tags [VIP, 高活跃]::jsonb;3.4 在表和表之间用JSON字段做关联很多人在 JOIN 场景里遇到JSON字段就犯难其实思路和普通列一样只是要把键值解析出来再关联。我做过一个比较典型的案例订单表里有JSON字段存了用户手机号用户表里有手机号列要查出订单对应的用户信息SELECT o.order_no, u.nickname FROM orders o JOIN users u ON u.phone o.extra_info - phone WHERE o.created_at 2024-01-01;这种关联查询能用但需要注意它本质上相当于在关联条件里对JSON字段做了解析计算如果订单表很大性能瓶颈会很明显。这时候就应该考虑对(extra_info - phone)这种表达式建索引后面索引部分会详细说。4. 修改JSON字段jsonb_set和删除键的正确姿势PostgreSQL里JSON字段的更新和普通行更新有本质区别。因为JSONB是整体存储在行里的你每次修改JSON里的某个子字段实际上都是取出整个JSON、修改后再整体写回。理解这一点你就能明白为什么频繁更新大JSON字段会导致表膨胀和性能下降。4.1 用jsonb_set更新嵌套键值jsonb_set的完整语法jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true)path表示要更新或创建的键路径new_value是要设置的新值必须是json/jsonb类型create_missing控制当路径不存在时是否自动创建。一个典型的更新例子-- 更新specs.ram的值 UPDATE products SET attributes jsonb_set(attributes, {specs,ram}, 12GB::jsonb, true) WHERE id 1001;注意一个常见的坑new_value参数必须是JSON格式。如果你想写字符串必须带引号即12GB::jsonb如果你写成12GB::jsonb数据库会报错因为12GB不是合法的JSON文本。我在项目里经常看到有人在这里困惑报错信息是invalid input syntax for type json。数字类型可以直接写18::jsonb布尔类型写true::jsonb对象和数组写{key:value}::jsonb。如果你要更新的字段可能存在也可能不存在把第四个参数设为true是最省事的。但如果你希望严格模式下报错可以设为false。4.2 删除键- 和 #- 操作符删除JSON字段的键用-操作符-- 删除顶层键 UPDATE products SET attributes attributes - color WHERE id 1001; -- 数组按下标删除删除数组第二个元素 UPDATE products SET attributes attributes - 1 WHERE id 1001;删除深层键要用#-操作符配路径-- 删除specs下的old_key UPDATE products SET attributes attributes #- {specs,old_key} WHERE id 1001;4.3 数组追加和对象合并的几个便利写法JSONB在拼接方面也提供了一些操作符极大简化了修改逻辑-- 数组追加元素 UPDATE products SET attributes attributes || [新标签]::jsonb WHERE id 1001; -- 对象合并多个键合并更新 UPDATE products SET attributes attributes || {warranty: 1年, color: 黑色}::jsonb WHERE id 1001;注意||对于对象类型是浅层合并只合并第一层键嵌套对象会整体替换而不是递归合并。比如attributes里原本有{specs: {ram: 8GB}}你用|| {specs: {storage: 256GB}}去合并结果是{specs: {storage: 256GB}}ram丢了。如果要做深层递归合并还是得用jsonb_set逐层设置或者写一个递归合并的辅助函数。4.4 更新大JSON的性能注意事项前面提过更新JSON字段是整行读改写。如果一条记录的JSON字段有几十KB甚至更大那么每次只更新里面一个键整个大JSON都会经历序列化、修改、再序列化写回。如果有高并发大对象更新需求会造成行锁竞争、WAL日志写入量暴增、表膨胀最终拖垮性能。我的实践经验是JSON字段控制在KB级别是正常的如果单条要上MB就该考虑JSON字段是否适合存储这份数据了。比如日志明细与其塞在一个JSON数组字段里不如拆出去单独建表一行一条日志利用PostgreSQL的列式存储面向OLAP场景或普通行存做聚合查询。JSON字段做低频更新、低频写入的高价值小对象是它的舒适区。5. 索引优化让JSON字段查询快起来的核心手段在数据量小的时候JSON字段怎么查都很快。但数据量一涨比如到了百万级没有索引的JSON条件查询可能就是一次全表扫描性能直接不可用。PostgreSQL给jsonb提供了专用索引但在实际使用中很多人建了索引却发现没走本质原因是不够了解索引的作用域。5.1 GIN索引jsonb的默认高性能查询方案GINGeneralized Inverted Index索引适用于全文搜索、数组包含等场景对jsonb的、?、?|、?这类存在性操作符有显著的加速效果。创建方式CREATE INDEX idx_products_attributes ON products USING GIN (attributes);我的测试环境里200万条数据中按attributes {brand: Xiaomi}筛选没有GIN索引时全表扫描耗时约1.8秒建了GIN索引后降到40毫秒左右提升非常明显。GIN索引还有一个细化选项jsonb_path_opsCREATE INDEX idx_products_attributes ON products USING GIN (attributes jsonb_path_ops);jsonb_path_ops索引比普通GIN索引更小也更高效但代价是它只支持操作符不支持?、?|、?。如果你的查询模式主要是包含关系判断比如按JSON里的属性组合筛选用jsonb_path_ops是更优选择如果还需要频繁判断键是否存在那就用普通GIN。5.2 表达式索引给JSON内的某个字段建索引GIN索引能解决存在性查询但对排序、范围查询, , BETWEEN、等值匹配-取值后比较就无能为力了。比如我们前面讲的WHERE (attributes - price)::numeric 100这属于在表达式上做范围比较需要建表达式索引CREATE INDEX idx_products_price ON products ((attributes - price));如果你在WHERE条件里经常用(attributes - price)::numeric转成数值再比较建议索引直接建在数值类型上CREATE INDEX idx_products_price_numeric ON products (((attributes - price)::numeric));注意这里两侧的括号别省创建时如果有问题可以检查一下CAST的写法。表达式索引的代价是必须先明确表达式形态在WHERE里写的表达式和索引表达式必须匹配否则不会走索引。5.3 用EXPLAIN确认索引真的生效了索引建了不等于一定生效。操作符比较特殊只有当左侧列上有GIN索引且右侧是常量表达式时优化器才会考虑走索引。如果你的右侧又是一个表的列值情况会变得复杂优化器可能选择全表扫描。我建议每次建完索引后用EXPLAIN ANALYZE实际验证一下EXPLAIN ANALYZE SELECT * FROM products WHERE attributes {brand: Xiaomi};如果看到Seq Scan on products说明没走索引要检查查询语句条件是否匹配索引范围或者数据量太小导致优化器认为全表扫描更快。如果看到Bitmap Index Scan on idx_products_attributes和Bitmap Heap Scan说明索引生效了。我积累的一个经验是数据量低于几万行时全表扫描和索引查询的差距并不大但数据量过百万后索引的价值才会体现出来。所以判断是否建索引先看数据量再看实际执行计划别盲目建一堆索引徒增写入成本。6. 和MySQL的JSON较量从Oracle/MySQL迁过来的人最容易踩哪些坑如果之前主要用MySQL或者Oracle转到PostgreSQL的JSON字段时你会发现概念类似但具体操作方式差异不小。这个对比对团队迁移决策特别重要。6.1 存储和类型的底层差异MySQL从5.7开始支持JSON类型存储上也是解析后的二进制格式这点和jsonb有点像。但MySQL的JSON字段不能有默认值8.0.13之前而且JSON列不能直接建普通索引必须用函数索引MySQL 8.0.13开始才支持对JSON列的表达式索引。PostgreSQL在这方面灵活得多——jsonb支持BTREE、GIN等多种索引策略jsonb字段也允许设置默认值。在查询操作符上差异更明显能力项PostgreSQL (jsonb)MySQL (JSON)包含判断data {a:1}JSON_CONTAINS(data, {a:1})取键值data - aJSON_UNQUOTE(JSON_EXTRACT(data, $.a))键是否存在data ? aJSON_CONTAINS_PATH(data, one, $.a)修改字段jsonb_set(data, {a}, 1)JSON_SET(data, $.a, 1)数组查询原生操作符支持JSON_TABLE8.0以后等MySQL的JSON_EXTRACT用path语法如$.a.bPostgreSQL用花括号路径如{a,b}。从MySQL迁移SQL到PostgreSQL时单是这些操作符的改写就要花不少时间如果团队不熟悉容易出低级错误。6.2 聚合与查询能力差距在复杂场景下会放大PostgreSQL的jsonb在复杂JSON数据处理上的优势在嵌套聚合时尤其明显。比如要把多行数据聚合成JSON数组-- PostgreSQL 的 jsonb_agg 非常丝滑 SELECT jsonb_agg(jsonb_build_object(id, id, name, name)) FROM users WHERE status active;MySQL 8.0也有JSON_ARRAYAGG但灵活性不如PostgreSQL的组合方式多。而且PostgreSQL的jsonb配合jsonb_each、jsonb_to_record等函数做拆行处理时能比较优雅地实现JSON和关系数据之间的双向转换。我在做数据仓库清洗时经常需把JSON里嵌套的数组炸开成多行数据PostgreSQL的jsonb_array_elements配合LATERAL JOIN非常顺手。6.3 从Oracle迁移时的思维差异Oracle的JSON支持是后来加上去的操作风格偏向函数式JSON_VALUE、JSON_QUERY和PostgreSQL的操作符式风格截然不同。如果你在Oracle里习惯写JSON_VALUE(json_col, $.name)转PostgreSQL后很容易写成json_col - name然后奇怪为什么返回的是带引号的字符串——这其实是忘记用-的问题。团队迁移时建议先在测试环境跑一遍两边的JSON查询对照表让所有开发熟悉PostgreSQL的操作符表达习惯。我再重复一次-返回带类型的JSON值-返回纯文本这两者在WHERE、SELECT、JOIN场景下的选择直接决定SQL正确性和性能。7. 排错实录这六个JSON字段相关的坑我都踩过7.1 报错invalid input syntax for type json这个错误在往JSON字段插入或更新数据时非常常见。原因是输入的字符串不是合法的JSON文本。比如-- 错误缺少双引号 UPDATE products SET attributes jsonb_set(attributes, {name}, 张三) WHERE id 1; -- ERROR: invalid input syntax for type json -- DETAIL: Token 张三 is invalid. -- 正确写法字符串值必须用双引号包起来 UPDATE products SET attributes jsonb_set(attributes, {name}, 张三) WHERE id 1;后端语言里如果拼SQL时直接把一个带引号的JSON字符串传进去最外层还要做一个转义容易乱。我的建议是尽量用参数化查询把完整的JSON文本或字符串作为绑定参数传给驱动不要手动拼接SQL。调试时可以用SELECT 张三::jsonb先验证字符串是否合法。7.2 报错unexpected end of json input这个报错一般发生在解析不完整的JSON文本上通常是截断或者拼接时少了大括号。我在一次从日志文件批量导入数据时遇到过因为源文件某行JSON被换行截断了导致整条SQL执行失败。当时排查了半天最后用一个小查询把有问题的文本捞出来SELECT id, raw_text FROM import_log WHERE raw_text NOT LIKE %}%;这种问题没有太好的捷径只能先定位是哪条数据格式不完整。日常写脚本导入JSON时建议先找一个JSON校验库或在线格式化工具校验一遍再入库能省去很多后续排查时间。7.3 查询条件写错-和-的类型混淆这个前面反复提过但还是值得单独列出因为它太常见了。比如下面这类查询经常会报错或者查不到-- 报错场景返回jsonb不能和字符串直接比较 SELECT * FROM users WHERE info - status active; -- 正确用 - 返回text再比较 SELECT * FROM users WHERE info - status active; -- 静默错误的场景取出的值是带引号的字符串被当成JSON文档比较 SELECT * FROM users WHERE info - status active;如果你发现查询结果莫名其妙地为零优先检查是不是把-写成了-。这类问题不像类型报错那么显眼特别容易在代码review时被漏过去。7.4 jsonb里的大小写和空格以及NULL的两种形态JSON的键是区分大小写的。表里存了{Name: 张三}你用WHERE data - name 张三是查不到的。这一点在对接外部接口时尤其要留意接口文档和实际返回字段经常存在大小写不一致的情况。另一个容易混淆的点是JSON里的null和SQL的NULL。看这个例子-- 键存在但值就是null SELECT {name: null}::jsonb - name; -- 结果: NULL 类型是SQL NULL但键在JSON中是存在的 -- 键不存在 SELECT {age: 30}::jsonb - name; -- 结果: NULL 也是SQL NULL -- 但用 判断时两者完全不同 SELECT {name: null}::jsonb {name: null}; -- 结果: true SELECT {age: 30}::jsonb {name: null}; -- 结果: false在应用层判断JSON里某个字段是否为空时要想清楚你要判断的是“键不存在”还是“键的值为null”这两者的业务含义可能差别很大。我一般建议在应用层统一约定要么完全不用null缺省就删键要么键必须存在值为null表示字段无效。两种风格混合使用后面写条件判断会写出很多Bug。7.5 更新了JSON字段之后没反应这种情况多半是jsonb_set的path路径写错或者create_missing参数设为了false。我见过有人写了一晚上的线上排查最后发现是路径大小写和字段实际键名差一个字符。调试时可以先SELECT出来看对不对再改成UPDATE-- 先查 SELECT jsonb_set(attributes, {specs,RAM}, 12GB::jsonb, true) FROM products WHERE id 1001; -- 确认输出正确后再加UPDATE UPDATE products SET attributes jsonb_set(attributes, {specs,RAM}, 12GB::jsonb, true) WHERE id 1001;这个习惯能避免很多无谓的线上事故。UPDATE一条语句直接执行出了问题回滚麻烦先SELECT预览一下成本极低。7.6 Navicat等客户端下JSON字段查着模糊、拷不出来有些人在Navicat里看PostgreSQL的JSON字段发现显示的是一个长文本不能像普通列那样方便地复制某个键的值。其实Navicat支持直接点开JSON查看器但如果没有也可以在SQL里用jsonb_pretty格式化后查看或者直接SELECT attributes - 某个键取出你要的值。从数据导出角度看Excel导出JSON字段时容易因为换行和特殊字符导致列错位需要特别处理我通常会在导出前先把JSON字段转成单行文本再去掉换行符。8. 实用扩展JSON字段在真实项目里还能这么用除了基本的增删改查JSON字段在PostgreSQL里还能玩出一些高级花样。这些用法不是炫技是我在真实业务场景里用过的确实能解决实际问题。8.1 用JSONB数组做主从表关系的临时载体某些一次性报表场景主表和明细表的数据量特别大JOIN查询太慢而且明细数据是一次性生成也不需要长期更新。这时候可以把明细聚合到主表的JSONB数组字段里-- 一次JOIN产生明细并聚合写入主表的jsonb字段 UPDATE report_main m SET details sub.details FROM ( SELECT main_id, jsonb_agg(jsonb_build_object(product, product, amount, amount)) AS details FROM report_detail GROUP BY main_id ) sub WHERE m.id sub.main_id;这样后续报表查询主表时直接取details字段不需要再关联明细表能在报表加载性能上获得非常直观的提升。当然代价是明细数据更新时主表的聚合字段也要跟着更新适合明细基本不会变的场景。8.2 利用jsonb_each做行转列探索在数据探查阶段我们经常不太确定一张JSON表里都有哪些键这个时候用jsonb_each函数把每个键拆成一行来看比一条条记录翻要高效得多SELECT key, count(*) AS cnt FROM products, jsonb_each(attributes) AS attr(key, value) GROUP BY key ORDER BY cnt DESC;这条SQL能统计出所有JSON键的出现次数特别适合初次接触一个脏数据表时先摸清数据分布再决定哪个键可以提升为正式列。8.3 给关键JSON查询封装视图JSON里的键毕竟没有列注释团队里每个人对键名的理解不同写出来的SQL容易各写各的。我习惯为基础的核心JSON数据创建视图把常用键提升为虚拟列并加上注释CREATE VIEW v_products AS SELECT id, name, attributes - brand AS brand, attributes - color AS color, attributes # {specs,ram} AS ram, attributes # {specs,storage} AS storage FROM products; COMMENT ON COLUMN v_products.brand IS 品牌;这样下游的报表开发、数据分析同学就不需要理解JSON的路径语法直接当普通虚拟列使用同时也能规避掉因为键名拼写错误带来的隐性问题。视图方案实际执行效果很好团队成员对JSON字段的接受度会高很多。回到最开始那句话——JSON字段不是万能的但它确实是PostgreSQL提供的一件利器。用对了地方它能让你少写几百行代码、少做几十次表结构变更。用错了地方它就是性能黑洞和数据质量洼地。我这几年的经验归纳起来就三点偏好jsonb、善用索引、维护字段字典。做到这三点PostgreSQL的JSON字段就能稳稳地成为你架构工具箱里的常规选项。最后再分享一个小建议如果你正在把MySQL的老业务迁到PostgreSQL迁移时别急着把JSON字段全部拆成关系表。先跑一遍真实业务查询看看哪些字段是被高频检索的哪些只是存而不用——只把高频检索的键提取出来建索引或建列剩下的留在JSONB里。这样迁移成本低、风险小性能还往往不降反升。
