Postgres JSON数组-如何做选择字段

Question

我在Postgres中有JSONB文件表。JSONB包含json作为:

{
"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

请帮忙写查询

Answer 1

您可以使用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条件可以利用the_column上的GIN索引来提高性能。

如果没有这样的索引或者性能不是那么重要，可以通过使用派生表来避免重复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

Answer 2

您可以使用下一个查询:

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 |
+--------------------------------------+----------+------------+

Answer 3

demo

---

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的第一个字符是">