我在 Postgres 中有一張帶有 JSONB 檔案的表格。JSONB 包含 jsons 為：
{
"id": "adf59079-4921-4abc-a262-1dc8c2b1ccc7",
"lastname": "LOBATOS",
"firstname": "Leslie",
"birth_date": "1988-01-26",
"gender": 3,
"contacts": {
"phoneList": [
{
"fullNumber": "0671234567",
"verifyStateId": 1
},
{
"fullNumber": "0671234588",
"verifyStateId": 0
}
]
}
}
我需要選擇以下資料集（以 SQL 表示法）
SELECT id, lastname, fullNumber FROM <JSONB-field>
WHERE fullNumber LIKE '067%' and verifyStateId = 1
請幫助撰寫查詢
uj5u.com熱心網友回復：
您可以使用 JSON 路徑運算式來過濾掉所需的行：
where the_column @? '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'
要真正得到fullNumber你需要重復 JSON 路徑以提取有問題的陣列元素：
select id,
the_column ->> 'lastname',
jsonb_path_query_first(the_column,
'$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'
) ->> 'fullNumber' as fullnumber
from the_table
where the_column @? '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'
WHERE 條件可能會利用 GIN 索引the_column來提高性能。
如果沒有這樣的索引或者性能不是那么重要，您可以通過使用派生表來避免重復 JSON 路徑：
select *
from (
select id,
the_column ->> 'lastname',
jsonb_path_query_first(the_column, '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)') ->> 'fullNumber' as fullnumber
from the_table
) t
where fullnumber is not null
uj5u.com熱心網友回復：
您可以使用下一個查詢：
with unnested as (
select
fld->>'id' id, fld->>'lastname' lastname,
jsonb_array_elements(((fld->>'contacts')::jsonb->>'phoneList')::jsonb)
from tbl
) select id, lastname, jsonb_array_elements->>'fullNumber' from unnested;
PostgreSQL 小提琴
====================================== ========== ============
| id | lastname | ?column? |
====================================== ========== ============
| adf59079-4921-4abc-a262-1dc8c2b1ccc7 | LOBATOS | 0671234567 |
-------------------------------------- ---------- ------------
| adf59079-4921-4abc-a262-1dc8c2b1ccc7 | LOBATOS | 0671234588 |
-------------------------------------- ---------- ------------
uj5u.com熱心網友回復：
演示
WITH cte AS (
SELECT
jsonb_path_query(data, '$.contacts.phoneList[*].verifyStateId')::text AS verifyStateId,
jsonb_path_query(data, '$.id')::text AS id,
jsonb_path_query(data, '$.lastname')::text AS lastname,
jsonb_path_query(data, '$.contacts.phoneList[*].fullNumber')::text AS fullnumber
FROM
extract_jsonb
)
SELECT
*
FROM
cte
WHERE
verifyStateId = '1'::text
AND fullnumber ILIKE '"067%'::text;
由于轉換為文本，因此 fullnumber 的第一個字符是“
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484232.html
標籤：json PostgreSQL