Postgresql JSON對象和數(shù)組查詢功能實現(xiàn)
一. Postgresql 9.5以下版本
1.1 簡單查詢(缺陷:數(shù)組必須指定下標,不推薦)
1.1.1 模糊查詢
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' like '%bb%'
address字段是JSONArray類型,所以在路徑中,使用數(shù)字索引來訪問數(shù)組元素,從 0 開始計數(shù)。
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
,數(shù)據(jù)結構為
[{ "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%' );
當該層級類型是數(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ù)字類型后面需要轉換
::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 );
查的是另外三條數(shù)據(jù)源
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
查的是另外三條數(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"]' );
此時使用的操作符是
->
,返回值是jsonb
類型
1.2.10 包含 NOT
查的是另外三條數(shù)據(jù)源
二. Postgresql 9.5和以上版本
也兼容上面的JSON查詢
2.1 模糊查詢
使用函數(shù)
jsonb_path_exists
(可以指定JSON
路徑,如果是數(shù)組添加[*]
)的正則查詢達到模糊查詢的效果
-- 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
到此這篇關于Postgresql JSON對象和數(shù)組查詢的文章就介紹到這了,更多相關Postgresql JSON對象內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
PostgreSQL中offset...limit分頁優(yōu)化的一些常見手段
我們在使用數(shù)據(jù)庫進行分頁查詢時,隨著offset過濾的數(shù)據(jù)越來越多,查詢也會越來越慢,下面這篇文章主要給大家介紹了關于PostgreSQL中offset...limit分頁優(yōu)化的一些常見手段,需要的朋友可以參考下2023-05-05使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法
這篇文章主要介紹了使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法,本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-08-08PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案
這篇文章主要介紹了PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01解決postgresql 數(shù)字轉換成字符串前面會多出一個空格的問題
這篇文章主要介紹了解決postgresql 數(shù)字轉換成字符串前面會多出一個空格的問題,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12PostgreSql生產(chǎn)級別數(shù)據(jù)庫安裝要注意事項
這篇文章主要介紹了PostgreSql生產(chǎn)級別數(shù)據(jù)庫安裝要注意事項,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-08-08