Postgresql JSON对象和数组查询-Toy模板网

这篇具有很好参考价值的文章主要介绍了Postgresql JSON对象和数组查询。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一. Postgresql 9.5以下版本

1.1 简单查询(缺陷：数组必须指定下标，不推荐)

1.1.1 模糊查询

SELECT  * FROM "public"."tf_low_data_testUser" WHERE  "address" #>> '{0,name}' like '%bb%'

address字段是JSONArray类型，所以在路径中，使用数字索引来访问数组元素，从 0 开始计数。

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.1.2 等值匹配

SELECT  * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' = 'bbb'

Postgresql JSON对象和数组查询,Postgresql,JSON查询
如果字段是int类型，后面需要添加::int

1.1.3 时间搜索

SELECT  * FROM "public"."tf_low_data_testUser" WHERE  "address" #>> '{0,date}' BETWEEN '2023-08-13' AND '2023-08-17'

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.1.4 在列表

SELECT  * FROM "public"."tf_low_data_testUser" WHERE  "address" #>> '{0,name}' IN ('bbb','ccc')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.1.5 包含

SELECT  * FROM "public"."tf_low_data_testUser" WHERE "address" #> '{0,roles,0,roleUsers}' @> '["eee"]'

#>：获取在指定路径的 JSON 对象，路径不存在则返回空。返回类型是json(b)

#>>：获取在指定路径的 JSON 对象，路径不存在则返回空。返回类型是text

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2 多层级JSONArray（推荐）

如果表中有一个字段posts，数据结构为

[{
	"name": "aaa",
	"ports": [{
		"port": 443,
		"nickname": "ggg",
		"date": "2023-08-29",
		"address": ["111", "222"]
	}, {
		"port": 80,
		"nickname": "fff",
		"date": "2022-08-29",
		"address": ["333", "444"]
	}]
}, {
	"name": "bbb",
	"ports": [{
		"port": 2443,
		"nickname": "hhh",
		"date": "2021-08-29",
		"address": ["999"]
	}, {
		"port": 280,
		"nickname": "jjj",
		"date": "2020-08-29",
		"address": ["111111"]
	}]
}]

1.2.1 模糊查询

查询nickname like '%jj%'

可以看出有两层JSONArray结构

SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'nickname') like '%gg%'
);

当该层级类型是数组就添加CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.2 模糊查询 NOT

SELECT * FROM "public"."tf_low_data_testUser" WHERE NOT EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'nickname') like '%gg%'
);

查的是另外三条数据源
Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.3 等值匹配

SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'port')::int = 80
);

如果是数字类型后面需要转换 ::int，因为 ->> 操作符的返回类型是 text

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.4 等值匹配 NOT

SELECT * FROM "public"."tf_low_data_testUser" WHERE NOT EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'port')::int = 80
);

查的是另外三条数据源
Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.5 时间搜索

SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'date') BETWEEN '2022-08-13' AND '2023-08-17'
);

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.6 时间搜索 NOT

查的是另外三条数据源
Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.7 在列表

SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->>'nickname') IN ('ggg','fff')
);

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.8 在列表 NOT

查的是另外三条数据源
Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.9 包含

SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("ports") as arr1(obj1) 
	CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)
  WHERE (obj2->'address') @> '["444"]'
);

此时使用的操作符是->，返回值是jsonb类型

Postgresql JSON对象和数组查询,Postgresql,JSON查询

1.2.10 包含 NOT

查的是另外三条数据源
Postgresql JSON对象和数组查询,Postgresql,JSON查询

二. Postgresql 9.5和以上版本

也兼容上面的JSON查询

2.1 模糊查询

使用函数jsonb_path_exists（可以指定JSON路径，如果是数组添加[*]）的正则查询达到模糊查询的效果

-- like '%ggg%'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "g")')
-- 左模糊 like '%g'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "g$")')
-- 右模糊 like 'g%'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "^g")')
-- 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "^ggg$")')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

同样支持NOT文章来源地址https://www.toymoban.com/news/detail-691080.html

2.2 等值匹配

SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ == "fff")')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

同样支持NOT

2.3 时间搜索

SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].date ?(@ >= "2022-01-02" && @ <= "2023-08-02")')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

同样支持NOT

2.4 在列表

SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ == "ggg" || @ == "fff")')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

同样支持NOT

2.5 包含

和等值匹配一样

SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].address ?(@ == "222")')

Postgresql JSON对象和数组查询,Postgresql,JSON查询

同样支持NOT

到了这里，关于Postgresql JSON对象和数组查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

Postgresql JSON对象和数组查询

一. Postgresql 9.5以下版本

1.1 简单查询(缺陷：数组必须指定下标，不推荐)

1.1.1 模糊查询

1.1.2 等值匹配

1.1.3 时间搜索

1.1.4 在列表

1.1.5 包含

1.2 多层级JSONArray（推荐）

1.2.1 模糊查询

1.2.2 模糊查询 NOT

1.2.3 等值匹配

1.2.4 等值匹配 NOT

1.2.5 时间搜索

1.2.6 时间搜索 NOT

1.2.7 在列表

1.2.8 在列表 NOT

1.2.9 包含

1.2.10 包含 NOT

二. Postgresql 9.5和以上版本

2.1 模糊查询

2.2 等值匹配

2.3 时间搜索

2.4 在列表

2.5 包含

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2