How do I use the query function in the Databases page?

[Peter Viglietta]

Hi everyone,

As you can probably tell I'm very new to the mobile app development world.. can someone tell me how to use the Query function when you're in the databases section of Appery? I have a bunch of data cleanup that I have to do and I want to be able to run a query to find the problematic rows. How do I run a query equivalent to this SQL query?

SELECT * from Cities where cityname like '%brooklyn%'

In other words, I want to run a query that returns all the cities in my Cities collection whose names contain 'brooklyn'.

Any help is very much appreciated

[Illya Stepanov]

Hi Peter,

This section in our docs would help: http://devcenter.appery.io/documentat...

And I recommend you to walk through our tutorials here: http://devcenter.appery.io/tutorials/...
-- it will help you better understand DB in Appery.io.

[Peter Viglietta]

I just need to know how to run a query in Databases my DB Rest query.. what is that field? can I just put a JS query in there? I need to see every record in my Cities table where the City column contains 'Brooklyn'. How do I write that? I'm new to this as you can probably tell

[Evgene Karachevtsev]

Hello Peter,

You should pass a json object in the query. SQL function like is implemented through regular expression
You may find more info here:
http://devcenter.appery.io/documentat...
http://docs.mongodb.org/manual/refere...

In your case query would be like this:

code{"cityname" : { $regex: "brooklyn" , $options: "i" }}/code

[Peter Viglietta]

Thanks Evgene. As you can see I'm still learning the fundamentals, pointers like this are very useful!

In my cities collection I have a City column and a state column. There are many duplicates, for example there are 15 or so iterations of Brooklyn, NY. I want to find all the city/state combinations that have duplicates. Can you tell me that query?

[Alena Prykhodko]

Hello Peter,

Unfortunately Appery.io Database API doesn't support such requests.

In order to create query like this you need to get all data from collection (you can do it within project with list service http://devcenter.appery.io/documentat... or from Server code) and then sort it as you need with custom JavaScript, you will need to search online for more information.