从JSON对象提取数据

JSONB 超强大，可以存很复杂的数据结构，比如嵌套对象或者数组。但光存数据还不够——我们还得会把这些数据拿出来。比如，你有个 data 列在 users 表里，所有用户设置都用JSONB格式存着。想知道用户选了啥主题？那就得从JSONB对象里把它抠出来。

如果说JSONB是个藏宝箱，那 ->、->>、#>> 这些操作符，还有 jsonb_extract_path() 这种函数，就是你的钥匙。来，咱们看看怎么用。

JSONB常用操作符

PostgreSQL给JSONB准备了好几个关键操作符。它们能让你从key、嵌套对象、数组里把值提出来。主要有这些：

操作符 ->

-> 操作符能按key提取 对象或数组。如果你想拿到和JSON格式一样的值，就用它。

例子：

-- 数据示例
SELECT '{"name": "Alice", "age": 25}'::jsonb -> 'name';
-- 结果: "Alice"

操作符 ->>

->> 跟 -> 差不多，但它会把提取出来的值变成 文本。想要直接拿到字符串就用它。

例子：

-- 数据示例
SELECT '{"name": "Alice", "age": 25}'::jsonb ->> 'age';
-- 结果: "25" (字符串)

操作符 #>>

#>> 能按路径从嵌套对象里提数据。路径要用key数组的形式传。

例子：

-- 数据示例
SELECT '{"user": {"name": "Bob", "details": {"age": 30}}}'::jsonb #>> '{user, details, age}';
-- 结果: "30" (字符串)

-> 和 ->> 的区别：

如果你想保留数据类型（比如数组或对象），用 ->。要文本就用 ->>。

JSONB相关函数用法

jsonb_extract_path() 函数能按路径从JSONB对象里提值。它其实就是 #>> 的函数版，但写起来更直观。

例子：

SELECT jsonb_extract_path('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- 结果: "dark"

如果你想直接拿到文本值，用 jsonb_extract_path_text()。它和 jsonb_extract_path() 一样，就是返回字符串。

例子：

SELECT jsonb_extract_path_text('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- 结果: dark

实用例子

按key提取值。 假设有个 products 表，details 列里存的是JSONB数据：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "Intel i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 1000, "specs": {"ram": "4GB", "cpu": "A13"}}');

结果：

提取所有产品的品牌。

SELECT name, details->'brand' AS brand FROM products;

结果：

提取文本值。 如果想要没有引号的品牌名，就用 ->>：

SELECT name, details->>'brand' AS brand FROM products;

结果：

提取嵌套数据。 提取每个产品的内存（ram）：

SELECT name, details#>>'{specs, ram}' AS ram FROM products;

结果：

按路径提取数据。 用 jsonb_extract_path_text() 也能做到：

SELECT name, jsonb_extract_path_text(details, 'specs', 'ram') AS ram FROM products;

结果：

常见错误和怎么避免

常见的坑有：

• 路径写错了。比如key不存在，结果就是 null。
• 操作符用错了。-> 适合提对象和数组，要文本得用 ->>。

错误示例：

-- 错误：没有 'nonexistent' 这个key
SELECT details->>'nonexistent' FROM products;
-- 结果: null

小建议：写SQL前先看看数据结构，避免踩坑。

实际应用场景

JSONB数据提取在很多实际项目里都用得到：

• 电商里处理商品属性。
• Web应用里存用户设置。
• 数据分析时处理结构化数据，比如事件、日志啥的。

再举个例子。假设有个 orders 表，存着订单数据：

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

INSERT INTO orders (customer_name, items) VALUES
    ('John', '[{"product": "Laptop", "quantity": 1}, {"product": "Mouse", "quantity": 2}]'),
    ('Alice', '[{"product": "Phone", "quantity": 1}]');

提取所有订单里的产品名：

SELECT customer_name, jsonb_array_elements(items)->>'product' AS product FROM orders;

结果：

接下来我们会更深入地玩JSONB，研究嵌套数据和怎么把它们变成更方便分析的样子。准备好继续探索吧！