JSON数据

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB  -- 使用JSONB
);

-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES 
    ('iPhone 15', '{"color": "black", "storage": "256GB", "price": 5999}'),
    ('MacBook Pro', '{"color": "silver", "cpu": "M3", "ram": "16GB"}');

-- -> 返回JSON类型
SELECT attributes -> 'color' FROM products;
-- 结果："black"

-- ->> 返回文本类型
SELECT attributes ->> 'color' FROM products;
-- 结果：black

-- 嵌套访问
SELECT attributes -> 'specs' -> 'cpu' FROM products;

-- 查找黑色产品
SELECT * FROM products 
WHERE attributes ->> 'color' = 'black';

-- 查找价格大于5000的
SELECT * FROM products 
WHERE (attributes ->> 'price')::numeric > 5000;

-- 检查键是否存在
SELECT * FROM products 
WHERE attributes ? 'storage';

-- 包含某个值
SELECT * FROM products 
WHERE attributes @> '{"color": "black"}';

-- 添加字段
UPDATE products 
SET attributes = attributes || '{"warranty": "1年"}'
WHERE id = 1;

-- 修改字段
UPDATE products 
SET attributes = jsonb_set(attributes, '{price}', '6999')
WHERE id = 1;

-- 删除字段
UPDATE products 
SET attributes = attributes - 'price'
WHERE id = 1;

-- jsonb_array_elements：展开数组
SELECT * FROM jsonb_array_elements('[1,2,3]');

-- jsonb_each：展开对象
SELECT * FROM jsonb_each('{"a":1,"b":2}');

-- jsonb_object_keys：获取所有键
SELECT jsonb_object_keys(attributes) FROM products;

-- jsonb_pretty：格式化输出
SELECT jsonb_pretty(attributes) FROM products;

-- GIN索引（推荐）
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- 现在查询很快
SELECT * FROM products WHERE attributes @> '{"color": "black"}';

-- 索引特定路径
CREATE INDEX idx_color ON products ((attributes ->> 'color'));

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

INSERT INTO user_settings VALUES 
    (1, '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "sms": false}}');

-- 查询主题是dark的用户
SELECT user_id FROM user_settings 
WHERE settings ->> 'theme' = 'dark';

-- 更新设置
UPDATE user_settings 
SET settings = jsonb_set(settings, '{theme}', '"light"')
WHERE user_id = 1;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    specs JSONB
);

-- 手机
INSERT INTO products VALUES 
    (1, 'iPhone 15', '手机', '{"brand": "Apple", "color": ["黑色", "白色"], "storage": ["128GB", "256GB", "512GB"]}');

-- 查询有黑色的手机
SELECT * FROM products 
WHERE specs -> 'color' ? '黑色';

-- 统计每种颜色的数量
SELECT 
    jsonb_array_elements_text(specs -> 'color') as color,
    COUNT(*) 
FROM products 
GROUP BY color;