PostgreSQL / 系统课时

读取对象与嵌套字段

读取对象与嵌套字段

JSONB 查询最常见的第一步,是从对象中取出顶层字段或嵌套字段,并明确结果究竟还是 JSONB,还是已经变成可参与普通文本比较的 SQL 文本。掌握 ->、->>、#> 与 #>>,就能避免大量类型不匹配问题。

学习目标

  • 使用 -> 与 ->> 读取顶层对象字段,并区分 JSONB 与文本返回值
  • 使用 #> 与 #>> 按路径读取嵌套字段
  • 根据后续比较、排序或继续 JSON 运算的需要选择正确运算符

从顶层字段开始

先建立一张最小示例表。这里把商品动态属性放在 jsonb 列中,既包含字符串,也包含嵌套对象。

sql
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;

预期结果:

text
 id | name_jsonb | name_text
----+------------+-----------
  1 | "Keyboard" | Keyboard
  2 | "Mouse"    | Mouse

-> 返回 JSONB,所以 JSON 字符串仍保留双引号;->> 返回 SQL text,字符串内容已经被提取出来。后续如果还要继续使用 JSONB 运算符,通常保留 ->;如果要和普通文本比较、拼接或排序,通常直接使用 ->>。

读取嵌套对象

嵌套字段可以逐层使用 -> 和 ->>,也可以使用路径运算符一次定位。下面两种写法读取的都是品牌名称。

sql
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;

预期结果:

text
 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 验证。

sql
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;

预期结果:

text
 arrow_type | arrow_text_type | path_type | path_text_type
------------+-----------------+-----------+----------------
 jsonb      | text            | jsonb     | text

当表达式右侧仍需要 JSONB 语义时,不要过早转成文本。例如判断一个子对象是否包含某个键,可以先用 -> 取得对象,再继续应用 JSONB 运算符;反之,如果最终只是与字符串常量比较,->> 或 #>> 更直接。

注意不存在的路径

这些提取运算符在键或路径不存在时不会报错,而是返回 SQL NULL。查询本身仍然成功,因此“路径不存在”和“取到了某个文本值”必须由后续条件显式区分。

sql
SELECT
    id,
    attrs ->> 'color' AS color,
    attrs #>> '{brand,city}' AS brand_city
FROM product_demo
ORDER BY id;

预期结果:

text
 id | color | brand_city
----+-------+------------
  1 |       |
  2 |       |

在 psql 默认显示中,SQL NULL 通常表现为空白。后续如果要判断缺失键、JSON null 或类型转换失败,不能只看屏幕上的空白,而要使用明确的 NULL 判断与 JSONB 语义。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

读取对象与嵌套字段

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通