文章目录
- `->>`获取json字段并作为文本
- ->>和->的区别?
- `::`(双冒号)+类型 转换成指定类型
- `@>`是否包含
- `?`(问号)判断key是否存在
- 创建t_product表及插入数据
常规的就不说了，postgres有些语法和其他数据库不一样，特此记录下。
->>获取json字段并作为文本
主要用的是->>语法：
SELECT*FROMt_productWHEREattributes->>'brand'='Apple';attributes->>'brand'='Apple';相当于从attributes取出brand字段的文本值，再apple比较。->>和->的区别?
这么理解吧：->># 获取json字段，并作为文本-># 获取json字段，并作为json
| 写法***** | 左边取出的值 | 右边的值 | 结果 | 原因 |
|---|---|---|---|---|
->> | Nike(文本) | 'Nike'(文本) | ✅ 匹配成功 | 文本对文本，天经地义。 |
-> | "Nike"(JSONB) | 'Nike'(文本) | ❌ 不匹配 | 类型不同，且 JSONB"Nike"实际上包含了引号字符，不等于纯文本 Nike。 |
简单说就是->>取出的文本，所以可以匹配上，->取出的是json，所以匹配不上。一般来说我们用->>即可。
::(双冒号)+类型 转换成指定类型
因为->>取出来的默认会认为是文本，::numeric表示转换为数值类型。
双冒号是类型转换符号，后面要带上类型。
语法：双冒号+类型
如：::numeric# 转换为数值::jsonb# 转换为jsonb
SELECT*FROMt_productWHERE(attributes->>'price')::numericBETWEEN1000AND5000;@>是否包含
SELECT*FROMt_productWHEREattributes->'tags'@>'["laptop"]'::jsonb;从attributes中取出tag作为jsonb，是否包含laptop，并转换为jsonb注：这里获取用的是->，数组中的内容也加了双引号。
?(问号)判断key是否存在
SELECT*FROMt_productWHEREattributes ?'cpu';创建t_product表及插入数据
CREATETABLEt_product(id int8NOTNULL,product_codevarchar(50)NOTNULL,product_namevarchar(200)NOTNULL,"attributes"jsonbNULLDEFAULT'{}'::jsonb,specification jsonbNULLDEFAULT'{}'::jsonb,create_datetimestampNULLDEFAULTCURRENT_TIMESTAMP,update_datetimestampNULLDEFAULTCURRENT_TIMESTAMP,CONSTRAINTt_product_pkeyPRIMARYKEY(id),CONSTRAINTt_product_product_code_keyUNIQUE(product_code));CREATEINDEXidx_product_attributesONt_productUSINGgin(attributes);CREATEINDEXidx_product_codeONt_productUSINGbtree(product_code);CREATEINDEXidx_product_nameONt_productUSINGbtree(product_name);CREATEINDEXidx_product_specificationONt_productUSINGgin(specification);-- Table Triggerscreatetriggerupdate_t_product_modtime beforeupdateont_productfor each rowexecutefunctionupdate_modified_column();INSERTINTOt_product(id,product_code,product_name,attributes,specification)VALUES(1001,'PROD-001','MacBook Pro 14寸','{"brand": "Apple", "category": "electronics", "tags": ["laptop", "premium", "work"], "price": 14999, "cpu": "M3 Pro", "memory": "18GB", "storage": "512GB SSD"}'::jsonb,'{"screen_size": "14.2英寸", "resolution": "3024x1964", "weight": "1.61kg", "battery": "70Wh", "ports": ["HDMI", "USB-C x3", "SD卡槽"]}'::jsonb),(1002,'PROD-002','iPhone 15 Pro','{"brand": "Apple", "category": "electronics", "tags": ["smartphone", "5G", "premium"], "price": 8999, "cpu": "A17 Pro", "memory": "8GB", "storage": "256GB"}'::jsonb,'{"screen_size": "6.1英寸", "resolution": "2556x1179", "weight": "187g", "battery": "3274mAh", "camera": "4800万主摄+1200万超广角+1200万长焦"}'::jsonb),(1003,'PROD-003','Nike Air Max 270','{"brand": "Nike", "category": "shoes", "tags": ["sports", "casual", "running"], "price": 1299, "sizes": ["38", "39", "40", "41", "42", "43"], "colors": ["black", "white", "red"]}'::jsonb,'{"material": "网眼布+合成革", "sole": "橡胶", "technology": "Air Max气垫", "weight": "310g", "origin": "越南"}'::jsonb),(1004,'PROD-004','ThinkPad X1 Carbon','{"brand": "Lenovo", "category": "electronics", "tags": ["laptop", "business", "lightweight"], "price": 12999, "cpu": "Intel i7-1365U", "memory": "16GB", "storage": "1TB SSD"}'::jsonb,'{"screen_size": "14英寸", "resolution": "2880x1800", "weight": "1.12kg", "battery": "57Wh", "ports": ["USB-A x2", "USB-C x2", "HDMI"]}'::jsonb),(1005,'PROD-005','星巴克咖啡豆','{"brand": "Starbucks", "category": "food", "tags": ["coffee", "beans", "imported"], "price": 88, "origin": "哥伦比亚", "roast_level": "中度烘焙", "weight": "200g"}'::jsonb,'{"flavor": "坚果+巧克力", "acidity": "中等", "body": "醇厚", "processing": "水洗法", "altitude": "1200-1800米"}'::jsonb),(1006,'PROD-006','小米手环8','{"brand": "Xiaomi", "category": "electronics", "tags": ["wearable", "fitness", "budget"], "price": 249, "display": "AMOLED", "battery_life": "16天", "water_resistance": "5ATM"}'::jsonb,'{"screen_size": "1.62英寸", "resolution": "490x192", "weight": "27g", "sensors": ["心率", "血氧", "加速度计", "陀螺仪"], "connectivity": "蓝牙5.1"}'::jsonb),(1007,'PROD-007','优衣库羽绒服','{"brand": "Uniqlo", "category": "clothing", "tags": ["winter", "down", "lightweight"], "price": 599, "sizes": ["S", "M", "L", "XL", "XXL"], "colors": ["black", "navy", "gray", "red"]}'::jsonb,'{"fill_power": "640+", "down_content": "90%鸭绒", "weight": "约300g", "fabric": "尼龙100%", "warmth_rating": "★★★☆☆"}'::jsonb),(1008,'PROD-008','戴森V15吸尘器','{"brand": "Dyson", "category": "home_appliance", "tags": ["vacuum", "cordless", "premium"], "price": 4990, "suction_power": "230AW", "battery_life": "60分钟", "dustbin_capacity": "0.76L"}'::jsonb,'{"weight": "3.1kg", "filtration": "五重过滤", "modes": ["ECO", "Auto", "Boost"], "accessories": ["激光探测头", "防缠绕刷头", "缝隙清洁头"], "warranty": "2年"}'::jsonb);
