I've got a script that uploads data to a Postgres database. I've now got a requirement where I need to make sure I'm not uploading duplicate data. The field I need to check against is a jsonb one. I can't make the check on another field.
I've seen a lot of questions that ask whether it's possible to check if key/value exists inside a jsonb object, but I've not come across a case where anyone has asked to check if an entire jsonb object exists or not.
One of the things I tried was converting the database object to a string (::text) and comparing that to a stringified version of the object that I need to insert. This has 2 issues.
- JSONB doesn't store the order of the keys, so it's very likely that my stringified object won't match even if all the values and keys are present.
- I typecasted one of the the db values to a string, and used the exact same string in my WHERE clause to see if it will return a result. For some reason this did not work either. So even when my string has the same order of keys and values, it does not result in a match.
This is what I tried:
select paxInfo::text from pax_meta where id = 32560; -- Took a value to test against
select pax_id from pax_meta where paxInfo::text = '....Lots of text';
I was expecting the second line to work since it would match the string exactly as it was stored in the database, but this did not work either
