PostgreSQL JSON 操作 #

┌─────────────────────────────────────────────────────────────┐
│                    JSON vs JSONB                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  JSON                                                       │
│  ├── 存储为文本                                            │
│  ├── 每次查询时解析                                        │
│  ├── 保留原始格式（空格、键顺序）                          │
│  ├── 插入速度快                                            │
│  └── 不支持索引                                            │
│                                                             │
│  JSONB                                                      │
│  ├── 存储为二进制                                          │
│  ├── 解析一次，查询快                                      │
│  ├── 不保留格式（键排序、去除空格）                        │
│  ├── 支持索引（GIN）                                       │
│  └── 推荐使用                                              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES
    ('Laptop', '{"brand": "Dell", "price": 999.99, "specs": {"cpu": "Intel i7", "ram": "16GB"}}'),
    ('Phone', '{"brand": "Apple", "price": 799.99, "colors": ["black", "white", "blue"]}'),
    ('Tablet', '{"brand": "Samsung", "price": 599.99, "in_stock": true}');

-- 使用 JSON 函数构建
INSERT INTO products (name, attributes) VALUES
    ('Monitor', jsonb_build_object(
        'brand', 'LG',
        'price', 349.99,
        'resolution', '4K'
    ));

-- 使用 JSON 数组
INSERT INTO products (name, attributes) VALUES
    ('Keyboard', '{"features": ["mechanical", "RGB", "wireless"]}'::jsonb);

-- JSON 转 JSONB
SELECT '{"name": "Alice", "age": 25}'::jsonb;

-- JSONB 转 JSON
SELECT '{"name": "Alice", "age": 25}'::jsonb::json;

-- 文本转 JSONB
SELECT '{"name": "Alice"}'::jsonb;

-- 使用 jsonb_of_json 函数
SELECT to_jsonb(ROW('Alice', 25));

-- -> 提取 JSON 对象/数组元素（返回 JSON）
SELECT attributes -> 'brand' FROM products;
-- "Dell"

-- ->> 提取 JSON 对象/数组元素（返回文本）
SELECT attributes ->> 'brand' FROM products;
-- Dell

-- 提取嵌套值
SELECT attributes -> 'specs' -> 'cpu' FROM products WHERE name = 'Laptop';
-- "Intel i7"

SELECT attributes -> 'specs' ->> 'cpu' FROM products WHERE name = 'Laptop';
-- Intel i7

-- 提取数组元素
SELECT attributes -> 'colors' -> 0 FROM products WHERE name = 'Phone';
-- "black"

-- #> 提取指定路径
SELECT attributes #> '{specs,cpu}' FROM products WHERE name = 'Laptop';
-- "Intel i7"

-- #>> 提取指定路径（返回文本）
SELECT attributes #>> '{specs,cpu}' FROM products WHERE name = 'Laptop';
-- Intel i7

-- @> 包含检查
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- <@ 被包含检查
SELECT * FROM products WHERE '{"brand": "Dell", "price": 999.99}' <@ attributes;

-- ? 键存在检查
SELECT * FROM products WHERE attributes ? 'brand';

-- ?| 任一键存在
SELECT * FROM products WHERE attributes ?| ARRAY['brand', 'color'];

-- ?& 所有键存在
SELECT * FROM products WHERE attributes ?& ARRAY['brand', 'price'];

-- @? JSONPath 匹配
SELECT * FROM products WHERE attributes @? '$.specs.cpu';

-- @@ JSONPath 谓词
SELECT * FROM products WHERE attributes @@ '$.price > 500';

-- jsonb_build_object：构建 JSON 对象
SELECT jsonb_build_object(
    'name', 'Alice',
    'age', 25,
    'active', true
);
-- {"name": "Alice", "age": 25, "active": true}

-- jsonb_build_array：构建 JSON 数组
SELECT jsonb_build_array(1, 'two', true, null);
-- [1, "two", true, null]

-- jsonb_object：从键值对构建对象
SELECT jsonb_object('{a,b,c}', '{1,2,3}');
-- {"a": "1", "b": "2", "c": "3"}

-- to_jsonb：转换为 JSONB
SELECT to_jsonb(ROW('Alice', 25));
SELECT to_jsonb(NOW());
SELECT to_jsonb(ARRAY[1, 2, 3]);

-- jsonb_typeof：获取 JSON 类型
SELECT jsonb_typeof(attributes) FROM products;
-- object

SELECT jsonb_typeof(attributes -> 'colors') FROM products WHERE name = 'Phone';
-- array

-- jsonb_object_keys：获取所有键
SELECT jsonb_object_keys(attributes) FROM products WHERE name = 'Laptop';
-- brand
-- price
-- specs

-- jsonb_array_elements：展开数组
SELECT jsonb_array_elements(attributes -> 'colors') FROM products WHERE name = 'Phone';
-- "black"
-- "white"
-- "blue"

-- jsonb_each：展开为键值对
SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);
-- key | value
-- a   | 1
-- b   | 2

-- jsonb_each_text：展开为文本键值对
SELECT * FROM jsonb_each_text('{"a": 1, "b": 2}'::jsonb);
-- key | value
-- a   | 1
-- b   | 2

-- || 合并/更新 JSON
SELECT '{"a": 1}'::jsonb || '{"b": 2}'::jsonb;
-- {"a": 1, "b": 2}

UPDATE products 
SET attributes = attributes || '{"discount": 0.1}'::jsonb
WHERE name = 'Laptop';

-- - 删除键
SELECT '{"a": 1, "b": 2}'::jsonb - 'a';
-- {"b": 2}

UPDATE products 
SET attributes = attributes - 'discount'
WHERE name = 'Laptop';

-- 删除数组元素
SELECT '["a", "b", "c"]'::jsonb - 1;
-- ["a", "c"]

-- jsonb_set：设置指定路径的值
SELECT jsonb_set(
    '{"a": {"b": 1}}'::jsonb,
    '{a,b}',
    '2'::jsonb
);
-- {"a": {"b": 2}}

-- jsonb_insert：插入值
SELECT jsonb_insert(
    '{"a": [1, 2, 3]}'::jsonb,
    '{a,1}',
    '1.5'::jsonb
);
-- {"a": [1, 1.5, 2, 3]}

-- jsonb_strip_nulls：删除 NULL 值
SELECT jsonb_strip_nulls('{"a": 1, "b": null}'::jsonb);
-- {"a": 1}

-- jsonb_agg：聚合为 JSON 数组
SELECT jsonb_agg(name) FROM products;
-- ["Laptop", "Phone", "Tablet", "Monitor", "Keyboard"]

-- jsonb_object_agg：聚合为 JSON 对象
SELECT jsonb_object_agg(name, attributes ->> 'brand') FROM products;
-- {"Laptop": "Dell", "Phone": "Apple", ...}

-- 分组聚合
SELECT 
    brand,
    jsonb_agg(jsonb_build_object('name', name, 'price', attributes ->> 'price'))
FROM products,
     jsonb_each_text(attributes) AS t(key, value)
WHERE key = 'brand'
GROUP BY brand;

-- 创建 GIN 索引
CREATE INDEX idx_products_attributes ON products USING gin(attributes);

-- 使用索引的查询
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- 创建 jsonb_path_ops 索引（更小，更快）
CREATE INDEX idx_products_attributes_path ON products USING gin(attributes jsonb_path_ops);

-- 只支持 @> 操作符
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- 对特定键创建索引
CREATE INDEX idx_products_brand ON products ((attributes ->> 'brand'));

-- 对数值创建索引
CREATE INDEX idx_products_price ON products (((attributes ->> 'price')::numeric));

-- 使用索引
SELECT * FROM products WHERE attributes ->> 'brand' = 'Dell';
SELECT * FROM products WHERE (attributes ->> 'price')::numeric > 500;

-- $ 根元素
-- . 键访问
-- [] 数组访问
-- * 通配符

-- 基本查询
SELECT * FROM products WHERE attributes @? '$.brand';

-- 条件查询
SELECT * FROM products WHERE attributes @@ '$.price > 500';

-- 嵌套查询
SELECT * FROM products WHERE attributes @? '$.specs.cpu';

-- 数组查询
SELECT * FROM products WHERE attributes @? '$.colors[*] == "black"';

-- 复杂条件
SELECT * FROM products WHERE attributes @@ '$.price > 500 && $.in_stock == true';

-- jsonb_path_query：返回所有匹配
SELECT jsonb_path_query(attributes, '$.specs.*') FROM products;

-- jsonb_path_query_array：返回数组
SELECT jsonb_path_query_array(attributes, '$.colors[*]') FROM products;

-- jsonb_path_query_first：返回第一个匹配
SELECT jsonb_path_query_first(attributes, '$.specs.*') FROM products;

-- jsonb_path_exists：检查是否存在匹配
SELECT jsonb_path_exists(attributes, '$.specs.cpu') FROM products;

CREATE TABLE user_settings (
    user_id INTEGER PRIMARY KEY,
    settings JSONB DEFAULT '{}'
);

-- 设置默认值
INSERT INTO user_settings (user_id, settings) VALUES
    (1, '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "push": false}}');

-- 更新特定设置
UPDATE user_settings 
SET settings = jsonb_set(settings, '{notifications,email}', 'false'::jsonb)
WHERE user_id = 1;

-- 获取特定设置
SELECT settings ->> 'theme' FROM user_settings WHERE user_id = 1;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    properties JSONB
);

-- 不同类型的商品有不同的属性
INSERT INTO items (name, properties) VALUES
    ('Shirt', '{"type": "clothing", "size": "M", "color": "blue"}'),
    ('Book', '{"type": "media", "author": "John Doe", "pages": 300}'),
    ('Laptop', '{"type": "electronics", "cpu": "M1", "ram": "16GB"}');

-- 查询特定类型
SELECT * FROM items WHERE properties @> '{"type": "clothing"}';

-- 查询特定属性
SELECT * FROM items WHERE properties ->> 'size' = 'M';

扩展阶段
├── JSON操作（本文）
├── 全文搜索
└── 常用扩展