Search code examples
postgresqlsql-likejsonb

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

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.

Solution

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