Postgres Pattern matching with LIKE/ILIKE on all JSONB fields-CodePudding

I have a column, called metadata on my table (say trips) which is of JSONB type. I'd like to pattern match/search on all values of metadata.

For example, one of the entires of the metadata column is of the form:

{"city": "London", "trip_id": 2075209, "store_id": 108, "driver_id": 1166061,  "driver_name": "Jon Doe", "track_back_order": false}

I'd like to fetch all records with city as London. I've tried to cast the metadata column into text in my WHERE clause with

WHERE trips.metadata::TEXT ILIKE 'London'

which returns 0 results.

But searching at a field level works, for example this WHERE clause returns the appropriate results:

WHERE trips.metadata->>'city' ILIKE 'London'

But since the keys of the metadata column can be arbitrary, I cannot do the above query for each field. Any ideas on how I can solve for this?

CodePudding user response：

ILIKE or LIKE without any wildcards is the same as =.

So - ignoring case sensitivity - trips.metadata::TEXT ILIKE 'London' is essentially the same as trips.metadata::TEXT = 'London'

Obviously that doesn't work.

You can use a JSON path expression if you want to iterate over all keys:

where metadata @@ '$.* like_regex "London" flag "i"'