PostgreSQL JSON 函数和操作符

概述

PostgreSQL 为 JSON 数据类型提供了强大的原生支持，实现了完整的 SQL/JSON 数据模型。该模型遵循 JSON 规范 RFC 7159，允许在 SQL 环境中无缝处理 JSON 数据，包括存储、查询、修改和创建 JSON 数据。

SQL/JSON 数据模型

PostgreSQL 的 SQL/JSON 数据模型包含一系列项，每一项可以包含：

SQL 标量值（字符串、数字、布尔值）
SQL/JSON 空值（null）
复合数据结构（JSON 数组和对象）

PostgreSQL JSON 支持的核心功能

数据存储：将 JSON 数据作为字符或二进制字符串存储在 SQL 列中
数据生成：从关系数据生成 JSON 对象和数组
数据查询：使用 SQL/JSON 查询函数和路径语言表达式查询 JSON 数据
事务支持：完整的 ACID 事务支持

JSON 数据类型对比

PostgreSQL 提供两种 JSON 数据类型，各有特点：

特性	json	jsonb
存储方式	文本格式	二进制格式
输入速度	快	稍慢（需要转换）
查询速度	慢	快
索引支持	有限	完整的 GIN 索引支持
键排序	保持原始顺序	重新排序
重复键	保留所有重复键	只保留最后一个
比较运算符	不支持	支持完整比较

选择建议

如果只需要存储 JSON 数据而很少查询，选择 json
如果需要频繁查询、索引或操作 JSON 数据，选择 jsonb
大多数现代应用建议使用 jsonb

JSON 操作符

基本访问操作符

操作符	左操作数	右操作数	返回类型	描述	示例
`->`	json/jsonb	int	json/jsonb	获取数组元素（从 0 开始）	`'[1,2,3]'::json->1` → `2`
`->`	json/jsonb	text	json/jsonb	获取对象字段	`'{"a":1}'::json->'a'` → `1`
`->>`	json/jsonb	int	text	获取数组元素为文本	`'[1,2,3]'::json->>1` → `"2"`
`->>`	json/jsonb	text	text	获取对象字段为文本	`'{"a":1}'::json->>'a'` → `"1"`
`#>`	json/jsonb	text[]	json/jsonb	按路径获取对象	`'{"a":{"b":2}}'::json#>'{a,b}'` → `2`
`#>>`	json/jsonb	text[]	text	按路径获取对象为文本	`'{"a":{"b":2}}'::json#>>'{a,b}'` → `"2"`

jsonb 专用操作符

| 操作符 | 左操作数 | 右操作数 | 返回类型 | 描述 | | ------ | -------- | -------- | -------- | ------------------------------------ | ---------------------------------- | ---------------- | | @> | jsonb | jsonb | boolean | 左边 JSON 是否包含右边 JSON | | <@ | jsonb | jsonb | boolean | 左边 JSON 是否被右边 JSON 包含 | | ? | jsonb | text | boolean | 字符串是否作为顶层键存在 | | ? | | jsonb | text[] | boolean | 数组中任一字符串是否作为顶层键存在 | | ?& | jsonb | text[] | boolean | 数组中所有字符串是否都作为顶层键存在 | | | | | jsonb | jsonb | jsonb | 连接两个 JSON 值 | | - | jsonb | text | jsonb | 删除键/值对 | | - | jsonb | int | jsonb | 删除数组元素 | | #- | jsonb | text[] | jsonb | 删除指定路径的字段 |

实际业务场景示例

示例 1：电商产品管理系统

假设我们有一个电商系统，需要存储和查询产品信息：

sql

-- 创建产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    details JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例数据
INSERT INTO products (name, details) VALUES
('智能手机', '{
    "brand": "Apple",
    "model": "iPhone 15",
    "specs": {
        "screen": "6.1英寸",
        "storage": ["128GB", "256GB", "512GB"],
        "colors": ["黑色", "白色", "蓝色"],
        "camera": {
            "main": "48MP",
            "front": "12MP"
        }
    },
    "price": 5999,
    "in_stock": true,
    "tags": ["5G", "Face ID", "无线充电"]
}'),
('笔记本电脑', '{
    "brand": "Dell",
    "model": "XPS 13",
    "specs": {
        "cpu": "Intel i7",
        "ram": "16GB",
        "storage": "512GB SSD",
        "screen": "13.3英寸 4K"
    },
    "price": 12999,
    "in_stock": false,
    "tags": ["轻薄", "4K屏幕", "长续航"]
}');

问题陈述：查询所有有库存且价格在 10000 以下的苹果产品。

解决方案：

sql

SELECT
    name,
    details->>'brand' as brand,
    details->>'model' as model,
    (details->>'price')::int as price,
    details->>'in_stock' as in_stock
FROM products
WHERE details->>'brand' = 'Apple'
  AND details->>'in_stock' = 'true'
  AND (details->>'price')::int < 10000;

分析过程：

details->>'brand'：使用 ->> 操作符提取品牌信息为文本类型
details->>'in_stock' = 'true'：注意 JSON 布尔值提取后为文本，需要与字符串比较
(details->>'price')::int：将价格从文本转换为整数进行数值比较
查询使用了 JSONB 字段的高效索引查找

输入和输出：

sql

-- 输出结果
name    | brand | model     | price | in_stock
--------|-------|-----------|-------|----------
智能手机 | Apple | iPhone 15 | 5999  | true

示例 2：用户行为分析系统

问题陈述：分析用户活动日志，找出使用特定功能的用户。

sql

-- 创建用户活动表
CREATE TABLE user_activities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    activity_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例数据
INSERT INTO user_activities (user_id, activity_data) VALUES
(101, '{
    "action": "product_view",
    "product_id": 12345,
    "categories": ["电子产品", "手机"],
    "device": {
        "type": "mobile",
        "os": "iOS",
        "version": "17.0"
    },
    "location": "北京",
    "duration": 120
}'),
(102, '{
    "action": "purchase",
    "order_id": 98765,
    "items": [
        {"product_id": 12345, "quantity": 1, "price": 5999},
        {"product_id": 67890, "quantity": 2, "price": 299}
    ],
    "payment_method": "credit_card",
    "total": 6597
}'),
(103, '{
    "action": "search",
    "query": "苹果手机",
    "filters": {
        "price_range": [3000, 8000],
        "brand": ["Apple", "Samsung"]
    },
    "results_count": 25
}');

问题陈述：查找所有在移动设备上进行产品浏览的用户活动。

解决方案：

sql

SELECT
    user_id,
    activity_data->>'action' as action,
    activity_data->'device'->>'type' as device_type,
    activity_data->'device'->>'os' as device_os,
    activity_data->>'duration' as duration
FROM user_activities
WHERE activity_data->>'action' = 'product_view'
  AND activity_data->'device'->>'type' = 'mobile';

分析过程：

activity_data->'device'->>'type'：使用链式操作符访问嵌套 JSON 对象
先用 -> 获取 device 对象，再用 ->> 获取 type 字段的文本值
组合条件筛选特定行为和设备类型

输入和输出：

sql

-- 输出结果
user_id | action       | device_type | device_os | duration
--------|--------------|-------------|-----------|----------
101     | product_view | mobile      | iOS       | 120

示例 3：使用 JSONB 包含操作符

问题陈述：查找包含特定标签或属性的产品。

解决方案：

sql

-- 查找包含"5G"标签的产品
SELECT name, details->'tags' as tags
FROM products
WHERE details->'tags' @> '["5G"]';

-- 查找具有特定相机配置的产品
SELECT name, details->'specs'->'camera' as camera
FROM products
WHERE details->'specs'->'camera' @> '{"main": "48MP"}';

-- 查找价格范围在5000-8000的产品
SELECT name, details->>'price' as price
FROM products
WHERE details @> '{"price": 5999}'
   OR (details->>'price')::int BETWEEN 5000 AND 8000;

分析过程：

@> 操作符检查左边 JSON 是否包含右边 JSON
details->'tags' @> '["5G"]'：检查 tags 数组是否包含"5G"
details->'specs'->'camera' @> '{"main": "48MP"}'：检查嵌套对象是否包含指定键值对
JSONB 的包含操作符比文本匹配更高效且更精确

JSON 创建和操作函数

JSON 构造函数

函数	返回类型	描述	示例
`json_build_array(...)`	json	构建 JSON 数组	`json_build_array(1,2,'foo')` → `[1,2,"foo"]`
`json_build_object(...)`	json	构建 JSON 对象	`json_build_object('name','John','age',30)` → `{"name":"John","age":30}`
`json_object(text[])`	json	从文本数组构建对象	`json_object('{a,1,b,2}')` → `{"a":"1","b":"2"}`
`json_array_elements(json)`	setof json	展开 JSON 数组为行	`json_array_elements('[1,2,3]')` → 3 行
`json_each(json)`	setof record	展开 JSON 对象为键值对	`json_each('{"a":1,"b":2}')` → (a,1), (b,2)

实际应用示例：动态报表生成

问题陈述：根据查询结果动态生成 JSON 格式的报表。

sql

-- 生成产品销售报表
WITH sales_data AS (
    SELECT
        details->>'brand' as brand,
        COUNT(*) as product_count,
        AVG((details->>'price')::int) as avg_price,
        array_agg(name) as product_names
    FROM products
    GROUP BY details->>'brand'
)
SELECT json_build_object(
    'report_type', '产品销售统计',
    'generated_at', CURRENT_TIMESTAMP,
    'data', json_agg(
        json_build_object(
            'brand', brand,
            'product_count', product_count,
            'average_price', round(avg_price, 2),
            'products', product_names
        )
    )
) as sales_report
FROM sales_data;

分析过程：

json_build_object()：构造嵌套的 JSON 对象结构
json_agg()：将多行数据聚合为 JSON 数组
round(avg_price, 2)：数值处理后再放入 JSON
生成标准化的报表格式，便于前端消费

SQL/JSON 路径语言

路径表达式基础

SQL/JSON 路径语言提供了强大的 JSON 数据查询能力，类似于 XPath 对 XML 的作用。

路径语言语法要素

元素	语法	描述	示例
根引用	`$`	引用当前 JSON 值	`$`
键访问	`.key`	访问对象键	`$.name`
数组索引	`[index]`	访问数组元素	`$[0]`
数组通配符	`[*]`	访问所有数组元素	`$[*]`
递归下降	`.**`	递归查找所有匹配项	`$.**.name`
过滤表达式	`?(condition)`	条件过滤	`$[*] ? (@ > 10)`
当前项引用	`@`	在过滤表达式中引用当前项	`@ > 100`

实际应用示例：GPS 轨迹数据分析

假设我们有 GPS 跟踪数据需要分析：

sql

-- 创建 GPS 轨迹表
CREATE TABLE gps_tracks (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    track_data JSONB
);

-- 插入示例数据
INSERT INTO gps_tracks (user_id, track_data) VALUES (1, '{
    "track": {
        "name": "晨跑路线",
        "date": "2024-03-15",
        "segments": [
            {
                "HR": 73,
                "location": [47.763, 13.4034],
                "start_time": "2024-03-15 06:05:14",
                "duration": 300
            },
            {
                "HR": 135,
                "location": [47.706, 13.2635],
                "start_time": "2024-03-15 06:10:14",
                "duration": 420
            },
            {
                "HR": 128,
                "location": [47.689, 13.1523],
                "start_time": "2024-03-15 06:17:14",
                "duration": 380
            }
        ],
        "total_distance": 5.2,
        "total_time": 1100
    }
}');

问题陈述：使用 SQL/JSON 路径语言查询高强度运动段（心率>130）。

解决方案：

基础路径查询条件过滤查询复杂路径表达式

sql

-- 获取所有轨迹段
SELECT jsonb_path_query(track_data, '$.track.segments[*]')
FROM gps_tracks;

-- 获取所有位置信息
SELECT jsonb_path_query(track_data, '$.track.segments[*].location')
FROM gps_tracks;

sql

-- 查找心率大于130的轨迹段
SELECT jsonb_path_query(
    track_data,
    '$.track.segments[*] ? (@.HR > 130)'
) as high_intensity_segments
FROM gps_tracks;

-- 获取高强度段的心率值
SELECT jsonb_path_query(
    track_data,
    '$.track.segments[*].HR ? (@ > 130)'
) as high_hr_values
FROM gps_tracks;

sql

-- 统计高强度段的数量
SELECT jsonb_path_query(
    track_data,
    '$.track.segments[*] ? (@.HR > 130).size()'
) as high_intensity_count
FROM gps_tracks;

-- 检查是否存在高强度运动
SELECT jsonb_path_query(
    track_data,
    '$.track ? (exists(@.segments[*] ? (@.HR > 130)))'
) as has_high_intensity
FROM gps_tracks;

分析过程：

$.track.segments[*]：使用数组通配符访问所有轨迹段
? (@.HR > 130)：过滤表达式，@ 代表当前数组元素
exists(@.segments[*] ? (@.HR > 130))：嵌套的存在性检查
路径表达式支持复杂的逻辑组合和函数调用

输入和输出：

sql

-- 高强度轨迹段输出
high_intensity_segments
---------------------------------------------------------
{"HR": 135, "location": [47.706, 13.2635], "start_time": "2024-03-15 06:10:14", "duration": 420}

SQL/JSON 查询函数

三大核心查询函数

PostgreSQL 提供三个标准 SQL/JSON 查询函数，每个都有特定的用途：

函数	返回类型	用途	适用场景
`JSON_EXISTS()`	boolean	检查路径是否存在	验证 JSON 结构
`JSON_VALUE()`	text	提取标量值	获取单个值
`JSON_QUERY()`	jsonb	提取 JSON 片段	获取对象或数组

JSON_EXISTS() 函数详解

语法：

sql

JSON_EXISTS(
    context_item,
    path_expression
    [PASSING { value AS varname } [, ...]]
    [{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
) → boolean

实际应用示例：

sql

-- 检查产品是否有库存信息
SELECT
    name,
    JSON_EXISTS(details, '$.in_stock') as has_stock_info,
    JSON_EXISTS(details, '$.specs.storage') as has_storage_info
FROM products;

-- 使用参数化查询检查特定价格范围
SELECT name
FROM products
WHERE JSON_EXISTS(
    details,
    '$.price ? (@ >= $min_price && @ <= $max_price)'
    PASSING 5000 AS min_price, 10000 AS max_price
);

JSON_VALUE() 函数详解

语法：

sql

JSON_VALUE(
    context_item,
    path_expression
    [PASSING { value AS varname } [, ...]]
    [RETURNING data_type]
    [{ ERROR | NULL | DEFAULT expression } ON EMPTY]
    [{ ERROR | NULL | DEFAULT expression } ON ERROR]
) → text

实际应用示例：

sql

-- 提取产品价格并转换为数值类型
SELECT
    name,
    JSON_VALUE(details, '$.price' RETURNING int) as price,
    JSON_VALUE(details, '$.brand' DEFAULT '未知品牌' ON EMPTY) as brand
FROM products;

-- 提取嵌套属性并处理错误
SELECT
    name,
    JSON_VALUE(
        details,
        '$.specs.camera.main'
        DEFAULT '未知' ON EMPTY
    ) as main_camera
FROM products;

JSON_QUERY() 函数详解

语法：

sql

JSON_QUERY(
    context_item,
    path_expression
    [PASSING { value AS varname } [, ...]]
    [RETURNING data_type [FORMAT JSON [ENCODING UTF8]]]
    [{ WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ARRAY] WRAPPER]
    [{ KEEP | OMIT } QUOTES [ON SCALAR STRING]]
    [{ ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON EMPTY]
    [{ ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON ERROR]
) → jsonb

实际应用示例：

sql

-- 提取产品规格信息
SELECT
    name,
    JSON_QUERY(details, '$.specs') as specifications,
    JSON_QUERY(details, '$.tags' WITH WRAPPER) as tags_wrapped
FROM products;

-- 使用条件包装器处理多个结果
SELECT
    JSON_QUERY(
        details,
        '$.specs.storage[*]'
        WITH CONDITIONAL WRAPPER
    ) as storage_options
FROM products;

综合应用示例：订单分析系统

问题陈述：构建一个复杂的订单分析查询，结合使用三个 JSON 查询函数。

sql

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_data JSONB
);

-- 插入示例数据
INSERT INTO orders (customer_id, order_data) VALUES
(1001, '{
    "order_id": "ORD-2024-001",
    "date": "2024-03-15",
    "customer": {
        "name": "张三",
        "email": "[email protected]",
        "vip_level": "gold"
    },
    "items": [
        {
            "product_id": 12345,
            "name": "iPhone 15",
            "quantity": 1,
            "unit_price": 5999,
            "discount": 0.1
        },
        {
            "product_id": 67890,
            "name": "AirPods Pro",
            "quantity": 2,
            "unit_price": 1999,
            "discount": 0.05
        }
    ],
    "shipping": {
        "address": "北京市朝阳区",
        "method": "express",
        "fee": 20
    },
    "payment": {
        "method": "credit_card",
        "status": "completed"
    },
    "total": 9796.9
}');

解决方案：

sql

SELECT
    -- 使用 JSON_VALUE 提取标量值
    JSON_VALUE(order_data, '$.order_id') as order_id,
    JSON_VALUE(order_data, '$.customer.name') as customer_name,
    JSON_VALUE(order_data, '$.customer.vip_level') as vip_level,
    JSON_VALUE(order_data, '$.total' RETURNING decimal(10,2)) as total_amount,

    -- 使用 JSON_EXISTS 检查结构存在性
    JSON_EXISTS(order_data, '$.customer.vip_level') as is_vip,
    JSON_EXISTS(order_data, '$.items[*] ? (@.discount > 0)') as has_discounted_items,

    -- 使用 JSON_QUERY 提取复杂结构
    JSON_QUERY(order_data, '$.items') as order_items,
    JSON_QUERY(order_data, '$.shipping') as shipping_info,

    -- 组合使用：提取有折扣的商品
    JSON_QUERY(
        order_data,
        '$.items[*] ? (@.discount > 0)'
        WITH CONDITIONAL WRAPPER
    ) as discounted_items

FROM orders
WHERE JSON_EXISTS(order_data, '$.payment ? (@.status == "completed")');

分析过程：

JSON_VALUE 用于提取订单号、客户名称等单一标量值
JSON_EXISTS 检查 VIP 状态、折扣商品等条件的存在性
JSON_QUERY 提取完整的商品列表、配送信息等复杂对象
WITH CONDITIONAL WRAPPER 在有多个结果时自动包装为数组
WHERE 子句 使用 JSON_EXISTS 过滤已完成支付的订单

JSON_TABLE 函数

概述

JSON_TABLE 是最强大的 SQL/JSON 函数，它可以将 JSON 数据转换为关系表格形式，支持复杂的嵌套结构处理。

语法结构

sql

JSON_TABLE (
    context_item,
    path_expression [AS json_path_name]
    [PASSING { value AS varname } [, ...]]
    COLUMNS ( json_table_column [, ...] )
    [{ ERROR | EMPTY [ARRAY]} ON ERROR ]
)

-- 列定义语法
json_table_column:
    name FOR ORDINALITY
  | name type [PATH path_expression]
        [{ ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON ERROR ]
  | name type EXISTS [PATH path_expression]
        [{ ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ]
  | NESTED [PATH] path_expression [AS json_path_name]
        COLUMNS ( json_table_column [, ...] )

实际应用示例：电影数据分析

问题陈述：将复杂的电影收藏 JSON 数据转换为关系表格，便于 SQL 查询和分析。

sql

-- 创建电影收藏表
CREATE TABLE movie_collections (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    collection_data JSONB
);

-- 插入示例数据
INSERT INTO movie_collections (user_id, collection_data) VALUES (1, '{
    "user": "影迷小王",
    "favorites": [
        {
            "genre": "comedy",
            "films": [
                {
                    "title": "大话西游",
                    "year": 1995,
                    "director": "刘镇伟",
                    "rating": 9.2
                },
                {
                    "title": "喜剧之王",
                    "year": 1999,
                    "director": "周星驰",
                    "rating": 8.7
                }
            ]
        },
        {
            "genre": "action",
            "films": [
                {
                    "title": "英雄",
                    "year": 2002,
                    "director": "张艺谋",
                    "rating": 8.5
                }
            ]
        },
        {
            "genre": "drama",
            "films": [
                {
                    "title": "活着",
                    "year": 1994,
                    "director": "张艺谋",
                    "rating": 9.3
                },
                {
                    "title": "霸王别姬",
                    "year": 1993,
                    "director": "陈凯歌",
                    "rating": 9.5
                }
            ]
        }
    ]
}');

解决方案：

基础展开嵌套路径展开复杂过滤和统计

sql

-- 展开类型和电影数据
SELECT jt.*
FROM movie_collections mc,
JSON_TABLE (
    mc.collection_data,
    '$.favorites[*]'
    COLUMNS (
        genre_id FOR ORDINALITY,
        genre TEXT PATH '$.genre',
        films JSONB PATH '$.films'
    )
) AS jt;

sql

-- 使用 NESTED PATH 展开电影详细信息
SELECT jt.*
FROM movie_collections mc,
JSON_TABLE (
    mc.collection_data,
    '$.favorites[*]'
    COLUMNS (
        genre_id FOR ORDINALITY,
        genre TEXT PATH '$.genre',
        NESTED PATH '$.films[*]'
        COLUMNS (
            film_id FOR ORDINALITY,
            title TEXT PATH '$.title',
            year INTEGER PATH '$.year',
            director TEXT PATH '$.director',
            rating DECIMAL(3,1) PATH '$.rating'
        )
    )
) AS jt;

sql

-- 结合过滤条件的复杂查询
SELECT
    genre,
    COUNT(*) as film_count,
    AVG(rating) as avg_rating,
    MAX(rating) as highest_rating,
    array_agg(title ORDER BY rating DESC) as top_films
FROM movie_collections mc,
JSON_TABLE (
    mc.collection_data,
    '$.favorites[*]'
    COLUMNS (
        genre TEXT PATH '$.genre',
        NESTED PATH '$.films[*] ? (@.rating >= 8.0)'
        COLUMNS (
            title TEXT PATH '$.title',
            rating DECIMAL(3,1) PATH '$.rating'
        )
    )
) AS jt
GROUP BY genre
ORDER BY avg_rating DESC;

分析过程：

FOR ORDINALITY：自动生成行号，便于标识和排序
NESTED PATH：处理嵌套数组结构，将每部电影展开为独立行
路径过滤：$.films[*] ? (@.rating >= 8.0) 只展开高评分电影
类型转换：自动将 JSON 值转换为指定的 SQL 数据类型
聚合分析：转换后的关系数据可以直接使用 SQL 聚合函数

输入和输出：

sql

-- 嵌套展开结果示例
genre_id | genre  | film_id | title      | year | director | rating
---------|--------|---------|------------|------|----------|--------
1        | comedy | 1       | 大话西游   | 1995 | 刘镇伟   | 9.2
1        | comedy | 2       | 喜剧之王   | 1999 | 周星驰   | 8.7
2        | action | 1       | 英雄       | 2002 | 张艺谋   | 8.5
3        | drama  | 1       | 活着       | 1994 | 张艺谋   | 9.3
3        | drama  | 2       | 霸王别姬   | 1993 | 陈凯歌   | 9.5

-- 统计分析结果示例
genre  | film_count | avg_rating | highest_rating | top_films
-------|------------|------------|----------------|----------------------------------
drama  | 2          | 9.4        | 9.5           | {霸王别姬,活着}
comedy | 2          | 8.95       | 9.2           | {大话西游,喜剧之王}
action | 1          | 8.5        | 8.5           | {英雄}

性能优化和最佳实践

索引策略

GIN 索引创建查询优化示例

sql

-- 为 JSONB 列创建 GIN 索引
CREATE INDEX idx_products_details_gin ON products USING GIN (details);

-- 为特定路径创建表达式索引
CREATE INDEX idx_products_brand ON products USING BTREE ((details->>'brand'));
CREATE INDEX idx_products_price ON products USING BTREE (((details->>'price')::int));

-- 为 JSONB 路径创建 GIN 索引
CREATE INDEX idx_products_specs_gin ON products USING GIN ((details->'specs'));

sql

-- ✅ 使用索引的高效查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE details->>'brand' = 'Apple';

-- ✅ 使用 GIN 索引的包含查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE details @> '{"brand": "Apple"}';

-- ❌ 避免的低效查询
SELECT * FROM products
WHERE details::text LIKE '%Apple%';

数据类型选择建议

场景	推荐类型	原因
日志记录	`json`	写入频繁，查询较少
配置存储	`jsonb`	需要频繁查询和更新
API 响应缓存	`json`	保持原始格式
搜索和分析	`jsonb`	支持索引和复杂查询
大型文档存储	`jsonb`	压缩效率更高

常见错误和解决方案

常见陷阱

类型转换错误

sql

-- ❌ 错误：直接比较 JSON 数字
WHERE details->>'price' > 1000

-- ✅ 正确：先转换类型
WHERE (details->>'price')::int > 1000

NULL 值处理

sql

-- ❌ 错误：忽略 NULL 值
WHERE details->>'status' = 'active'

-- ✅ 正确：明确处理 NULL
WHERE details->>'status' = 'active' AND details ? 'status'

路径表达式错误

sql

-- ❌ 错误：使用 SQL 语法
WHERE JSON_EXISTS(data, '$.items[count > 0]')

-- ✅ 正确：使用 JSON 路径语法
WHERE JSON_EXISTS(data, '$.items[*] ? (@.count > 0)')

实际项目应用场景

场景 1：内容管理系统

sql

-- 文章表设计
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content JSONB,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 复杂查询：搜索包含特定标签和作者的文章
SELECT
    title,
    JSON_VALUE(metadata, '$.author') as author,
    JSON_QUERY(metadata, '$.tags') as tags
FROM articles
WHERE JSON_EXISTS(metadata, '$.tags[*] ? (@ == $tag)' PASSING '技术' AS tag)
  AND JSON_VALUE(metadata, '$.status') = 'published'
ORDER BY created_at DESC;

场景 2：物联网数据处理

sql

-- 传感器数据表
CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(50),
    readings JSONB,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分析温度异常的传感器
SELECT
    device_id,
    timestamp,
    readings
FROM sensor_data
WHERE JSON_EXISTS(
    readings,
    '$.sensors[*] ? (@.type == "temperature" && @.value > 40)'
);

场景 3：用户个性化配置

sql

-- 用户设置表
CREATE TABLE user_settings (
    user_id INTEGER PRIMARY KEY,
    preferences JSONB,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 获取启用了特定功能的用户
SELECT user_id
FROM user_settings
WHERE JSON_VALUE(preferences, '$.notifications.email' RETURNING boolean) = true
  AND JSON_EXISTS(preferences, '$.features.advanced_mode')
  AND JSON_VALUE(preferences, '$.features.advanced_mode' RETURNING boolean) = true;

通过这些全面的示例和最佳实践，我们可以看到 PostgreSQL 的 JSON 支持为现代应用提供了强大而灵活的数据处理能力。正确使用这些功能可以大大简化复杂数据结构的存储和查询，提高开发效率和应用性能。

在生产环境中使用 JSON 功能时，始终要考虑性能影响，合理设计索引策略，并选择适当的数据类型（json vs jsonb）以满足具体的业务需求。

PostgreSQL JSON 函数和操作符 ​

概述 ​

PostgreSQL JSON 支持的核心功能 ​

JSON 数据类型对比 ​

JSON 操作符 ​

基本访问操作符 ​

jsonb 专用操作符 ​

实际业务场景示例 ​

示例 1：电商产品管理系统 ​

示例 2：用户行为分析系统 ​

示例 3：使用 JSONB 包含操作符 ​

JSON 创建和操作函数 ​

JSON 构造函数 ​

实际应用示例：动态报表生成 ​

SQL/JSON 路径语言 ​

路径表达式基础 ​

路径语言语法要素 ​

实际应用示例：GPS 轨迹数据分析 ​

SQL/JSON 查询函数 ​

三大核心查询函数 ​

JSON_EXISTS() 函数详解 ​

JSON_VALUE() 函数详解 ​

JSON_QUERY() 函数详解 ​

综合应用示例：订单分析系统 ​

JSON_TABLE 函数 ​

概述 ​

语法结构 ​

实际应用示例：电影数据分析 ​

性能优化和最佳实践 ​

索引策略 ​

数据类型选择建议 ​

常见错误和解决方案 ​

实际项目应用场景 ​

场景 1：内容管理系统 ​

场景 2：物联网数据处理 ​

场景 3：用户个性化配置 ​

PostgreSQL JSON 函数和操作符

概述

PostgreSQL JSON 支持的核心功能

JSON 数据类型对比

JSON 操作符

基本访问操作符

jsonb 专用操作符

实际业务场景示例

示例 1：电商产品管理系统

示例 2：用户行为分析系统

示例 3：使用 JSONB 包含操作符

JSON 创建和操作函数

JSON 构造函数

实际应用示例：动态报表生成

SQL/JSON 路径语言

路径表达式基础

路径语言语法要素

实际应用示例：GPS 轨迹数据分析

SQL/JSON 查询函数

三大核心查询函数

JSON_EXISTS() 函数详解

JSON_VALUE() 函数详解

JSON_QUERY() 函数详解

综合应用示例：订单分析系统

JSON_TABLE 函数

概述

语法结构

实际应用示例：电影数据分析

性能优化和最佳实践

索引策略

数据类型选择建议

常见错误和解决方案

实际项目应用场景

场景 1：内容管理系统

场景 2：物联网数据处理

场景 3：用户个性化配置