search table by Property where Null

eric-yi · August 8, 2015, 12:30am

Is it possible to search a table for a certain property whose value is null?

Obviously https://api.backendless.com/v1/data/Request?where=requestId%3Dnull does not work.

Thanks.

mark-piller · August 8, 2015, 2:05am

try this where clause:

requestId IS null

Regards,
Mark

eric-yi · August 8, 2015, 2:08am

The question is how would I include that parameter in a URL.

https://api.backendless.com/v1/data/Request?where=requestIdISnull ?

That doesn’t look right. Didn’t work.

mark-piller · August 8, 2015, 2:09am

it must be URL encoded. Try this tool for example:

http://meyerweb.com/eric/tools/dencoder/

You will get the following result:
requestId%20IS%20null

eric-yi · August 8, 2015, 2:14am

Really thought that one would work. The result was

{"offset":0,"data":[],"nextPage":null,"totalobjects":0}

mark-piller · August 8, 2015, 2:15am

Do you have any objects where requestId is null ?

eric-yi · August 8, 2015, 2:17am

Yes, I changed one of the requestId to null and made sure it saved.

mark-piller · August 8, 2015, 2:30am

Try testing it in console. Make sure “SQL Search” is ON:

https://backendless.com/feature-14-sql-based-search-for-data-objects-using-console/

eric-yi · August 10, 2015, 4:44pm

I turned on SQL Search and still no luck.

“https://api.backendless.com/v1/data/Request?where=requestId%20IS%20null” returns {“offset”:0,“data”:[],“nextPage”:null,“totalObjects”:0} while

“https://api.backendless.com/v1/data/Request?where=requestId%20%3D%20null” returns {“code”:1017,“message”:“Invalid where clause”}

As a workaround, I am changing the default values to 1 so that they are searchable.

mark-piller · August 10, 2015, 4:55pm

Could you please attach 2 screenshots of Backendless console:

With all the data in the table showing table columns
When you run the query

Regards,
Mark

eric-yi · August 10, 2015, 4:59pm

Data + query in console attached.

mark-piller · August 10, 2015, 5:02pm

You’re using REST Console, not the SQL search. The SQL search is this:

Also, I think the data which you believe is null, might be an empty string:

eric-yi · August 10, 2015, 5:10pm

I think you’re right about the data being an empty string rather than null.

requestId = ‘’ returns the correct data.

Did I change that field from null to an empty string, or is it created as an empty string?

eric-yi · August 10, 2015, 5:17pm

I see now. It looks like the data is created as an empty string, unless I specify “(null”) in Default Value. In that case, searching requestId%20IS%20null seems to work Thanks.

mark-piller · August 10, 2015, 5:19pm

If the value is missing we use null as the default. You must’ve changed it to an empty string.

eric-yi · August 10, 2015, 5:20pm

I must have. Ok thanks for the clarification. I’ll change the default value back.