10 Array fields 📋
Array fields, like CCTV survey details and Manhole Survey pipes in InfoAsset Manager, typically show up in grids on property sheets. They contain multiple rows, each representing different details like CCTV survey or manhole survey pipes.
Each row possesses various named 'fields', similar to how each object has distinct fields.
Flags for an object can also be viewed as an array, with the array containing two fields: value and name (where name is read-only 🛑).
Through SQL, you can access these array fields in different ways:
10.1 Detecting if there's data in the array 🕵️♂️ You can check for data in the array using the function ANY with the name of the array followed by '.' - for instance, ANY(details.). This returns true if records exist in the array, and false if not.
Details about the ANY function will be discussed later in this section.
10.2 Counting the rows in the array 🧮 Using the COUNT function with the name of the array (e.g., COUNT(details.*)), you can tally the number of rows in it.
For instance, to pinpoint CCTV survey records exceeding 10 detail records, you might use COUNT(details.*)>10.
To enumerate the number of survey flags, S1, in the network: SELECT node_id, COUNT(flags.value='S1').
The nature of the COUNT function will be elaborated on later.
10.3 Aggregate functions 🔄 Aggregate functions evaluate an expression for each row in an array field, then perform an action with all the results. Here are some aggregate functions explained:
ANY: Returns true if the expression is true for any row of the array field. For instance, ANY(details.code='JDS') checks if any row in the 'details' array has the code 'JDS'.
ALL: This function checks if the expression is true for every row in the array.
COUNT: Returns the count of records in the array where the expression is true. For example, COUNT(details.code=’ST’) counts records where the 'details' code is 'ST'.
MAX: Finds the maximum value for the expression among all the records.
MIN: Identifies the minimum value.
AVG: Calculates the average value.
FIRST & LAST: These return the value for the first and last records respectively.
10.4 Using ‘bare’ array field values in expressions 💡 You can utilize values from array fields both within and outside aggregate functions. For instance, details.code='GP' selects all CCTV surveys with at least one record labeled 'GP'.
It's vital to understand your query's intent, especially with tests beyond equality.
10.5 Assigning ‘bare’ array field values 📌 You can assign values to array fields using them in assignment clauses outside aggregate functions.
For example, without a WHERE sub-clause, SET pipes_in.width = 123 assigns the width for every ‘pipe in’ record in every manhole survey to 123.
10.6 Interaction between ‘bare’ array fields and aggregate functions 🔄 When you use array field values outside aggregate functions in a WHERE sub-clause, and within aggregate functions in the SET sub-clause, the aggregate functions in the SET will be evaluated only for records true to the WHERE sub-clause.
For example, SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES' assigns user_number_1 the count of 'details' records for the CCTV survey with the code 'DES'.
This section provides a comprehensive overview of how to work with array fields using SQL, ensuring you can effectively manage and query your data. 📊🔍📈
