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...
>Solution :
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';