企业官网建设流程全解析

• 文档
• 教程
• 知识库

导读：本文围绕 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版本完全一致：

-- 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签名意味着两件事：

1. 可以逐个传参：json_extract_path(owner, 'license', 'number')与
2. 可以直接传数组：先构造好路径数组再展开传入，例如

-- 路径来自一个 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; -- 返回 "b"

'1'会被按数组下标解释，从而支持对 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"到"取嵌套值"的完整开发场景。

• 文档
• 教程
• 知识库