PostgreSQL 学习笔记(六)对 JSONB 类型字段的增删改查操作

苦 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 继续学习。

结束。