PostgreSql Querying Array Values

Question

I have the following JSON format stored as JSONB column in 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]"]}

I want to query for EmailAddresses Array where it starts with Bob. I am able to query for normal string type keys using the LIKE and ILIKE operators, however for searching inside ARRAY types, is posing a problem. Please advise.

Answer 1

If you want to check if any element of array in jsonb field match to your mask:

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)

Or if you want list of email that match to mask:

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)