JSONB操作：PG里玩转半结构化数据，查询更新真方便

-- 在DataGrip的查询控制台或psql命令行里执行
SELECT version();

-- 在DataGrip的查询控制台执行
-- 创建一个商品表
CREATETABLE products (
    id SERIALPRIMARYKEY,-- 自增ID，主键
    name VARCHAR(255)NOTNULL,-- 商品名，不能为空
    attrs JSONB,-- 我们的主角：JSONB类型的“万能口袋”
    created_at TIMESTAMPTZ DEFAULTNOW()-- 创建时间，带时区，默认为当前时间
);

-- 给小白的注释：
-- SERIAL: 这是PG特有的，会自动创建一个自增的整数序列，非常适合做主键。
-- PRIMARY KEY: 声明id是主键，保证唯一且非空。
-- VARCHAR(255): 存最多255个字符的字符串。
-- TIMESTAMPTZ: 带时区的时间戳，可以避免时区混乱问题。

-- 在DataGrip的查询控制台执行
-- 插入一条手机数据
INSERTINTO products (name, attrs)VALUES
('SuperPhone Pro Max','{
    "brand": "IT咸鱼",
    "price": 8999.00,
    "in_stock": true,
    "tags": ["旗舰", "新品", "5G"],
    "specs": {
        "screen": "6.7 inch OLED",
        "cpu": "S1 Chip",
        "storage": "256GB"
    }
}');

-- 插入一条T恤数据
INSERTINTO products (name, attrs)VALUES
('简约印花T恤','{
    "brand": "咸鱼优品",
    "price": 99.00,
    "in_stock": true,
    "tags": ["纯棉", "夏季"],
    "specs": {
        "color": "白色",
        "size": "L"
    }
}');

-- 插入一条缺货的笔记本电脑数据
INSERTINTO products (name, attrs)VALUES
('GamerBook X1','{
    "brand": "IT咸鱼",
    "price": 12999.00,
    "in_stock": false,
    "tags": ["游戏本", "RTX"],
    "specs": {
        "screen": "16 inch 165Hz",
        "cpu": "Core i9",
        "storage": "1TB SSD"
    }
}');

-- 在DataGrip的查询控制台执行

-- 例子1：获取所有商品的品牌 (brand)
-- 使用 ->>，因为我们通常需要的是文本格式的品牌名
SELECT
    name,
    attrs ->>'brand'AS brand_name -- 使用 ->> 直接获取文本
FROM
    products;

-- 在DataGrip的查询控制台执行

-- 对比一下 -> 和 ->> 的区别
SELECT
    name,
    attrs ->'price'AS price_json,-- 使用 -> 获取的是JSON数字
    attrs ->>'price'AS price_text -- 使用 ->> 获取的是文本
FROM
    products
WHERE
    name ='SuperPhone Pro Max';

-- 在DataGrip的查询控制台执行

-- 获取SuperPhone的CPU型号
SELECT
    name,
    attrs #>> '{specs,cpu}' AS cpu_model -- 一步到位，获取嵌套的CPU型号
FROM
    products
WHERE
    name ='SuperPhone Pro Max';

-- 在DataGrip的查询控制台执行

-- 例子1：查询所有品牌是“IT咸鱼”的商品
-- '{"brand": "IT咸鱼"}' 这个小JSON，是不是被 attrs 这个大JSON包含了？
SELECT name, attrs ->>'price'as price
FROM products
WHERE attrs @>'{"brand": "IT咸鱼"}';

-- 例子2：查询所有库存为 true 的商品
SELECT name, attrs ->>'price'as price
FROM products
WHERE attrs @>'{"in_stock": true}';

-- 例子3：查询标签(tags)里包含“新品”的商品
-- 注意，对于数组，它会判断右边的数组元素是否都在左边的数组里
SELECT name
FROM products
WHERE attrs ->'tags' @>'["新品"]';

-- 在DataGrip的查询控制台执行

-- 例子1：把GamerBook X1的价格从12999改成11999
UPDATE products
SET attrs = jsonb_set(attrs,'{price}','11999.00'::jsonb)
WHERE name ='GamerBook X1';

-- 例子2：给GamerBook X1增加一个内存(memory)属性
-- 因为原来没有memory，所以第四个参数要设为true
UPDATE products
SET attrs = jsonb_set(attrs,'{specs,memory}','"32GB DDR5"'::jsonb,true)
WHERE name ='GamerBook X1';

-- 检查一下修改结果
SELECT name, attrs FROM products WHERE name ='GamerBook X1';

-- 在DataGrip的查询控制台执行
CREATEINDEX idx_products_attrs_gin ON products USING GIN (attrs);