query on array=null not working

Gi Ma

I have the following situation:

• i have a db table called notification
• it has a field called "target_users", it's text field but marked as list, so basically the exact same as an array (if I understood the structure correctly)
• when I query all the notifications that have target_users = null, I will not get those where this list has been initiated, yet is still empty

so basically, I can't seem to query on those where there are no target_users, or at least I wouldn't know how to do it? I even tried adding the filter "length" but that doesn't seem to work either.

also, when creating rows, by default it's not set as NULL, but it's simply an empty array. and there's no way to change it from empty to null.

I believe it should be possible to define empty, or null, and work with it somehow?

Michael Udinski

Hi @Gi Ma since you are dealing with a list, you actually won't want to use null but an empty array. So if you want to see empty target_user fields you should structure the statement like this:

WHERE

db: notifications.target_users = array: []

Gi Ma

Thank you Michael!

that would work, however then I also would have to add

OR db: notifications.target_users = null

..to avoid that scenario. Could you please provide some information as to when a new row is created with null and when it is created with an empty array? I've found that there are differences to which I can't seem to find any explanation because the rows have been created with the same method.

Ray Deck

There is a filter called "is_empty" that you can apply to your left-hand-side value. is_empty starts with your value and converts it to a true or false based on whether the value in question is "falsy" (0, null, empty array, blank string) Then you can check for whether the filtered value is "true".

Gi Ma

@Michael Udinski 's suggestion doesnt seem to work:

returns:

message: ParseError: "notifications.target_users" = [], message=Lists are not supported.

----

@Ray Deck that's what I was thinking too, however I can't seem to have this in my list but can't understand why:

this is the structure of the db:

Ray Deck

Ah, is_empty is useful, but it is for the regular function stack. When querying a table, you have a different set of filters available to you.

So what I would do is if you know you are looking for nulls or blank arrays, use the length filter.

To wit, make a query filter of

x = null

OR

x:length = 0

Not pretty, but then again it's not a super pretty data problem!

Gi Ma

tried that too, but I get:

SQLSTATE[22023]: Invalid parameter value: 7 ERROR: cannot get array length of a scalar

(thank you for helping btw.! @Ray Deck )

Ray Deck

A screenshot could help with the diagnosis - sometimes the devil is in the details! The screenshots need to be what the query filter is currently, along with the current structure of the field we are filtering on.

Gi Ma

Sure thing!

Field structure is in the post above.

error:

thank you

Michael Udinski

Hi @Gi Ma just confirmed setting it to an empty array is throwing an error. But also confirmed using the length filter works as expected - I expect your SQLState error may be related to your chained expression. Can you please write into support chat with your error and query?

Ray Deck

Thank you so much for the screenshot, @Gi Ma ! Can you share what the structure is of the field target_users? Like, what you see in the table view in Xano, and what the drop-down looks like for editing the settings/type of the field?