PostgreSQL json search

Question

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?

donkopotamus · Accepted Answer · 2016-06-06 11:13:50Z

4

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%"
    )

answered Jun 6, 2016 at 11:13

donkopotamus

23.4k3 gold badges58 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kamuniaft Over a year ago

@donkopotamis Awesome, thank you! That's exactly what i was looking for.

Collectives™ on Stack Overflow

PostgreSQL json search

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related