Postgres invalid input syntax for type json Detail: Token "%" is invalid

Question

I'm trying to check if some text contains the concatenation of a text and a value from an array in Postgres, something like:

SELECT true from jsonb_array_elements('["a", "b"]'::jsonb) as ids 
WHERE 'bar/foo/item/b' LIKE '%item/' || ids->>'id' || '%'

I'm getting the following error:

ERROR: invalid input syntax for type json Detail: Token "%" is invalid. Position: 95 Where: JSON data, line 1: %...

How can I make use of the values of the array, concatenate them with the text and check the LIKE expression?

I have tried several ideas of explicitly adding a cast like ::jsonb, but no luck so far.

Answer 1

The problem is that the || and ->> operators have the same precedence and are left associative, so the expression is interpreted as

(('%item/' || ids) ->>'id') || '%'

You'd have to add parentheses:

'%item/' || (ids->>'id') || '%'

Answer 2

Finally got this working, this is the result:

SELECT true from jsonb_array_elements_text('["a", "c"]'::jsonb) as ids 
WHERE 'bar/foo/item/b' LIKE '%item/' || ids.value || '%'

The key changes were to use jsonb_array_elements_text instead of jsonb_array_elements and ids.value instead of ids->>'id'