Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Query for data in Postgresql with forward slash in jsonb field

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...

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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';
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading