I wouldn’t even mention that you are working with Anvil.
I have created a simple app, did a select * from users
and a select * from reports
to see what was in there.
Then I asked this to chatgpt (I’m pasting it here without even rereading it) and got something working out of it:
i have a postgresql database with two tables: users and reports.
users has an _id column containing numbers as primary keys.
reports has the jsonb users column containing a list like this, where the user ids are 1147978440 and 1147978441:
[{"id": "[790289,1147978440]", "mac": "23aa55b2dbdd0e1f112d080fcf9c8f484f75a14627a7621c03466d34ae7c86d4", "backend": "anvil.tables.Row", "methods": ["__anvil_iter_page__", "__getitem__", "update", "get_id", "__setitem__", "delete", "set"], "permissions": []}, {"id": "[790289,1147978441]", "mac": "aa6f48212b305e4f2b3ece53a2da8b746d2acc395ff00fcd39e234812551032e", "backend": "anvil.tables.Row", "methods": ["__anvil_iter_page__", "__getitem__", "update", "get_id", "__setitem__", "delete", "set"], "permissions": []}]
create a query that joins the two tables and finds all the users that have the id included in that jsonb structure
This wasn’t what you were looking for. It was just a way to explore and see how chatgpt would approach the problem. The result was way more complex than I expected, but it was kind of working.
As I mentioned earlier, I imagine this would be a long chat (I’m sure for those who know postgres better than me would be shorter).