Search Postgres JSONB fields

Question

I want to search the pattern '%myvalue%' in key and value for a json field, return any one that matched.

I use

select * from dialogue_responses where field_rule like '%myvalue%'

it prompts me error

ERROR:  operator does not exist: jsonb ~~ unknown
LINE 1: select * from dialogue_responses where field_rule like '%auf...
                                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

What's he correct way of doing this?

Mabu Kloesen · Accepted Answer · 2018-01-26 02:40:36Z

1

You can cast into text in the query. Such as

select * from dialogue_responses where field_rule::varchar like '%myvalue%'

answered Jan 26, 2018 at 2:40

Mabu Kloesen

1,3687 silver badges8 bronze badges

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

2 Comments

Mabu Kloesen Over a year ago

@VaoTsun: I really didn't this case is answered before. I've just answer base on my experience. Thanks for your advice.

Vao Tsun Over a year ago

I sent you the link with opinion regarding text vs varchar. its not other answer. I upvoted your answer because this is the easiest way to find match in both keys or values in json. I just was surprised you cast jsonb to varchar, not text.

Collectives™ on Stack Overflow

Search Postgres JSONB fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related