0

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

randomness
  • 1,377
  • 1
  • 14
  • 21
  • 2
    Possible duplicate of [How do I query using fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype) – Evaldas Buinauskas Nov 16 '15 at 11:37
  • The response given are all to navigate the json when the array is in key value format. When the json does not have keys but just array of values, how to frame the query?. I dont see an example of this case in official doc also – randomness Nov 16 '15 at 12:11

3 Answers3

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)
klin
  • 112,967
  • 15
  • 204
  • 232
1

How about

select * from tbl where data::text like '%[email protected]%'
Ruslan
  • 2,691
  • 1
  • 19
  • 29
0

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

Himanshu
  • 1