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 包含

一. 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对象和数组查询,在这里插入图片描述,第1张

1.1.2 等值匹配

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第2张

如果字段是int类型，后面需要添加::int

Postgresql JSON对象和数组查询,在这里插入图片描述,第3张

1.1.3 时间搜索

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第4张

1.1.4 在列表

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第5张

1.1.5 包含

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

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第6张

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对象和数组查询,在这里插入图片描述,第7张

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对象和数组查询,在这里插入图片描述,第8张

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对象和数组查询,在这里插入图片描述,第9张

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对象和数组查询,在这里插入图片描述,第10张

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对象和数组查询,在这里插入图片描述,第11张

1.2.6 时间搜索 NOT

查的是另外三条数据源

Postgresql JSON对象和数组查询,在这里插入图片描述,第12张

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对象和数组查询,在这里插入图片描述,第13张

1.2.8 在列表 NOT

查的是另外三条数据源

Postgresql JSON对象和数组查询,在这里插入图片描述,第14张

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对象和数组查询,在这里插入图片描述,第15张

1.2.10 包含 NOT

查的是另外三条数据源

Postgresql JSON对象和数组查询,在这里插入图片描述,第16张

二. 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对象和数组查询,在这里插入图片描述,第17张

同样支持NOT

2.2 等值匹配

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第18张

同样支持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对象和数组查询,在这里插入图片描述,第19张

同样支持NOT

2.4 在列表

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第20张

同样支持NOT

2.5 包含

和等值匹配一样

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

Postgresql JSON对象和数组查询,在这里插入图片描述,第21张

同样支持NOT

上一篇：MybatisPlus显示Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required异常问题

下一篇：Python 操作 MySQL 数据库：使用 mysql-connector-python 库