Query for data in Postgresql with forward slash in jsonb field-CodePudding

I have a transactions table which has a jsonb field with some additional info. I am storing some invoice numbers in jsonb which contain / in them. While trying to query it I am facing an error. How to query for json containing this invoice number with forward slash?

SELECT * 
FROM "transactions" 
WHERE transactions"."other_data" -> 'invoice_number'='ABC1/211/000359';

I am getting below error


LINE 1: ...transactions where other_data -> 'invoice_number'='ABC1/211...

                                                             ^

DETAIL:  Token "ABC1" is invalid.

CONTEXT:  JSON data, line 1: ABC1...

CodePudding user response：

As documented in the manual, the operator -> returns a json value, but you are trying to compare it to a string (varchar) value. So Postgres tries to convert the right hand side to a json value.

You need to extract the value as a string using the ->> operator

WHERE transactions"."other_data" ->> 'invoice_number' = 'ABC1/211/000359';