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 索引不要用-做全表扫描

相关新闻

东方甄选俞敏洪,董宇辉与辉同行,同时在“去个人化“

东方甄选俞敏洪,董宇辉与辉同行,同时在“去个人化“

利润暴涨90倍,却没了最能带货的人 东方甄选近期发布的业绩预告,在舆论场里堪称一场"商业奇迹":利润同比暴涨近90倍。人们纷纷赞扬俞敏洪的人格魅力与战略定力。但拨开情绪,这件事真正值得讨论的,不是俞敏洪是…

2026/11/19 14:43:43 阅读更多 →
LinkSwift:网盘直链下载助手的终极使用指南

LinkSwift:网盘直链下载助手的终极使用指南

LinkSwift:网盘直链下载助手的终极使用指南 【免费下载链接】Online-disk-direct-link-download-assistant 一个基于 JavaScript 的网盘文件下载地址获取工具。基于【网盘直链下载助手】修改 ,支持 百度网盘 / 阿里云盘 / 中国移动云盘 / 天翼云盘 / 迅雷…

2026/11/18 13:41:21 阅读更多 →
Vue+SpringBoot智能交通管控系统开发实践

Vue+SpringBoot智能交通管控系统开发实践

1. 项目背景与核心需求交通管理系统作为现代城市基础设施的重要组成部分,其智能化升级已成为必然趋势。传统交通管理系统往往存在响应速度慢、数据可视化程度低、系统扩展性差等问题。基于VueSpringBoot的技术栈构建的智能交通管控平台,正是为了解决这些…

2026/11/18 1:37:43 阅读更多 →

最新新闻

Electron桌面应用开发实战:从环境搭建到打包发布全流程

Electron桌面应用开发实战:从环境搭建到打包发布全流程

做前端这些年,我大部分时间都在跟浏览器标签页打交道,直到第一次把桌面应用跑起来,才发现原来自己一直被限制在地址栏和刷新按钮围起来的框框里。Electron这个词,最开始是在某段时间频繁出现在同事的讨论里,一个用HTML…

2026/11/18 13:59:04 阅读更多 →
滑盖智能锁T109深度解析:结构原理、安装调试与故障排查

滑盖智能锁T109深度解析:结构原理、安装调试与故障排查

简介:亚太天能T109滑盖智能锁产品说明书是一份PDF格式的官方技术文档,主要面向智能锁安装人员、终端用户及售后维护者,帮助快速掌握产品安装、配置与常见故障处理。全包仅1个PDF文件,大小4.37MB,内容结构清晰&#xff…

2026/11/18 15:33:23 阅读更多 →
PMSM弱磁控制原理与Simulink建模:高速AGV驱动系统设计指南

PMSM弱磁控制原理与Simulink建模:高速AGV驱动系统设计指南

上个月我在调试一台高速AGV的驱动系统,整车要求空载跑到8m/s,电机转速已经拉到额定点,驱动器的输出电压也顶到了母线电压极限附近。再往上踩速度指令,转速纹丝不动,三相电流反而开始震荡。那会儿我的第一反应就是&…

2026/11/18 17:05:28 阅读更多 →
C++核心知识点:从内存管理到STL的实战指南

C++核心知识点:从内存管理到STL的实战指南

C 这门语言我前前后后折腾了十几年,用它写过嵌入式驱动、做过游戏服务端,也搭过基础组件库。每次看到新人一头扎进"C 核心知识点"这个搜索词,我其实挺感慨的——这门语言的难点从来不是语法多,而是知识点之间的耦合太深…

2026/11/18 14:33:19 阅读更多 →
T109滑盖智能锁怎么装?安装调试与故障排查全攻略

T109滑盖智能锁怎么装?安装调试与故障排查全攻略

简介:亚太天能T109滑盖智能锁产品说明书以PDF格式发布,适合智能锁用户、安装人员与售后维修人员阅读。内容围绕T109系列指纹密码刷卡锁展开,先介绍前面板、后面板、锁体、机械钥匙、电池盒盖等部件名称与功能,再给出完整安装步骤&…

2026/11/18 17:05:28 阅读更多 →
LLM知识降熵:从信息垄断到理解权下放

LLM知识降熵:从信息垄断到理解权下放

你有没有过这种经历:翻开源码注释、论文摘要或者一份专业报告,每个字都认识,连在一起却像天书。想找人聊聊,身边要么没人懂,要么懂的懒得跟你解释。后来我试着把同一段内容丢给一个大语言模型,几秒钟之后&a…

2026/11/18 14:13:08 阅读更多 →

日新闻

周新闻

月新闻