PostgreSQL查询数组值

puneet 发表于 Dev

浦那

我将以下JSON格式存储为PostgreSQL中的JSONB列。

{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类型中进行搜索会带来问题。请指教。

罗曼·楚卡克（Roman Tkachuk）

如果要检查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)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。