Postgres操作jsonb数据

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