PG JSON 遍历终极指南：7 个函数 + 5 个实战场景，看完直接抄搞不定 jsonb 循环遍历？从 jsonb_array_elements 到 LATERAL JOIN，一篇讲透

适用版本PostgreSQL 9.4本文所有函数基于jsonb类型json类型函数名为json_array_elements/json_each等去掉b即可阅读时间约 12 分钟关键词PostgreSQL、JSONB、数组遍历、键值遍历、LATERAL JOIN、展开、实战一、写在前面在上一篇中我们讲了-和-提取单个值的用法。但在实际开发中我们经常需要遍历 JSON 数组或遍历 JSON 对象的所有键值对——比如把一个 JSON 数组拆成多行、把 JSON 对象的每个 key 展开成行。PostgreSQL 提供了一组强大的生成函数Generating Functions配合LATERAL JOIN可以实现各种循环遍历需求。本文系统讲解。二、核心函数一览函数作用输入输出jsonb_array_elements(jsonb)将 JSON 数组展开为多行JSON 数组每行一个数组元素jsonbjsonb_array_elements_text(jsonb)同上但返回文本JSON 数组每行一个文本值jsonb_each(jsonb)展开对象为多行key, valueJSON 对象每行(key: text, value: jsonb)jsonb_each_text(jsonb)同上但 value 返回文本JSON 对象每行(key: text, value: text)jsonb_object_keys(jsonb)提取对象的所有键名JSON 对象每行一个 keytextjsonb_array_length(jsonb)返回数组长度JSON 数组整数jsonb_typeof(jsonb)返回 JSON 值的类型任意 JSON文本object/array/string/number等三、基础环境准备sql复制-- 创建测试表 CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(50), data JSONB ); -- 插入测试数据 INSERT INTO employees (name, data) VALUES (张三, {skills: [Java, Python, Go], meta: {age: 28, city: 北京, level: P6}, projects: [{name: 订单系统, role: 后端}, {name: 支付网关, role: 架构}]}), (李四, {skills: [C, Rust], meta: {age: 35, city: 上海, level: P7}, projects: [{name: 中间件, role: 核心开发}]}), (王五, {skills: [JavaScript], meta: {age: 22, city: 深圳, level: P5}, projects: []});四、遍历 JSON 数组jsonb_array_elements4.1 基本用法数组展开为多行sql复制SELECT e.name, skill AS skill_value FROM employees e, jsonb_array_elements(e.data-skills) AS skill;结果nameskill_value张三Java张三Python张三Go李四C李四Rust王五JavaScript⚠️jsonb_array_elements返回的是 jsonb 类型所以值带双引号。4.2 取文本值jsonb_array_elements_textsql复制SELECT e.name, skill AS skill_text FROM employees e, jsonb_array_elements_text(e.data-skills) AS skill;结果nameskill_text张三Java张三Python张三Go李四C李四Rust王五JavaScript✅ 用jsonb_array_elements_text直接返回文本不带引号更方便后续使用。4.3 加序号展开数组并保留原始索引PostgreSQL 原生函数不直接返回索引可以用WITH ORDINALITYsql复制SELECT e.name, skill.skill AS skill_value, skill.ordinality AS skill_index FROM employees e, jsonb_array_elements(e.data-skills) WITH ORDINALITY AS skill(skill, ordinality);结果nameskill_valueskill_index张三Java1张三Python2张三Go3李四C1李四Rust2王五JavaScript1WITH ORDINALITY是 PostgreSQL 9.4 特性自动生成行号从 1 开始。这是很多人不知道的隐藏用法。五、遍历 JSON 对象jsonb_each/jsonb_object_keys5.1 展开对象所有键值对jsonb_eachsql复制SELECT e.name, kv.key AS meta_key, kv.value AS meta_value FROM employees e, jsonb_each(e.data-meta) AS kv;结果namemeta_keymeta_value张三age28张三city北京张三levelP6李四age35李四city上海李四levelP7王五age22王五city深圳王五levelP55.2 取文本值jsonb_each_textsql复制SELECT e.name, kv.key AS meta_key, kv.value AS meta_value_text FROM employees e, jsonb_each_text(e.data-meta) AS kv;结果value 不带引号namemeta_keymeta_value_text张三age28张三city北京张三levelP6.........5.3 只取键名jsonb_object_keyssql复制SELECT e.name, jsonb_object_keys(e.data-meta) AS meta_key FROM employees e;结果namemeta_key张三age张三city张三level李四age......适合场景不确定 JSON 有哪些 key需要动态获取所有键名。六、LATERAL JOIN 详解6.1 什么是 LATERAL JOINLATERAL关键字允许子查询引用左侧表的列。PostgreSQL 中生成函数如jsonb_array_elements放在 FROM 子句中时默认就是 LATERAL 行为不需要显式写LATERAL。但显式写出LATERAL可以让意图更清晰sql复制-- 隐式 LATERAL等价写法 SELECT e.name, skill FROM employees e, jsonb_array_elements(e.data-skills) AS skill; -- 显式 LATERAL更清晰的写法 SELECT e.name, skill FROM employees e CROSS JOIN LATERAL jsonb_array_elements(e.data-skills) AS skill;6.2 LATERAL JOIN 的优势可以加 WHERE 条件sql复制-- 只展开张三的技能 SELECT e.name, skill FROM employees e CROSS JOIN LATERAL jsonb_array_elements_text(e.data-skills) AS skill WHERE e.name 张三;6.3 配合 LEFT JOIN 处理空数组CROSS JOIN在数组为空时会丢失整行。用LEFT JOIN LATERAL ... ON true保留主表行sql复制SELECT e.name, proj-name AS project_name, proj-role AS project_role FROM employees e LEFT JOIN LATERAL jsonb_array_elements(e.data-projects) AS proj ON true;结果nameproject_nameproject_role张三订单系统后端张三支付网关架构李四中间件核心开发王五NULLNULL✅ 王五的 projects 为空数组[]用LEFT JOIN LATERAL保留了行项目信息为 NULL。这在报表统计中非常重要。七、实战场景场景 1统计每个技能被多少人掌握sql复制SELECT skill AS skill_name, COUNT(*) AS user_count FROM employees e, jsonb_array_elements_text(e.data-skills) AS skill GROUP BY skill ORDER BY user_count DESC;结果skill_nameuser_countJava1Python1Go1C1Rust1JavaScript1场景 2展开嵌套数组中的对象sql复制-- 展开每个员工的 projects 数组取出项目名和角色 SELECT e.name, proj-name AS project_name, proj-role AS project_role FROM employees e, jsonb_array_elements(e.data-projects) AS proj;结果nameproject_nameproject_role张三订单系统后端张三支付网关架构李四中间件核心开发场景 3行转列pivotsql复制-- 把 meta 对象展开为列 SELECT e.name, meta_kv-age AS age, meta_kv-city AS city, meta_kv-level AS level FROM employees e CROSS JOIN LATERAL (SELECT e.data-meta AS meta_kv) AS t;更优雅的方式直接用-sql复制SELECT name, data-meta-age AS age, data-meta-city AS city, data-meta-level AS level FROM employees; 当 key 固定已知时直接用-提取更简洁。jsonb_each适合 key 不固定或需要动态遍历的场景。场景 4多层嵌套遍历sql复制-- 遍历每个员工每个项目的每个键值对 SELECT e.name, proj-name AS project_name, kv.key, kv.value FROM employees e, jsonb_array_elements(e.data-projects) AS proj, jsonb_each(proj) AS kv;结果nameproject_namekeyvalue张三订单系统name订单系统张三订单系统role后端张三支付网关name支付网关张三支付网关role架构李四中间件name中间件李四中间件role核心开发场景 5动态遍历未知结构 JSONsql复制-- 递归遍历用 jsonb_typeof 判断类型决定是否继续展开 SELECT e.name, key, value, jsonb_typeof(value) AS value_type FROM employees e, jsonb_each(e.data) AS kv(key, value) WHERE jsonb_typeof(value) IN (string, number);结果namekeyvaluevalue_type张三skills[Java,Python,Go]array (被过滤掉)张三meta{age:28,...}object (被过滤掉)张三projects[{name:...}]array (被过滤掉)用jsonb_typeof可以动态判断每个值的类型配合 CASE WHEN 实现灵活遍历逻辑。八、性能优化建议8.1 GIN 索引加速包含查询sql复制CREATE INDEX idx_emp_data ON employees USING GIN (data); -- 走索引的查询 SELECT name FROM employees WHERE data {skills: [Java]};8.2 避免对大 JSON 做全量展开sql复制-- ❌ 慢全量展开 10 万行的大数组 SELECT name, skill FROM employees, jsonb_array_elements_text(data-skills); -- ✅ 快先过滤再展开 SELECT name, skill FROM employees, jsonb_array_elements_text(data-skills) AS skill WHERE id IN (SELECT id FROM employees WHERE data {skills: [Java]});8.3 数组长度判断sql复制-- 只展开数组长度 2 的记录 SELECT e.name, skill FROM employees e, jsonb_array_elements_text(e.data-skills) AS skill WHERE jsonb_array_length(e.data-skills) 1;九、常见坑 注意事项坑说明解决方案空数组丢失行jsonb_array_elements([]::jsonb)返回 0 行CROSS JOIN 会丢掉主表行用LEFT JOIN LATERAL ... ON trueNULL 字段报错jsonb_array_elements(NULL::jsonb)返回 NULL不报错但 CROSS JOIN 丢行先COALESCE(data-skills, []::jsonb)兜底非 JSON 数组传入jsonb_array_elements({a:1}::jsonb)报错cannot extract elements from an object先用jsonb_typeof()判断是否为 array_text后缀混淆jsonb_each返回 value 是 jsonbjsonb_each_text返回 text根据需要选择文本值用_text版本WITH ORDINALITY语法必须放在表函数后面func() WITH ORDINALITY AS t(col, idx)注意 ORDINALITY 列从 1 开始字段不是 JSONB-只能用于 json/jsonb 类型先::jsonb转换(data_text::jsonb)-key十、速查表需求函数/写法示例展开数组为多行JSONjsonb_array_elementsjsonb_array_elements(data-skills)展开数组为多行文本jsonb_array_elements_textjsonb_array_elements_text(data-skills)展开数组序号... WITH ORDINALITYjsonb_array_elements(data-skills) WITH ORDINALITY AS t(val, idx)展开对象键值对JSONjsonb_eachjsonb_each(data-meta) AS kv(key, value)展开对象键值对文本jsonb_each_textjsonb_each_text(data-meta) AS kv(key, value)只取对象键名jsonb_object_keysjsonb_object_keys(data-meta)获取数组长度jsonb_array_lengthjsonb_array_length(data-skills)判断 JSON 类型jsonb_typeofjsonb_typeof(data-skills)→array保留空数组行LEFT JOIN LATERAL ... ON true见 6.3 节总结遍历场景推荐函数返回值类型遍历数组元素jsonb_array_elements_texttext推荐遍历数组序号jsonb_array_elementsWITH ORDINALITYjsonb int遍历对象键值jsonb_each_texttext, text只取对象键名jsonb_object_keystext空数组保留行LEFT JOIN LATERAL ... ON true—多层嵌套遍历链式jsonb_array_elementsjsonb_each组合最佳实践数组遍历优先用jsonb_array_elements_text直接拿文本空数组/NULL 字段用LEFT JOIN LATERAL ... ON true或COALESCE兜底需要序号用WITH ORDINALITY很多人不知道这个隐藏特性大数据量先WHERE过滤再展开避免全表jsonb_array_elements包含查询走 GIN 索引不要用-做全表扫描