[SOLVED] How to specify a where clause that compares a JSONB field to some text using 'like'?

How to specify a where clause that compares a JSONB field to some text using 'like'?

I have a table groups which has a docid (varchar(200)) column, and a doc (jsonb) column. The doc documents have a title field on them.

The following queries run with no errors, however, they return no rows, even though I have rows that match the condition (e.g. I have a row where the title field has a value of 'Personal group')

select docid from groups where (doc['title'])::text like 'P%';

select docid from groups where cast(doc['title'] as text) like 'P%';

To confirm that the data is correct, this query works as expected, returning the docid of the 'Personal group':

select docid from groups where doc['title'] = '"Personal group"';

How do I get the 'like' to work as expected?

Solution

As you saw, the title of that document is the value '"Personal group"', not 'Personal group' which would have been matched by like 'P%'. Do not convert the JSONB value to its text representation, use ->> to access the JSONB string value:

select docid from groups where doc ->> 'title' like 'P%';