PostgreSQL / 系统课时
读取对象与嵌套字段
读取对象与嵌套字段
JSONB 查询最常见的第一步,是从对象中取出顶层字段或嵌套字段,并明确结果究竟还是 JSONB,还是已经变成可参与普通文本比较的 SQL 文本。掌握 ->、->>、#> 与 #>>,就能避免大量类型不匹配问题。
学习目标
- 使用
->与->>读取顶层对象字段,并区分 JSONB 与文本返回值 - 使用
#>与#>>按路径读取嵌套字段 - 根据后续比较、排序或继续 JSON 运算的需要选择正确运算符
从顶层字段开始
先建立一张最小示例表。这里把商品动态属性放在 jsonb 列中,既包含字符串,也包含嵌套对象。
DROP TABLE IF EXISTS product_demo;
CREATE TABLE product_demo (
id integer PRIMARY KEY,
attrs jsonb NOT NULL
);
INSERT INTO product_demo (id, attrs) VALUES
(1, '{"name":"Keyboard","brand":{"name":"Acme","country":"US"},"stock":12}'),
(2, '{"name":"Mouse","brand":{"name":"Nova","country":"DE"},"stock":8}');
SELECT
id,
attrs -> 'name' AS name_jsonb,
attrs ->> 'name' AS name_text
FROM product_demo
ORDER BY id;预期结果:
id | name_jsonb | name_text
----+------------+-----------
1 | "Keyboard" | Keyboard
2 | "Mouse" | Mouse-> 返回 JSONB,所以 JSON 字符串仍保留双引号;->> 返回 SQL text,字符串内容已经被提取出来。后续如果还要继续使用 JSONB 运算符,通常保留 ->;如果要和普通文本比较、拼接或排序,通常直接使用 ->>。
读取嵌套对象
嵌套字段可以逐层使用 -> 和 ->>,也可以使用路径运算符一次定位。下面两种写法读取的都是品牌名称。
SELECT
id,
attrs -> 'brand' ->> 'name' AS chained_name,
attrs #>> '{brand,name}' AS path_name,
attrs #> '{brand}' AS brand_jsonb
FROM product_demo
ORDER BY id;预期结果:
id | chained_name | path_name | brand_jsonb
----+--------------+-----------+--------------------------------
1 | Acme | Acme | {"name": "Acme", "country": "US"}
2 | Nova | Nova | {"name": "Nova", "country": "DE"}#> 按路径返回 JSONB,#>> 按路径返回文本。路径写成 PostgreSQL 文本数组字面量,例如 '{brand,name}'。对于固定而较深的路径,#> 和 #>> 往往比连续写多个运算符更紧凑。
选择 JSONB 结果还是文本结果
运算符的关键差别不是“能不能取到值”,而是“取到后是什么 SQL 类型”。可以直接用 pg_typeof 验证。
SELECT
pg_typeof(attrs -> 'name') AS arrow_type,
pg_typeof(attrs ->> 'name') AS arrow_text_type,
pg_typeof(attrs #> '{brand,name}') AS path_type,
pg_typeof(attrs #>> '{brand,name}') AS path_text_type
FROM product_demo
LIMIT 1;预期结果:
arrow_type | arrow_text_type | path_type | path_text_type
------------+-----------------+-----------+----------------
jsonb | text | jsonb | text当表达式右侧仍需要 JSONB 语义时,不要过早转成文本。例如判断一个子对象是否包含某个键,可以先用 -> 取得对象,再继续应用 JSONB 运算符;反之,如果最终只是与字符串常量比较,->> 或 #>> 更直接。
注意不存在的路径
这些提取运算符在键或路径不存在时不会报错,而是返回 SQL NULL。查询本身仍然成功,因此“路径不存在”和“取到了某个文本值”必须由后续条件显式区分。
SELECT
id,
attrs ->> 'color' AS color,
attrs #>> '{brand,city}' AS brand_city
FROM product_demo
ORDER BY id;预期结果:
id | color | brand_city
----+-------+------------
1 | |
2 | |在 psql 默认显示中,SQL NULL 通常表现为空白。后续如果要判断缺失键、JSON null 或类型转换失败,不能只看屏幕上的空白,而要使用明确的 NULL 判断与 JSONB 语义。