提取嵌套数据： jsonb_to_recordset()

接下来我们要聊点更高级的内容，就是怎么处理 JSONB 格式的数据——把嵌套的数据提出来，变成表格的行。你可能会问，这有啥用？其实很简单！想象一下，你拿到一个包含购买记录的 JSON 对象数组，老板让你算一下所有购买的总金额，或者把它们变成表格做个报表。我们现在就来搞定它！

为啥不能直接把 JSON 当文本或者结构来用？举个例子。在很多实际应用里，数据都是以 JSON 数组的形式存的：

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

这样存确实方便，但分析数据的时候，经常需要把数组变成表格，比如做过滤、排序、聚合操作。想象一下：“所有订单金额大于 500 美元的”。单靠 JSONB 本身没法很方便地做到这些。这时候 jsonb_to_recordset() 就派上用场了。

用 jsonb_to_recordset() 干活

jsonb_to_recordset() 这个函数能把 JSONB 对象数组转成表格的行。它会把数组里的每个元素变成一行，把 key 变成列名。遇到嵌套很深或者有对象数组的数据，这个函数简直神器。

语法

SELECT *
FROM jsonb_to_recordset('[ JSONB 数组 ]') AS alias(列1 类型, 列2 类型, ...);

• [ JSONB 数组 ]：就是你要提取数据的 JSON 对象数组。
• AS alias：给结果表起个临时名字。
• 列1 类型, 列2 类型：定义每一列叫什么名，用什么数据类型（比如 INTEGER、TEXT、NUMERIC）。

例子：把 JSONB 数组转成表格行

假设我们有这样一张表：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

表里有如下数据：

现在的任务是：把所有订单里的所有产品都列出来，变成表格。用 jsonb_to_recordset() 就很简单：

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

结果：

例子：数据过滤

再来点难度。比如我们只想看那些价格大于 100 美元的产品：

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

结果：

例子：数据聚合

比如要统计每个订单所有产品的总金额？直接用聚合函数就行：

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

结果：

重要提示

一定要保证 JSON 数组里每个对象的结构都一样。如果有的对象 key 不一样或者嵌套结构不同，可能会报错或者结果很奇怪。

提取出来的列类型要写对。比如 key 是日期就用 DATE，数字就用 NUMERIC 或 INTEGER。

注意 jsonb_to_recordset() 只能处理 JSONB 数组，不能直接处理单个对象。

常见错误和防坑指南

数据类型用错：如果 JSONB 数组里有的值类型不一样（比如有的是字符串有的是数字），会报错。建议用这个函数前先把数据格式处理好。

key 写错：如果数组里有的对象没有某个 key，也会报错。写 SQL 前先检查下数据结构。

没有数据：如果 JSONB 列是空的（NULL），这个函数不会返回任何结果。这种情况可以用 COALESCE() 做下判断。

实际应用场景

jsonb_to_recordset() 在实际开发里用得超多，比如订单处理、报表分析、用户操作日志、外部 API 数据处理等等。举几个例子：

• 电商网站可以很方便地把产品数组转成表格，做各种报表。
• REST API 返回 JSON 数据时，用 PostgreSQL 直接分析很爽。
• 做数据分析的应用，经常用这个函数处理多层嵌套的数据。