postgresql 操作查询jsonb数据

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 类型。