苦 JSONB 久矣。
介绍下实验用表 item 的表结构:
名 类型 长度 小数点 不是 null 键 注释 id int4 32 0 √ 🔑 商品 ID name varchar 255 0 商品名 property jsonb 0 0 属性
表中数据: id name property 1 毛巾 {"price": 5.88, "num": 20, "type": "厨卫", "discount": false} 2 饼干 {"price": 3.00, "num": 55, "type": "食品", "discount": true} 注:当然实际开发中绝对不会把价格等信息写在 JSONB 中,这里的结构和数据仅作测试用。
然后考虑了一下介绍各操作的顺序,对于 JSON 类型,增加、删除、修改、查看的顺序居然意外的合理,那就按这个顺序来吧。
1、增加操作
① 为 JSONB 类型数据新增键值对的操作符为 ||,即双竖杠。
具体操作:
UPDATE item SET property = property::JSONB || '{"discount_percent": "90%"}'::JSONB WHERE id = 2;
这里为饼干新增了具体折扣数值这个属性,双冒号 :: 起到了类型转换的作用。
id name property 2 饼干 {"num": 55, "type": "食品", "price": 3.00, "discount": true, "discount_percent": "90%"}
② 使用 jsonb_insert 这个函数。
官方对这个函数的介绍:
jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])
第一个参数 target 为需要插入新属性的 JSONB 类型的对象;
第二个参数 path 为路径,列表类型,用 [] 或 {} 包裹元素都可以,键一般就写在这个列表的最后一位;
第三个参数 new_value 就是该键值对的值了;
第四个参数是个可选参数 insert_after,用以判断是将值插在指定位置之前还是之后,这个在路径最后一位为索引而非键的情况下常用,默认为 false。
具体操作:
-- 新建键值对
UPDATE item SET property = jsonb_insert(property, '{oriented}', '["老人", "小孩"]'::JSONB, false) WHERE id = 1;
-- 为键值对的值做添加,添加在索引 1 的元素后面,即最终顺序为:["老人", "小孩", "青年"]
UPDATE item SET property = jsonb_insert(property, '{oriented, 1}', '"青年"'::JSONB, true) WHERE id = 1;
-- 由于 jsonb_insert 无法自动设置父节点,因此在创建 country 下的 city 属性时,首先得确保 country 属性存在!
UPDATE item SET property = jsonb_insert(property, '{country}', '{"country": "China"}'::JSONB, false) WHERE id = 1;
UPDATE item SET property = jsonb_insert(property, '{from, city}', '"Changzhou"'::JSONB, false) WHERE id = 1;
id name property 1 毛巾 {"num": 20, "from": {"city": "Changzhou", "country": "China"}, "type": "厨卫", "price": 5.88, "discount": false, "oriented": ["老人", "小孩", "青年"]}
2、删除操作
删除的操作符为 -,即减号。
具体操作:
-- 单个属性
UPDATE item SET property = property::JSONB - 'discount_percent' WHERE id = 2;
-- 多个属性
UPDATE item SET property = property::JSONB - '{"discount", "type"}'::text[] WHERE id = 1;
id name property 1 毛巾 {"num": 20, "from": {"city": "Changzhou", "country": "China"}, "price": 5.88, "country": {"country": "China"}, "oriented": ["老人", "小孩", "青年"]} 2 饼干 {"num": 55, "type": "食品", "price": 3.00, "discount": true}
3、更新操作
使用 jsonb_set 这个函数。
官方对这个函数的介绍:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
第一个参数 target 为需要修改的 JSONB 类型的对象;
第二个参数 path 为路径,列表类型,用 [] 或 {} 包裹元素都可以,键一般就写在这个列表的最后一位;
第三个参数 new_value 就是该键值对的值了;
第四个参数是个可选参数 create_missing,是否在没有该值时创建,由此你也知道了 jsonb_set 在插入操作时也可使用,默认为 true。
具体操作:
-- 插入新值
UPDATE item SET property = jsonb_set(property, '{discount_percent}', '"100%"'::JSONB, true) WHERE id = 1;
-- 修改旧值
UPDATE item SET property = jsonb_set(property, '{price}', '4.88'::JSONB, false) WHERE id = 1;
-- 对更深层的值进行修改
UPDATE item SET property = jsonb_set(property, '{"from", "city"}', '"Suzhou"'::JSONB, false) WHERE id = 1;
-- 用一个新的 JSONB 类型数据来更新表中的字段,重复的键会使用新的数据,不重复的键值对会被添加(这里是添加符号 || 的另一种用法)
UPDATE item SET property = property || '{"num": 108, "sell_num": 1}'::JSONB WHERE id = 1;
id name property 1 毛巾 {"num": 108, "sell_num": 1, "from": {"city": "Suzhou", "country": "China"}, "price": 4.88, "oriented": ["老人", "小孩", "青年"], "discount_percent": "100%"}
4、查找操作
查找作为最基础的操作,可写的也最多,几乎所有能返回 Boolean 的函数都能作为查找条件,这里就只写几个我最常用的例子吧!
① 查找出存在这个属性的数据:
-- 包含指定属性
SELECT * FROM item WHERE property::JSONB ? 'from';
-- 只需包含列表中的任意属性
SELECT * FROM item WHERE property::JSONB ?| array['discount_percent', 'discount'];
-- 需要包含列表中的所有属性
SELECT * FROM item WHERE property::JSONB ?& array['discount_percent', 'discount'];
② 查找某属性值符合条件的数据:
-- 某属性等于某个值
SELECT * FROM item WHERE property::JSONB @> '{"discount": true}'::JSONB;
-- 更深层的属性相等
SELECT * FROM item WHERE property::JSONB @> '{"from": {"country": "China"}}'::JSONB;
-- 大于某个值
SELECT * FROM item WHERE (property::JSONB ->> 'price')::NUMERIC >= 4;
-- 更深层的值大于
SELECT * FROM item WHERE (property::JSONB #>> '{"from", "zip_code"}')::INT >= 213000;
-- 列表包含(属于列表类型操作)
SELECT * FROM item WHERE property::JSONB -> 'oriented' @> '"老人"';
如果上面这些操作无法满足你,那么请前往官方文档:JSON Functions and Operators 继续学习。
结束。