1.操作键值对

PostgreSQL 对 jsonb 类型的筛选查询可以使用 -> 或者 ->> 操作符。

* -> 操作符用于通过 JSON 对象中的键来获取对应的值。
* ->> 操作符可以将获取到的值转化为字符串类型。
例如,下面是一个包含 jsonb 类型字段的表: 
CREATE TABLE test ( id SERIAL PRIMARY KEY, data JSONB ); INSERT INTO test
(data) VALUES ('{"name": "John", "age": 30, "city": "New York"}'); INSERT INTO
test (data) VALUES ('{"name": "Jane", "age": 25, "city": "Los Angeles"}');
 查询方法:
-- 获取所有数据 SELECT * FROM test; -- 根据 jsonb 字段中的键值进行查询 SELECT * FROM test WHERE
data -> 'name' = '"John"'; -- 注意:键名需要用单引号括起来,并且键值需要用双引号括起来。 -- 使用 ->>
将键对应的值转成字符串进行查询 SELECT * FROM test WHERE data ->> 'age' = '30';
2. 操作json数组

2.1 筛选数据

例如,假设我们有以下包含 jsonb 类型字段的表:
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": "Jane", "age": 25,
"cities": ["London", "Paris"]}');
查询方法:
-- 获取所有数据 SELECT * FROM test; -- 根据 jsonb 字段中的数组元素进行查询 SELECT * FROM test
WHERE data -> 'cities' @> '["New York"]'; -- 上面的语句查询出 "cities" 数组中包含 "New York"
元素的数据项。 -- @> 操作符表示包含。它用于检查 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
操作符需要在数组左侧使用,并且等号(=)右边的值必须是一个数组元素的值。
2.2 如何判断数组是否为空

要筛选出键名对应的数组不是空的 JSONB 数据项,可以使用 ? 操作符或者jsonb_array_length() 函数。

例如,假设我们有以下包含 jsonb 类型字段的表:
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": "Jane", "age": 25,
"cities": []}');
 查询方法:
-- 获取所有数据 SELECT * FROM test; -- 筛选出键名对应的数组不是空的数据项 SELECT * FROM test WHERE
data -> 'cities' ?| array['{}']; -- 上面的语句中 {} 表示空数组。?| 操作符用于检查 JSONB 数组是否非空。 --
使用 jsonb_array_length() 函数进行查询 SELECT * FROM test WHERE jsonb_array_length(data
-> 'cities') > 0; -- 上面的语句中 jsonb_array_length() 函数会返回数组的长度。如果大于 0,则表示数组非空。
3. 操作json对象

例如,如果我们有以下包含 jsonb 类型字段的表:
CREATE TABLE test ( id SERIAL PRIMARY KEY, data JSONB ); INSERT INTO test
(data) VALUES ('{"name": "John", "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"}');
查询方法:
-- 获取所有数据 SELECT * FROM test; -- 根据 jsonb 字段中的嵌套对象的键值进行查询 SELECT * FROM test
WHERE data -> 'address' ->> 'city' = 'New York'; -- 上面的语句查询出 "address" 对象中
"city" 键对应的值为 "New York" 的数据项。 -- 使用 ? 操作符判断是否包含指定的键名 SELECT * FROM test WHERE
data ? 'phone'; -- 上面的语句查询出包含 "phone" 键名的数据项。 -- 使用 ?? 操作符模糊匹配键名 SELECT * FROM
test WHERE data ?? 'address%'; -- 上面的语句查询出包含以 "address" 开头的键名的数据项。 -- 注意:??
操作符支持通配符,在上例中 % 表示匹配任意字符。 -- 使用 @> 操作符判断是否包含指定的 JSON 对象 SELECT * FROM test
WHERE data @> '{"age": 30}'; -- 上面的语句查询出包含 "age" 键为 30 的 JSON 对象的数据项。 -- 注意:@>
操作符表示包含。它用于检查 JSONB 是否包含给定对象。 -- 使用 -> 和 #> 操作符结合起来查询更深层次的嵌套对象 SELECT * FROM
test WHERE data -> 'address' #> '{state}' = 'NY'; -- 上面的语句查询出 "address" 对象中
"state" 键对应的值为 "NY" 的数据项。 -- #> 操作符用于获取一个路径表达式的键对应的值,返回的值可以是原始类型或者 JSONB 类型。

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信