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

postgresql sql-like jsonb

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

postgresql sql-like jsonb

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'

CodeHunter