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 | ["abc@xyz.com"] 
03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | ["abc@xyz.com"]

I want to write a query to select all rows if the column data contains "abc@xyz.com"

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 | "abc@xyz.com"
 03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | "abc@xyz.com"
(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 = '"abc@xyz.com"';

                 guid                 | type  |     elem      
--------------------------------------+-------+---------------
 9cf100e8-87a8-4ce7-b187-b618bf2dc156 | email | "abc@xyz.com"
 03d5b41c-b834-4399-95dc-c51b1e214fb3 | email | "abc@xyz.com"
(2 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
1

How about

select * from tbl where data::text like '%abc@xyc.com%'
Ruslan
  • 2,691
  • 1
  • 19
  • 29
0

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

Himanshu
  • 1