Postgres操作jsonb数据

发布时间:2024年01月16日

Postgres操作jsonb数据

PostgreSQL 对 jsonb 类型的筛选查询可以使用 -> 或者 ->> 操作符。
-> 操作符用于通过 JSON 对象中的键来获取对应的值。
->> 操作符可以将获取到的值转化为字符串类型。

1 查询

-- 数据准备
CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  data JSONB
);
 
INSERT INTO test (data) VALUES ('{"name": "John", "age": 30, "cities": ["New York", "Los Angeles"]}');
INSERT INTO test (data) 
VALUES ('{"name": "Jack", "age": 30, "address": {"city": "New York", "state": "NY"}}');
INSERT INTO test (data) 
VALUES ('{"name": "Jane", "age": 25, "address": {"city": "Los Angeles", "state": "CA"}, "phone": "123-456-7890"}');

1.1 WHERE data -> ‘name’ = ‘“John”’:查询name为John

-- 注意:键名需要用单引号括起来,并且键值需要用双引号括起来。
SELECT * FROM test WHERE data -> 'name' = '"John"';

1.2 WHERE data ->> ‘age’ = ‘30’:将整数转换为字符

-- 使用 ->> 将键对应的值转成字符串进行查询
SELECT * FROM test WHERE data ->> 'age' = '30';

1.3 WHERE data -> ‘cities’ @> ‘[“New York”,“Los Angeles”]’:查询数组

-- 上面的语句查询出 "cities" 数组中包含 "New York","Los Angeles" 元素的数据项。
-- @> 操作符表示包含。它用于检查 JSONB 数组是否包含给定元素。
 
-- 如果要查询包含多个元素的数据项,可以在操作符两侧使用 AND 条件:
SELECT * FROM test WHERE data -> 'cities' @> '["New York","Los Angeles"]';

-- 使用 ->> 将数组转化为字符串后进行查询
SELECT * FROM test WHERE data ->> 'cities' LIKE '%York%';
-- 上面的语句查询出 "cities" 数组中包含 "York" 子串的数据项。
-- 注意:这里使用了 LIKE 操作符,需要将数组先转换成字符串。
 
-- 使用 ANY 操作符进行查询
SELECT * FROM test WHERE 'New York' = ANY(data -> 'cities');
-- 上面的语句查询出 "cities" 数组中包含 "New York" 元素的数据项。
-- 注意:ANY 操作符需要在数组左侧使用,并且等号(=)右边的值必须是一个数组元素的值。

1.4 WHERE data -> ‘address’ ->> ‘city’ = ‘New York’:多次解析查询

 
-- 根据 jsonb 字段中的嵌套对象的键值进行查询
SELECT * FROM test WHERE data -> 'address' ->> 'city' = 'New York';
-- 上面的语句查询出 "address" 对象中 "city" 键对应的值为 "New York" 的数据项。

1.5 WHERE data ? ‘phone’:查询json是否包含指定key

-- 使用 ? 操作符判断是否包含指定的键名
SELECT * FROM test WHERE data ? 'phone';
-- 上面的语句查询出包含 "phone" 键名的数据项。
 
-- 使用 ?? 操作符模糊匹配键名
SELECT * FROM test WHERE data ?? 'address%';
-- 上面的语句查询出包含以 "address" 开头的键名的数据项。
-- 注意:?? 操作符支持通配符,在上例中 % 表示匹配任意字符。

1.6 WHERE data @> ‘{“age”: 30}’:查询是否包含指定k-v

-- 使用 @> 操作符判断是否包含指定的 JSON 对象
SELECT * FROM test WHERE data @> '{"age": 30}';
-- 上面的语句查询出包含 "age" 键为 30 的 JSON 对象的数据项。
-- 注意:@> 操作符表示包含。它用于检查 JSONB 是否包含给定对象。
 
-- 使用 -> 和 #> 操作符结合起来查询更深层次的嵌套对象
SELECT * FROM test WHERE data -> 'address' #> '{state}' = 'NY';
-- 上面的语句查询出 "address" 对象中 "state" 键对应的值为 "NY" 的数据项。
-- #> 操作符用于获取一个路径表达式的键对应的值,返回的值可以是原始类型或者 JSONB 类型。

1.7 select data -> ‘cities’ -> 0 ->> ‘city1’ as city_name from test where id = 4;

json某个字段对应数组,查询出指定字段并另取别名

# 数据准备
INSERT INTO test (data) VALUES ('{"name": "Alisa", "age": 30, "cities": [{"city1":"New York", "city2":"Los Angeles"}]}');
# 从json字段中查询值并另取别名
select id, data -> 'cities' -> 0 ->> 'city1' as city_name from test where id = 4;

在这里插入图片描述

2 更新

2.1 set data = jsonb_set(data,‘{“age”}’,‘32’) where xx;更新age为32

在这里插入图片描述

# jsonb_set(需要更新的字段,更新的K,更新后V)
update test set data = jsonb_set(data,'{"age"}','32')
where id = 3;

3 删除

3.1 set data = data - ‘age’ where xx:删除json的age字段

在这里插入图片描述

# 删除json结构中的age字段
update test set data = data - 'age' where id = 3;

4 新增

4.1 set data = jsonb_set(data, ‘{“hobbies”}’,‘“soccer”’) where xx;添加hobbies字段

在这里插入图片描述

update test set data = jsonb_set(data, '{"hobbies"}','"soccer"') where id = 2;

参考文章:https://blog.csdn.net/Yuuuuuubs/article/details/131307500

文章来源:https://blog.csdn.net/weixin_45565886/article/details/135611311
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。