I am querying a jsonb field in postgres from retool.
I get a different result when I query in TablePlus, versus when I query in Retool.
Here is the query in question:
SELECT jsonb_array_elements(addresses) AS addresses FROM allow_list WHERE uid = '4c3febc4-b9cb-405d-b939-f43ee741a6fe';
When I run the query in TablePlus, I get exactly what I expect:
However, when I run in Retool, I get the following result:
I've run different versions of this query on different records in my database, and the pattern is always the same. The last string in the array is not shown in Retool. I've tried logging the result from the query in retool, and it always matches what I am shown in the preview.
Any ideas what is going on here?
Thank you
Hey @austinxyz!
Are you able to select that row individually? Or if you don't use jsonb_array_elements does it show up in your select query?
I did some testing on my end and haven't yet been able to reproduce the behavior. I'm wondering what I might be missing from your setup.
I talked with support in a chat, and they said I needed to add curlies around the uid:
SELECT
jsonb_array_elements(addresses)
FROM
allow_list
WHERE
uid = {{'4c3febc4-b9cb-405d-b939-f43ee741a6fe'}}
This worked at the time. Now that I retry the query without curlies, it now works too.
I created this query in attempt to get to the bottom of an issue I was having with another query, which, when I made this post, would return only 1 address (one index from the jsonb array) where, for this uid, there should have been 2.
As a sanity check, I duplicated the query and ran it, and it returned what I expected.
However, after I published it and reloaded, it stopped returning any addresses:
And this has continued every time I've tried it since. If I duplicate the broken query, or copy and paste the same query into a new query instance, it works the first time, but inevitably starts returning nothing later.
I'm not sure if this answers your question, but if I remove jsonb_array_elements from the query, I do get the correct result. However, now I'm getting the correct result for this query with or without the curlies or the jsonb_array_elements.
However, I am still getting inconsistent results for the other query in my other update.
Thanks for all the context here @austinxyz. We've been looking into this with the dev team and it looks like there's a bug with bringing data into the app model in certain cases. They'll continue to investigate further and we can report back here when it's been fixed!
In the meantime, it looks like duplicating the query may be the best workaround for now.
