Querying Postgres with json data type (when there is no key in json array)

Question

I have a table as shown below. the datatype of data is json (json does not have keys but just array of values)

guid                                 | type  | data           |       
9cf100e8-87a8-4ce7-b187-b618bf2dc156 | email | ["[email protected]"] 
03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | ["[email protected]"] 

I want to write a query to select all rows if the column data contains "[email protected]"

Answer 1

Use json_array_elements() to unpack a json array:

select guid, type, json_array_elements(data) elem
from guids;

                 guid                 | type  |     elem      
--------------------------------------+-------+---------------
 9cf100e8-87a8-4ce7-b187-b618bf2dc156 | email | "[email protected]"
 03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | "[email protected]"
(2 rows)

Use a derived table to filter the data:

select *
from (
    select guid, type, json_array_elements(data)::text elem
    from guids
    ) sub
where elem = '"[email protected]"';

                 guid                 | type  |     elem      
--------------------------------------+-------+---------------
 9cf100e8-87a8-4ce7-b187-b618bf2dc156 | email | "[email protected]"
 03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | "[email protected]"
(2 rows)

Answer 2

How about

select * from tbl where data::text like '%[email protected]%'

Answer 3

instead of unpacking by json_array_elements(), you can unpack by json_array_elements_text()