PostgreSQL 嵌套 JSON 数据提取实战：json_extract_path 与路径提取函数全解（TIL）

文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载导读本文围绕 TIL 仓库中 postgres/extracting-nested-json-data.md 记录的实战经验系统讲解如何在 PostgreSQL 的 JSON 列中按路径一次性取出深层嵌套值。你将掌握json_extract_path/jsonb_extract_path及其文本版本的使用方法、#/#运算符的等价写法、变长路径参数与数组下标的细节并了解与提取配合的类型判断、美化输出等配套技巧。场景JSON 列里存了嵌套数据在 PostgreSQL 中把 JSON 数据整体存进一列是很常见的做法——订单、配置、用户资料等结构化程度较低的数据都可以直接以 JSON 形式落库。但当业务代码需要访问这些 JSON 内部的具体字段时麻烦就来了。比如下面的owner字段顶层是一个对象license又是一个嵌套对象真正的目标值number藏在了第三层owner -------------------------------------------------------------------------------- { name: Jason Borne, license: { number: T1234F5G6, state: MA } }如果数据库代码需要拿到驾照号T1234F5G6该如何编写查询这正是原 TIL 要解决的第一个问题如何从嵌套 JSON 中按路径取值。为什么单靠-运算符不够用熟悉 PostgreSQL JSON 的人第一反应通常是-运算符。它的作用是按键取字段但一次只能向下取一层-- 取到 license 对象 select owner - license from some_table; -- 还需要再链一层才能到 number select owner - license - number from some_table;也就是说面对两层以上的嵌套-必须不断链式拼接owner-license-number。路径层级越深表达式越长越啰嗦更麻烦的是当路径本身是动态的——例如路径片段来自变量、来自代码拼接、或已经存放在一个text[]数组里——这种链式写法就难以应付。这正是原 TIL 的核心结论仅靠-运算符派不上用场需要改用json_extract_path函数由函数接收完整路径作为参数一次调用直达目标。核心函数json_extract_path 一次性提取嵌套值json_extract_path属于 PostgreSQL 的 JSON 处理函数第一个参数是 JSON 文档后面的参数依次是路径的每一层键名 select json_extract_path(owner, license, number) from some_table; json_extract_path ------------------- T1234F5G6与链式-不同json_extract_path把整条路径作为可变长参数列表传入语义清晰、层级可控尤其适合路径由程序动态构造的场景。其完整函数签名对应json类型为json_extract_path(from_json json, VARIADIC path_elems text[])调用时传入的license、number等字符串即被收集为一个text[]路径数组。路径不存在时的行为如果指定的路径在 JSON 中不存在例如键拼写错误、中间节点缺失json_extract_path会返回NULL而不是报错。这一点在数据清洗或防御性查询中很有价值——可以在提取后配合COALESCE提供默认值例如select coalesce(json_extract_path(owner, license, number), UNKNOWN) from some_table;json 与 jsonb四个提取函数对照原 TIL 的示例基于json类型。实际开发中更常见的是jsonb二进制存储、解析后的 JSONB 类型它同样有对应的提取函数。四个函数形成一张完整的对照表函数输入类型返回类型说明json_extract_path(json, text[])jsonjson返回目标值保持 JSON 类型json_extract_path_text(json, text[])jsontext返回目标值的纯文本形式jsonb_extract_path(jsonb, text[])jsonbjsonb返回目标值保持 JSONB 类型jsonb_extract_path_text(jsonb, text[])jsonbtext返回目标值的纯文本形式使用规则与json版本完全一致-- jsonb 列同样传路径 select jsonb_extract_path(owner, license, number) from some_table; -- 需要不带引号的纯文本 select jsonb_extract_path_text(owner, license, number) from some_table;返回类型带来的引号差异注意返回类型的差别*_extract_path返回的是 JSON 类型的值如果目标是一个 JSON 字符串客户端展示时会带上 JSON 的引号而*_extract_path_text直接返回text得到的就是T1234F5G6这样的裸字符串。后续要把结果用于字符串拼接、比较或嵌入文本时优先选用_text版本。为什么推荐 jsonb仓库中的 postgres/determine-types-of-jsonb-records.md 记录了jsonb的实用背景jsonb列里可以存放对象、数组、字符串、数字、布尔、null 等多种值且存储时已做解析访问和提取都更高效、更规范。如果业务从零开始设计表结构jsonb是更主流的选择配合jsonb_extract_path系列函数即可完成同样的嵌套提取。运算符等价写法# 与 #如果你更喜欢运算符风格PostgreSQL 为路径提取提供了#和#它们是json_extract_path的运算符形态json与jsonb均支持-- 等价于 json_extract_path(owner, license, number) select owner # {license, number} from some_table; -- 等价于 json_extract_path_text(owner, license, number) select owner # {license, number} from some_table;区别与上面一致#返回 JSON 类型#返回纯文本。此时路径以{license, number}这种文本数组字面量形式给出同样适用于路径先拼好、再传入查询的动态场景。#与json_extract_path属于同一套底层能力二者可以按代码风格任选。路径参数进阶可变长参数与数组下标json_extract_path的VARIADIC签名意味着两件事可以逐个传参json_extract_path(owner, license, number)与可以直接传数组先构造好路径数组再展开传入例如-- 路径来自一个 text[] 数组 select json_extract_path(owner, variadic array[license, number]) from some_table;两者等价适合在不同调用场景中选用。用数字字符串访问数组元素路径中的键名是字符串但如果某一段要访问的是数组下标直接把下标写成字符串即可-- 假设字段形如 {tags: [a, b, c]} select json_extract_path(doc, tags, 1) from some_table; -- 返回 b1会被按数组下标解释从而支持对 JSON 数组内元素的定位提取。与嵌套对象键混合使用时规则同样成立路径中每一段要么是对象键要么是数组下标。配套技巧与路径提取搭配的 JSON 实战嵌套提取只是 JSON 列处理的一环仓库中还有多篇 TIL 可以与它组合使用构成完整的 JSON 数据处理工具箱判断顶层值类型提取之前先用 postgres/determine-types-of-jsonb-records.md 记录的jsonb_typeof(my_jsonb_column)确认目标到底是对象、数组还是标量避免对路径形态做错误假设。美化查看整行嵌套 JSON 默认在一行里挤成一团难以阅读用 postgres/pretty-printing-jsonb-rows.md 中的jsonb_pretty(...)可以展开成缩进格式便于肉眼核对提取结果的上下文。写入含引号/特殊字符的 JSON往测试表里灌 JSON 数据时postgres/label-dollar-quoted-strings-with-a-tag.md 与 postgres/escaping-string-literals-with-dollar-quoting.md 记录的美元引用如$JSON$...$JSON$::jsonb可以免去转义烦恼。查询可用运算符全集想了解jsonb还能配合哪些运算符如包含关系可用 postgres/show-all-versions-of-an-operator.md 中的\do 在 psql 里直接列出其全部参数类型组合。版本与适用前提json类型自 PostgreSQL 9.2 起可用jsonb自 9.4 起可用json_extract_path/json_extract_path_text及#/#运算符自 9.3 起提供jsonb_extract_path/jsonb_extract_path_text随 9.4 的jsonb一并提供。原 TIL 引用的文档即为 9.4 版本。本文示例均沿用原 TIL 的json列写法若你的表使用jsonb列请对应改用jsonb_extract_path系列函数参数用法完全一致。小结面对 PostgreSQL JSON 列中的多层嵌套数据json_extract_path提供了一条直达路径的提取方式函数签名直观、支持变长参数与数组下标、路径缺失时安全返回NULL。配合_text版本去除引号、#/#运算符切换写法以及仓库内其他 JSON 相关 TIL 的配套技巧足以覆盖从存 JSON到取嵌套值的完整开发场景。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐Apache Druid SQL JSON 函数完全指南解析、提取、转换与构造嵌套数据Apache Druid SQL JSON 函数完全指南解析、提取、转换与构造嵌套数据 Druid 通过内建的 SQL JSON 函数族支持对 COMPLEX数据库OLAP大数据后端YOLOv8实时目标检测与AI辅助瞄准系统架构设计YOLOv8实时目标检测与AI辅助瞄准系统架构设计 技术架构概述与核心实现原理 YOLOv8自瞄系统是一个基于深度学习目标检测技术的实时计算机视觉应用专为FP人工智能计算机视觉游戏开发draw.io 桌面版 Windows 安装完整指南免费离线绘图工具一次搞定draw.io 桌面版 Windows 安装完整指南免费离线绘图工具一次搞定 drawio desktop 是 draw.io 官方出品的桌面端基于 Ele数据库OLAP数据仓库大数据湖仓一体数据分析创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考