QUERY BY ATTRIBUTE

QUERY BY ATTRIBUTE looks for records matching the query string defined using the objectField, attributePath, queryOp and value parameters, and returns a selection of records for aTable.

Note: For more information on Object fields (new in 4D v15), please refer to the section of the Design Reference manual.

QUERY BY ATTRIBUTE changes the current selection of aTable for the current process and makes the first record of the new selection the current record. If the aTable parameter is omitted, the command applies to the default table. If no default table has been set, an error occurs.

The optional conjOp parameter is used to join QUERY BY ATTRIBUTE calls when defining multiple queries. The conjunction operators available are the same as the ones for the QUERY command:

The conjOp parameter is not used for the first QUERY BY ATTRIBUTE call of a multiple query, or if the query is a simple query. If you omit it within a multiple query, the AND (&) operator is used by default.

In objectField, pass the Object field whose attribute(s) you want to query. If it belongs to a One table related to aTable with an automatic or manual relation, the objectField may belong to another table.
QUERY BY ATTRIBUTE supports 4D Write Pro custom attributes when documents are stored in Object fields. For more information about this point, please refer to the Storing 4D Write Pro documents in 4D Object fields section.

In attributePath, pass the path of the attribute whose values you want to compare for each record, for example "children.girls.age". If you pass a single name, for example "place", you designate the corresponding attribute found at the first level of the object field.
If an attribute "x" is an array, QUERY BY ATTRIBUTE will search records which contain an attribute "x" in which at least one element matches the criteria. To search in array attributes, it is necessary to indicate to the QUERY BY ATTRIBUTE command that attribute "x" is an array by appending "[]" to its name in attributePath (see example 3).

Notes:

• Keep in mind that attribute names are case-sensitive: you can have different "MyAtt" and "myAtt" attribute names in the same record.
• Attribute names are trimmed to eliminate extra spaces. For example, " my first attribute .my second attribute " is interpreted as "my first attribute.my second attribute". 

The queryOp parameter is the comparison operator that is applied between objectField and value. You can pass one of the symbols shown here:

Note: It is also possible to specify the comparison operator as an text expression instead of a symbol. See the QUERY command description for more information.

value is the data against which attributePath will be compared. The value can be any expression that evaluates to the same data type as attributePath. The value is evaluated once, at the beginning of the query. The value is not evaluated for each record. To query for a string contained within a string (a "contains" query), use the wildcard symbol (@) in value to isolate the string to be searched for as shown in this example: "@Smith@". Note that in this case, the search only partially benefits from the index (compactness of data storage).

Here is the structure of a query by attribute:

Note: An implicit criteria for all operators (except #) is that the Object field contains an attribute. However, for the # operator, it can be undefined (see below).

Queries by attribute using the "#" operator can have different results depending on whether or not the Map NULL values to blank values property is checked for the object field:

• Map NULL values to blank values property checked (default option, recommended in most cases).
  In this case, the "#" operator should be seen as selecting records where "no attribute" of the field contains the value searched for. In this context, 4D considers in a similar manner:
  
  • fields where the value of the attribute is different from the value searched for,
  • fields where the attribute is not present (or contains a Null value).

  For example, the following query returns records for people who have a dog whose name is not Rex, as well as records for people who do not have a dog, or who have a dog with no name:
  

   QUERY BY ATTRIBUTE([People];[People]Animals;"dog.name";#;"Rex")
  
  Another example: this query will return all records for which [Table]ObjectField contains an object which contains an attribute1 attribute which is itself an object containing an attribute2 attribute whose value is not value, as well as records where the object field does not contain attribute1 or attribute2):
  
   QUERY BY ATTRIBUTE([Table];[Table]ObjectField;"attribute1.attribute2";#;value)

  This principle also applies to array attributes. For example:
  

   QUERY BY ATTRIBUTE([People];[People]OB_Field;"locations[].city";#;"paris")

  This query will return records for people who do not have any address in Paris.

  To specifically obtain records where the attribute is undefined, you can use an empty object (see example 2). Note however that searching for NULL values in array elements is not supported.

• Map NULL values to blank values property unchecked ("SQL" mode).
  In this case, undefined attributes (attributes not present in the field or whose value is Null) are not considered as equivalent to blank values by default. As a result, queries of the type "attribute A is different from attribute B" will not return records where attribute A is undefined.
  To use the same example as above, when the Map NULL values to blank values option is not checked for the [People]Animals field, the following query will only return records for people who have a dog whose "name" attribute does not contain "Rex". Records for people who do not have a dog, or who have a dog with no name will not be returned in this case.
  
   QUERY BY ATTRIBUTE([People];[People]Animals;"dog.name";#;"Rex")
  
  This operation, closer to the SQL logic, is reserved for specific needs. 

Here are the rules for building multiple queries by attribute:

• The first query argument must not contain a conjunction.
• Each successive query argument can begin with a conjunction. If you omit it, the AND (&) operator is used by default.
• All queries, except the final query, must use the * parameter.
• QUERY BY ATTRIBUTE can be mixed with QUERY commands (see example).
• To perform the query, do not specify the * parameter in the last QUERY BY ATTRIBUTE command. Alternatively, you can execute the QUERY command without any parameters other than the table.

Note: Each table maintains its own currently-built query. This means that you can create multiple queries simultaneously, one for each table.

No matter which way a query has been defined:

• If the actual query operation is going to take some time to be performed, 4D automatically displays a message containing a progress meter. These messages can be turned on and off by using the MESSAGES ON and MESSAGES OFF commands. If a progress meter is displayed, the user can click on the Stop button to interrupt the query. If the query is completed, OK is set to 1. Otherwise, if the query is interrupted, OK is set to 0 (zero).
• If any indexed object fields are specified, the query is optimized every time that it is possible (indexed fields are searched first) resulting in a query that takes the least amount of time possible.

Dates are stored in objects according to database settings; by default, the time zone is taken into account (see the JSON use local time selector in the SET DATABASE PARAMETER command).

!1973-05-22! -> "1973-05-21T23:00:00.000Z"

This setting is also taken into account during queries, so you do not have to worry about it if you always use your database at the same place and if settings are the same on all machines that access the data. In this case, the following query will correctly return records whose Birthday attribute equals !1973-05-22! (saved as "1973-05-21T23:00:00.00Z"):

If you do not want to use the GMT settings, you can modify these settings using the following instruction:

Keep in mind that the scope of this setting is the process only. If you execute this instruction, then October 1st, 1965 will be stored "1965-10-01T00:00:00.000Z" but you will need to set the same parameter before launching your queries:

You can use the virtual "length" property with this command. This property is available automatically for all array type attributes and returns the size of the array, i.e. the number of elements it contains. It can be used in the context of executing the QUERY BY ATTRIBUTE command (see example 4).

In this example, the "age" attribute is either a string or an integer and we want to find people whose age is between 20 and 29. The first two lines query the attribute as an integer (>=20 and < 30) and the last ones query the field as a string (starts with "2" but is different from "2".)

The QUERY BY ATTRIBUTE command can be used to find records where certain attributes are defined (or are not defined). To do this, you have to use an empty object.

Note: This specific syntax is not supported with array type attributes. Searching for NULL values in array elements will give invalid results.

You want to search a field containing array attributes. With the following two records:

Record1:
[People]name: "martin"
[People]OB_Field:
    "locations" : [ {
                "kind":"office",
                "city":"paris"
            } ]

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

... QUERY BY ATTRIBUTE will find people with a location in "paris" using this statement:

Note: If you defined several criteria on the same array attribute, the matched criteria will not necessarily apply to the same array element. In the following example, the query returns "smith" because it has a "locations" element whose "kind" is "home" and a "locations" element whose "city" is "paris", even if it's not the same element:

This example illustrates the use of the virtual "length" property. Your database has a [Customer]full_Data object field with the following data:

You want to get the records for any customers who have two or more children. To do this, you can write:

If the query is carried out correctly, the OK system variable is set to 1.
The OK variable is set to 0 if:

• the user clicks on the Cancel/Stop button,
• in 'query and lock' mode (see the SET QUERY AND LOCK command), the query has found at least one locked record. In this case as well, the LockedSet system set is updated.