CODEWITHSUNDEEP
jsonpostgresqljsonb
kamuniaft
kamuniaft

Reputation: 149

PostgreSQL json search

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

Answers (1)

donkopotamus
donkopotamus

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

Related Questions