欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Postgresql JSON對(duì)象和數(shù)組查詢(xún)功能實(shí)現(xiàn)

 更新時(shí)間：2023年11月13日 16:04:00   作者：wnfee  
這篇文章主要介紹了Postgresql JSON對(duì)象和數(shù)組查詢(xún)功能實(shí)現(xiàn),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

一. Postgresql 9.5以下版本

1.1 簡(jiǎn)單查詢(xún)(缺陷：數(shù)組必須指定下標(biāo)，不推薦)

1.1.1 模糊查詢(xún)

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

address字段是JSONArray類(lèi)型，所以在路徑中，使用數(shù)字索引來(lái)訪問(wèn)數(shù)組元素，從 0 開(kāi)始計(jì)數(shù)。

1.1.2 等值匹配

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

如果字段是int類(lèi)型，后面需要添加::int

1.1.3 時(shí)間搜索

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 對(duì)象，路徑不存在則返回空。返回類(lèi)型是json(b)#>>：獲取在指定路徑的 JSON 對(duì)象，路徑不存在則返回空。返回類(lèi)型是text

1.2 多層級(jí)JSONArray（推薦）

如果表中有一個(gè)字段posts，數(shù)據(jù)結(jié)構(gòu)為

[{
	"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 模糊查詢(xún)

查詢(xún)nickname like '%jj%'

可以看出有兩層JSONArray結(jié)構(gòu)

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

當(dāng)該層級(jí)類(lèi)型是數(shù)組就添加CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)

1.2.2 模糊查詢(xún) 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%'
);

查的是另外三條數(shù)據(jù)源

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

如果是數(shù)字類(lèi)型后面需要轉(zhuǎn)換 ::int，因?yàn)?->> 操作符的返回類(lèi)型是 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
);

查的是另外三條數(shù)據(jù)源

1.2.5 時(shí)間搜索

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 時(shí)間搜索 NOT

查的是另外三條數(shù)據(jù)源

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

查的是另外三條數(shù)據(jù)源

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

此時(shí)使用的操作符是->，返回值是jsonb類(lèi)型

1.2.10 包含 NOT

查的是另外三條數(shù)據(jù)源

二. Postgresql 9.5和以上版本

也兼容上面的JSON查詢(xún)

2.1 模糊查詢(xún)

使用函數(shù)jsonb_path_exists（可以指定JSON路徑，如果是數(shù)組添加[*]）的正則查詢(xún)達(dá)到模糊查詢(xún)的效果

-- 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 時(shí)間搜索

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

到此這篇關(guān)于Postgresql JSON對(duì)象和數(shù)組查詢(xún)的文章就介紹到這了,更多相關(guān)Postgresql JSON對(duì)象內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論