©2020-2024 ioDraw All rights reserved
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 类型。