Tech Tip: Querying array attributes of the same element using linked arguments
PRODUCT: 4D | VERSION: 17 | PLATFORM: Mac & Win
Published On: September 20, 2018
It is often necessary to perform multiple query arguments on an object field of a Table. Typically one approach is to use QUERY BY ATTRIBUTE method to search through a specific object's attribute. For example, let's say we have the table [Competitor] with two fields: [Competitor]name and [Competitor]entries. [Competitor]entries is an object field that will contain a tournaments array listing all the types of competed tournaments as shown below.
Record 1 "name": "Bobby", "entries": { "tournaments":[ { "sport": "tennis", "type": "singles" }, { "sport": "badminton", "type": "doubles" } ] } Record 2 "name": "Chris", "entries": { "tournaments":[ { "sport": "tennis", "type": "doubles" } ] } |
Now let's say we want to make a query to find all players that compete in tennis and compete in doubles. We'll use QUERY BY ATTRIBUTE with the following arguments and result as shown below.
QUERY BY ATTRIBUTE([Competitor];[Competitor]entries;"tournaments[].sport";=;"tennis";*) QUERY BY ATTRIBUTE([Competitor]; & ;[Competitor]entries;"tournaments[].type";=;"doubles") |
The following query retrieved both Bobby and Chris, but shouldn't it just have retrieved Chris since he specifically plays doubles in tennis? In actuality, the query we executed only checked whether the competitor has a sport attribute of tennis and a type attribute of doubles within the same array, but not within the same object. We can fix this by adding a linking argument by adding a letter within the brackets [] (E.G. tournaments[a]) and repeat that letter to link any following queries.
QUERY BY ATTRIBUTE([Competitor];[Competitor]entries;"tournaments[a].sport";=;"tennis";*) QUERY BY ATTRIBUTE([Competitor]; & ;[Competitor]entries;"tournaments[a].type";=;"doubles") |
Chris is now successfully selected as he specifically competes in tennis for doubles.