Search by linking array attribute query arguments

Object fields, introduced with 4D v15, allows to store and index unstructured data. This could be a common set of data, like first name, last name, birthday. It could be different data in each record, such as a shop that will need different attributes for shoes (size, color), computers (CPU, memory), printers (color, ink), a list of values or all in any combination.

Here is a real User Story from a 4D customer :

“My badge application manages the access rights for each person to a building and for a time slot. This information is stored in a database as an array of objects ( e.g.: {access right, building, time slot} ). I want to be able to look for people who have access to a building during a time slot.”

4D provides several ways to query for data, using index and so extremely fast.

4D v16 R2 will further enhance the feature set, by allowing even more complex queries inside a list of elements in a single object. When searching in array attributes with multiple query arguments joined by the AND operator, you can choose to get the records where:

• matching arguments are in at least one element
• matching arguments are in the same element (new)

 Example

Let’s take an example with the following two records:

{
    "name":"martin",
    "locations": [ 
        { "kind":"home", "city":"paris" } ]
 } , {
    "name":"smith",
    "locations": [
        { "kind":"home", "city":"lyon" },
        { "kind":"office", "city":"paris" } ]
 }

You want to find people with a “home” location kind in the city “paris”.

Matching arguments are in at least one element

If you write:

QUERY BY ATTRIBUTE([People];[People]OB_Field;"locations[].kind";=;"home";*)
QUERY BY ATTRIBUTE([People]; & ;[People]OB_Field;"locations[].city";=;"paris")

The query returns “martin“ and “smith“ because “smith“ has a “locations” element whose “kind“ is “home“ and whose “city“ is “paris“, even if it’s not the same element.

Matching arguments are in the same element

If you write:

QUERY BY ATTRIBUTE([People];[People]OB_Field;"locations[a].kind";=;"home";*)
QUERY BY ATTRIBUTE([People]; & ;[People]OB_Field;"locations[a].city";=;"paris")

The query returns “martin“ because it has a “locations“ element whose “kind“ is “home“ and  whose “city“ is “paris“. The query doesn’t return “smith“ because the value “home“ and “paris“ are not in the same array element.

If you want to only get records where matching arguments are in the same element, you need to use linked arguments. To link query arguments add a letter between the [ ] in the first path to link and repeat the same letter in all linked arguments.

For example: locations[a].city and locations[a].kind. To add different linked criteria in the same query, use another letter. You can download the following example database to see more examples.

Example Database

You can also refer to the 4D v16 R2 documentation for more details:

• QUERY BY ATTRIBUTE
• QUERY SELECTION BY ATTRIBUTE