Hello community!
I have a table with a “links” column as simple object type that holds a list of values, and trying to get a search iterator of rows with the links = []
This query does not return any values, even if I have many rows with links=[]
categories = app_tables.categories.search(links=q.not_([]))
I’ve tried none_of() but same results. Any other ideas?
Hi, @andyfe!
The Simple Object search uses subsetting rules in Postgres JSON, in which a query using [] will match everything, which is likely why you’re not getting the expected results. Unfortunately, because of this, there is currently no query that will work for matching empty lists, so you will have to fetch those by hand with a comprehension.
I read this last week and couldn’t figure it out at all, not knowing enough about the JSON column. 
@andyfe one other solution to searching for empty lists is making sure they never exist, by having anything that interacts with that column make sure that if the list is empty the object is then replaced by a None type object.
This would allow you very quickly search app_tables.categories.search(links=q.not_(None)) instead.
You could even combine both approaches, writing a comprehension that finds all of the empty lists and replaces them with None, it would not be fast, but at least you could do it at some other time without a user waiting for the server module to chug through the entire table before returning a result, every time they ask for something. (Background task? Run on startup of a form?)