Reputation: 149
I'am thinking about storing some data in postgres jsonb data type. There would be a structure like
{"name": "Jhon Smith", "emails": ["[email protected]", "[email protected]"],
"phones": ["123456789", "987654321"]}.
I know, that i can search this structure like
where contact->'emails' @> '"[email protected]"'::jsonb;
But what I need is to search my data with some LIKE operator, so
where contact->'emails' <SOME_JSON_LIKE_OPERATOR> "smith"';
I can't find if psql have something similar, maybe it does not. So, maybe I can convert contact->'emails' field to Text ('["[email protected]", "[email protected]"]') and then use simple LIKE.. How would you have solved this problem?
Upvotes: 1
Views: 1070
Reputation: 23236
You can expand the json array into a recordset of text and search that in whatever manner you like:
where exists (
select 1 from json_array_elements_text(contact->'emails')
where
value like "%smith%"
)
Upvotes: 4