文章目录-获取json字段并作为文本-和-的区别?::(双冒号)类型 转换成指定类型是否包含?(问号)判断key是否存在创建t_product表及插入数据常规的就不说了postgres有些语法和其他数据库不一样特此记录下。-获取json字段并作为文本主要用的是-语法SELECT*FROMt_productWHEREattributes-brandApple;attributes-brandApple;相当于从attributes取出brand字段的文本值再apple比较。-和-的区别?这么理解吧-# 获取json字段并作为文本-# 获取json字段并作为json写法*****左边取出的值右边的值结果原因-Nike(文本)Nike(文本)✅ 匹配成功文本对文本天经地义。-Nike(JSONB)Nike(文本)❌ 不匹配类型不同且 JSONBNike实际上包含了引号字符不等于纯文本 Nike。简单说就是-取出的文本所以可以匹配上-取出的是json所以匹配不上。一般来说我们用-即可。::(双冒号)类型 转换成指定类型因为-取出来的默认会认为是文本::numeric表示转换为数值类型。双冒号是类型转换符号后面要带上类型。语法双冒号类型如::numeric# 转换为数值::jsonb# 转换为jsonbSELECT*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,attributesjsonbNULLDEFAULT{}::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);