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.

'bar/foo/item/b' LIKE '%item/' || NULLIF('', ids->>'id') || '%' — Rhim
– Rhim, Commented Feb 11, 2019 at 19:17
@Rhim that actually remove the error, but it's not working, the values are always getting empty and the expression is evaluating to true — yosbel
– yosbel, Commented Feb 11, 2019 at 21:02

Laurenz Albe · Accepted Answer · 2019-02-12 07:21:57Z

12

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

answered Feb 12, 2019 at 7:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yosbel Over a year ago

I tried that solution, but it's giving empty result:

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

Laurenz Albe Over a year ago

Yes, but that was the cause for the error message. There was probably some other problem with your query. But you have a solution now anyway.

yosbel Over a year ago

You are right, thanks for your help, I'm new to Postgres. I still don't understand why using jsonb_array_elements_text it does concatenate the values and using jsonb_array_elements don't. The correct answer to my question I think is yours, I'm just trying to understand why it does not work as I expect, so you can edit it to accept it :).

yosbel Over a year ago

As I see in the docs jsonb_array_elements: Expands a JSON array to a set of JSON values and jsonb_array_elements_text: Expands a JSON array to a set of text values, it have sense that text values should be used to concatenate with other text values and probably the json values from jsonb_array_elements should be casted or converted to text before used.

Laurenz Albe Over a year ago

Shouldn't matter, because json will implicitly be cast to text upon concatenation.

yosbel · Accepted Answer · 2019-02-11 21:06:59Z

1

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'

answered Feb 11, 2019 at 21:06

yosbel

1,8514 gold badges23 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related