PostgreSql查询数组值
问题描述:
我有以下JSON格式存储为postgresql中的JSONB列。PostgreSql查询数组值
{DisplayName":"Bob marley","FName":"Bob","Title":null,"LName":"Marley","State":null,"EmailAddresses":["[email protected]","[email protected]"]},
{DisplayName":"Bob martin","FName":"Bob ","Title":null,"LName":"Martin","State":null,"EmailAddresses":["[email protected]","[email protected]"]}
我想查询它以Bob开头的EmailAddresses阵列。我能够使用LIKE和ILIKE运算符查询正常的字符串类型键,但是在ARRAY类型内部搜索会产生问题。 请指教。
答
如果你想检查是否在jsonb场比赛阵列来你的面具的任何元素:
SELECT *
FROM jtable
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements_text(jfield->'EmailAddresses') AS j
WHERE j ~ 'bob'
)
jfield
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"FName": "Bob", "LName": "Marley", "State": null, "Title": null, "DisplayName": "Bob marley", "EmailAddresses": ["[email protected]", "[email protected]"]}
{"FName": "Bob ", "LName": "Martin", "State": null, "Title": null, "DisplayName": "Bob martin", "EmailAddresses": ["[email protected]", "[email protected]"]}
(2 rows)
或者,如果你想要的电子邮件列表匹配掩码:
WITH w AS (
SELECT jsonb_array_elements_text(jfield->'EmailAddresses') AS emails,
*
FROM jtable
)
SELECT * FROM w WHERE emails ~ 'bobm'
emails | jfield
---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
[email protected] | {"FName": "Bob", "LName": "Marley", "State": null, "Title": null, "DisplayName": "Bob marley", "EmailAddresses": ["[email protected]", "[email protected]"]}
[email protected] | {"FName": "Bob ", "LName": "Martin", "State": null, "Title": null, "DisplayName": "Bob martin", "EmailAddresses": ["[email protected]", "[email protected]"]}
(2 rows)
大,三江源 – puneet