索取营销诊断书
QQ
您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >
相关推荐recommended
Postgresql JSON对象和数组查询
作者：mmseoamin日期：2023-12-14

文章目录

  • 一. 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 开始计数。

                    在这里插入图片描述

                    1.1.2 等值匹配
                    SELECT  * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' = 'bbb'

                    在这里插入图片描述

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

                    在这里插入图片描述

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

                    在这里插入图片描述

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

                    在这里插入图片描述

                    1.1.5 包含
                    SELECT  * FROM "public"."tf_low_data_testUser" WHERE "address" #> '{0,roles,0,roleUsers}' @> '["eee"]'
                    • #>：获取在指定路径的 JSON 对象，路径不存在则返回空。返回类型是json(b)
                    • #>>：获取在指定路径的 JSON 对象，路径不存在则返回空。返回类型是text

                    在这里插入图片描述

                    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)

                    在这里插入图片描述

                    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%'
);

                    查的是另外三条数据源

                    在这里插入图片描述

                    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

                    在这里插入图片描述

                    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
);

                    查的是另外三条数据源

                    在这里插入图片描述

                    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'
);

                    在这里插入图片描述

                    1.2.6 时间搜索 NOT

                    查的是另外三条数据源

                    在这里插入图片描述

                    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')
);

                    在这里插入图片描述

                    1.2.8 在列表 NOT

                    查的是另外三条数据源

                    在这里插入图片描述

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

                    在这里插入图片描述

                    1.2.10 包含 NOT

                    查的是另外三条数据源

                    在这里插入图片描述

                    二. 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$")')

                    在这里插入图片描述

                    同样支持NOT

                    2.2 等值匹配

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

                    在这里插入图片描述

                    同样支持NOT

                    2.3 时间搜索

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

                    在这里插入图片描述

                    同样支持NOT

                    2.4 在列表

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

                    在这里插入图片描述

                    同样支持NOT

                    2.5 包含

                    和等值匹配一样

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

                    在这里插入图片描述

                    同样支持NOT

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

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

关注我们
扫描二维码
获取更多信息
精彩一手掌握