Search by linking array attribute query arguments

Automatically translated from English

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 whosekind ishome 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:

Vanessa Talbot

• Product Owner •

Vanessa Talbot joined 4D Program team in June, 2014. As a Product Owner, she is in charge of writing the user stories then translating it to functional specifications. Her role is also to make sure that the feature implementation delivered is meeting the customer need.

Since her arrival, she has worked to define key features in 4D. She has worked on most of preemptive multi-threading new features and also on a very complex subject: the new architecture for engined application. Vanessa has a degree from Telecom Saint-Etienne. She began her career at the Criminal Research Institute as a developer for the audiovisual department. She has also worked in media and medical fields as expert in technical support, production as well as documenting new features.