Postgresql JSON對(duì)象和數(shù)組查詢功能實(shí)現(xiàn)
更新時(shí)間：2023年11月13日 16:04:00 作者：wnfee
這篇文章主要介紹了Postgresql JSON對(duì)象和數(shù)組查詢功能實(shí)現(xiàn),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧
一. Postgresql 9.5以下版本
1.1 簡(jiǎn)單查詢(缺陷：數(shù)組必須指定下標(biāo)，不推薦)
1.1.1 模糊查詢
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' like '%bb%'address字段是JSONArray類型，所以在路徑中，使用數(shù)字索引來訪問數(shù)組元素，從 0 開始計(jì)數(shù)。
1.1.2 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' = 'bbb'
如果字段是int類型，后面需要添加::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ì)象，路徑不存在則返回空。返回類型是json(b)#>>：獲取在指定路徑的 JSON 對(duì)象，路徑不存在則返回空。返回類型是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 模糊查詢
查詢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í)類型是數(shù)組就添加
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%'
);查的是另外三條數(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ù)字類型后面需要轉(zhuǎn)換
::int，因?yàn)?->>操作符的返回類型是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類型
1.2.10 包含 NOT
查的是另外三條數(shù)據(jù)源
二. Postgresql 9.5和以上版本
也兼容上面的JSON查詢
2.1 模糊查詢
使用函數(shù)
jsonb_path_exists（可以指定JSON路徑，如果是數(shù)組添加[*]）的正則查詢達(dá)到模糊查詢的效果
-- 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ù)組查詢的文章就介紹到這了,更多相關(guān)Postgresql JSON對(duì)象內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作
這篇文章主要介紹了postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 字符串處理與日期處理操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了查詢PostgreSQL占多大內(nèi)存的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
快速解決PostgreSQL中的Permission denied問題
這篇文章主要介紹了快速解決PostgreSQL中的Permission denied問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在PostgreSQL中實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)的關(guān)聯(lián)查詢
在 PostgreSQL 中,通常情況下的關(guān)聯(lián)查詢是在同一個(gè)數(shù)據(jù)庫(kù)的不同表之間進(jìn)行的,然而,在某些復(fù)雜的應(yīng)用場(chǎng)景中,可能需要實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)的關(guān)聯(lián)查詢,本文將詳細(xì)探討如何在 PostgreSQL 中實(shí)現(xiàn)這一需求,并通過示例代碼進(jìn)行說明,需要的朋友可以參考下2024-08-08
這篇文章主要介紹了查看postgresql系統(tǒng)信息的常用命令操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
文章介紹了PostgreSQL中JSONB的使用,包括JSONB的基礎(chǔ)操作、索引策略、數(shù)組操作和批量更新,文章通過實(shí)例詳細(xì)講解了JSONB的使用方法,幫助讀者解決實(shí)際工作中的問題,感興趣的朋友跟隨小編一起看看吧2025-12-12
這篇文章主要介紹了PostgreSQL批量修改函數(shù)擁有者的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql重置序列起始值，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostGIS中ST_Union與ST_Collect的區(qū)別與使用詳解
這篇文章主要介紹了PostGIS中ST_Union與ST_Collect的區(qū)別與使用,對(duì)于初入PostGIS世界的新手來說,眾多的地理空間函數(shù)可能會(huì)讓人感到眼花繚亂,不知從何下手,而ST_Union與ST_Collect這兩個(gè)函數(shù),由于它們?cè)诠δ苌洗嬖谝欢ǖ南嗨菩?常常容易被混淆,需要的朋友可以參考下2026-01-01