Start a new topic
Answered

How to query objects and/or arrays?

Hello


While diving deeper into queries with Kinvey's Javascript API I'm trying to cover some requirements. The base documentation I'm using is from here


I try to process a query that will should find a given value in an object (inside a collection).

Example:

- Given string (by user): James

- Object it should search into: {'id': 'ABC', 'name': 'Hello World!', 'create': 'James'}


I've seen there is a command called 'contains' but it looks to me this command is working the other way around (list given and search in a single column value). I anyway couldn't make 'contains' working (no result back) and realised by that the general lack of examples there. All over I'm looking for something like mongoDBs $in like here

IF that is possible I wonder if it works for objects (with several values in it) AND for arrays as well?


Case sensitive?

There is a yellow box in the provided link above ( here )mentioning regexpression and case sensitive. I assume this is also the case for any non-regexpression cases since my queries won't work. I couldn't find any option that would allow case-insensitive queries and follow your "recommendations": Create an extra field just for holding the search value. This is (imho) a big disadvantage. In my case the user enters a searchstring and I have to compare it with different column values and object lists in the columns. All over: copying nearly the whole collection just for building up lower or upper cased search strings. :(


Regards


Best Answer

Hello Pranav


Thanks a lot! In between I have found a proper solution, that works great and will be explained later on for others with same "problem". For better understanding what I want to do:

I am holding "objects" (not technical) in a collection with their names, unique public ID and where they are worldwide located (GPS coordinates: _geoloc).

The user can search for them by entering any kind of text, like title, public ID, a place (f.e. "Paris"). That means I don't know what exactly he is looking for and I have to check all the possible values he can search for. 


Case 1: 

User doesn't enter anything in the search input: Get users current location (geolocation) and find all objects around him with a given radius. For this I'm using the search option "near", which seems to throw an error. (I have sent a another forum entry for that yesterday with title "The following filters are not supported locally: $nearSphere"") I hope it's not related to the delta sync you kindly enabled for me.


Case 2:

The user enters some text and all relevant columns (and values in objects/arrays) must be checked. It would be possible to store all values in lower case in the collection for easier search but I need those values also for display purposes so I can't just store the original values upper or lower cased (for easier search).


Solution:

I have now created an additional array column (for each object) with stored lower cased keywords the user can search for (title, public ID, etc.) So I only need to check the users input text against this column (by "contains") and this works fine now. 

Even if the upper/lower case would be no issue so I could search in the originally provided columns I still would have a cascade of $or's to check them all. So I think searching in a predefined array column is still way faster than using a lot of $or's in a single query. The solution is working very well and really fast! Coming from relational DB development this is confusing and "not wanted" but I'm aware this (redundancy) is absolutely ok in big data tables.


Regards


I made it working in between with Arrays only. That fine so far....

Tayger,


Simplest query considering your use-case for objects is explained on this link. You can use "$or" operator if you have multiple values. If this doesn't help please explain you use-case providing the query you are using, what output you are getting and what is the expected output.


Thanks,

Pranav


Answer

Hello Pranav


Thanks a lot! In between I have found a proper solution, that works great and will be explained later on for others with same "problem". For better understanding what I want to do:

I am holding "objects" (not technical) in a collection with their names, unique public ID and where they are worldwide located (GPS coordinates: _geoloc).

The user can search for them by entering any kind of text, like title, public ID, a place (f.e. "Paris"). That means I don't know what exactly he is looking for and I have to check all the possible values he can search for. 


Case 1: 

User doesn't enter anything in the search input: Get users current location (geolocation) and find all objects around him with a given radius. For this I'm using the search option "near", which seems to throw an error. (I have sent a another forum entry for that yesterday with title "The following filters are not supported locally: $nearSphere"") I hope it's not related to the delta sync you kindly enabled for me.


Case 2:

The user enters some text and all relevant columns (and values in objects/arrays) must be checked. It would be possible to store all values in lower case in the collection for easier search but I need those values also for display purposes so I can't just store the original values upper or lower cased (for easier search).


Solution:

I have now created an additional array column (for each object) with stored lower cased keywords the user can search for (title, public ID, etc.) So I only need to check the users input text against this column (by "contains") and this works fine now. 

Even if the upper/lower case would be no issue so I could search in the originally provided columns I still would have a cascade of $or's to check them all. So I think searching in a predefined array column is still way faster than using a lot of $or's in a single query. The solution is working very well and really fast! Coming from relational DB development this is confusing and "not wanted" but I'm aware this (redundancy) is absolutely ok in big data tables.


Regards

Check out our weekly blog about transforming SQL queries to complex functions using the ARRAY function prepaidgiftbalance.

Login or Signup to post a comment