4.1 Queries

Queries are a powerful tool the Officer can use to filter SRs through various stages in the plat-form. The Officer can:

·        Filter the number of SRs displayed based on the Officer’s selected parameters.

·        Export the result of a query to JSON or CSV formats.

4.1.1. Create a query

The Officer can access the Query Builder through the 'Data Management’ menu at the top of the page, then click on the 'Queries' option (Data Management > Queries).

In this section, the Officer will be able to create queries, share them with other users or manage queries shared by other users with the Officer.

 ‘In BO’ column refers to the case if the query is included in a batch operation or not. The Officer can see the BOs that are using the query by hovering over the ‘edit properties’ option in the 3-dot menu (N/A for Safety Recommendations)

‘To Lobby’ column, with a filter for ‘yes’ or ‘no’. The query can be exported between UAT and PROD environments or between Authorities depending on whether the query to be exported has custom attributes or not. If it has custom attributes, it cannot be exported.

The Officer will have a library tree on the left panel, including all the libraries available to the Officer. Those are the folders that can have categories and subcategories. Find more information about Libraries in the section Query libraries.

To view the Queries inside a library, category or subcategory, the Officer has to click on the corresponding folder.  The right side of the window will display the list of queries available for that selected folder:

The Officer can click the 'New Query' button to create a new one in the selected Library or folder. A new window will be displayed with the Query Builder:

The Query Builder allows the Officer to build queries to search SRs with attributes or metadata fields that meet the given conditions.

The queries are executed against the safety recommendations indexed in ElasticSearch.

The Officer has to fill in a unique Query Name for each of the Officer’s queries and select a Library where to store the query. See Libraries section for more information.

Before executing, exporting or saving a query from the Query Builder, it is required to select over which reports the query will be executed on the ‘Count Number of Items’ section:

 

They will be activated as soon as the mandatory fields of the query are filled.

SRIS queries (based on SRIS Taxonomy)

·        Safety Recommendations: including ‘Archived’ ones (Draft versions are not included).

·        ECR: All safety recommendations shared to the ECR by mine and other Authorities (major versions only)

4.1.2. Query conditions – Attributes and metadata

Once in the Query Builder, the Officer has to select the query parameters to set each rule by clicking on ‘Select Attribute’ under the ‘Conditions’ section. 

The system will display a modal window where the Officer can choose between:

·        Attributes: Any of the attributes available for the Officer’s Authority Taxonomy:

·        Metadata: additional information found at the database level that can be relevant for the Officer to query by:

ü  e2id – E2 identifier of SRs

ü  Audit fields – they refer to the information of the specific version of the re-port:

o         au_creation_date: the date when the report has been created

o         au_creation_user: user who has created the report

o         au_modification_date: the last date the report was updated

o         au_modification_user: the last user that updated the report

ü  blocked – it is set to TRUE when the report is blocked to prevent the users from editing it

ü  last_shared_ecr – it determines the last time the SR was shared with the ECR

ü  responsible_entities_id – id of the responsible Entity (Authority in E2)

ü  status – the report status, as described in the section Management of the SRs

ü  Version – version number

4.1.3.  Query structure

The queries are made using at least one rule. A rule is a condition the search made by the query has to meet.

Queries can also include various rules and groups of rules. A query can include one or more existing queries (subqueries). Queries cannot be edited while included as a subquery of another query.

A rule contains a condition to be met concerning an attribute or metadata field. Layouts and operators vary depending on the attribute and metadata field type, i.e.:

If the Officer selects ‘Closure Date’ as an attribute, the system will display ‘Value’ as the unique possible selectable layout and the Select Operator filter:

This will generate a ‘Date’ field to be filled by the Officer.

Attributes

·        Alphanumeric and Text attributes or metadata fields can be searched in documents by a complete word using the layout ‘Value (Word)’ or by a string of characters using the layout ‘Text’. i.e.:

ü  Value (Word) as in ‘Aircraft’ (complete word)

ü  Text begins with ‘Airc’ (a string of characters)

Data Type	Layout	Operators
Alphanumeric, Text	Value (Word)	equal does not equal in not in is null is not null
	Text	in not in begins with does not begin with ends with does not end with is null is not null

·        Code attributes can be searched in documents using layouts:

ü  Value: selecting one or more values of the value list

ü  Level: when the value list has more than one level, selecting all the values of a branch (level) in the value list, starting from the selected value(s)

ü  Alias: when the value list has alias(es), selecting one of them

Data Type	Layout	Operators
Code	Value	equal does not equal in not in is null is not null
	Level * if the Value List has more than one level	equal does not equal in  not in is null is not null
	Alias * if the Value List has alias(es)	in  not in begins with does not begin with ends with does not end with

·        Code and Additional Text, Code or Alternative Text attributes can be searched in documents using layouts:

ü  Value: selecting one or more values of the value list

ü  Level: when the value list has more than one level, selecting all the values of a branch in the value list

ü  Alias: when the value list has alias(es), select one of them.

ü  Text: typing a string of characters

Data Type	Layout	Operators
Code and Additional Text, Code or Alternative Text	Value	equal does not equal in not in is null is not null
	Level * if the Value List has more than one level	equal does not equal in  not in is null is not null
	Alias * if the Value List has alias(es)	in  not in begins with does not begin with ends with does not end with
	Text Used to search in the Text part of the Attribute	in  not in begins with does not begin with ends with does not end with is null is not null

·        Date and DateTime attributes can be searched in documents using layouts:

ü  Value: when searching by date or range of dates

ü  Date of the Month (1-31): when searching by the day of the month

ü  Date of Week (1-7): when searching by the day of the week

ü  Month (1-12): when searching by the month

ü  Year: when searching by year

Data Type	Layout	Operators
Date, DateTime	Value	equal does not equal less than less or equal greater than greater or equal between not between
	Day of the Month (1-31)	equal does not equal less than less or equal greater than greater or equal between not between
	Day of Week (1-7)	equal does not equal less than less or equal greater than greater or equal between not between
	Month	equal does not equal less than less or equal greater than greater or equal between not between
	Year	equal does not equal less than less or equal greater than greater or equal between not between

·        Time attributes can be searched in documents using layouts:

ü  Value: when searching by hours and minutes

Data Type	Layout	Operators
Time	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Latitude and Longitude attributes can be searched in documents using layouts:

ü  Value: when searching by degrees, minutes and seconds or range of them

Data Type	Layout	Operators
Latitude, Longitude	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Decimal attributes can be searched in documents using layouts:

ü  Value: when searching by a decimal number

Data Type	Layout	Operators
Decimals	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Number attributes can be searched in documents using layouts:

ü  Value: when searching by an integer number

Data Type	Layout	Operators
Number	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        ECCAIRS Resource Locator attributes can be searched in documents using layouts:

ü  File Name: When searching by the Name of an attached field

ü  Description: When searching by the description given in the attached field

Data Type	Layout	Operators
ECCAIRS Resource Locator	File Name	equal does not equal in not in is null is not null
	Description	equal does not equal in not in is null is not null

·        ECCAIRS Embedded data attributes can be searched in documents using layouts:

ü  ECCAIRS number: when searching the former document's ECCAIRS number

Data Type	Layout	Operators
ECCAIRS Embedded Data	ECCAIRS number	in  not in begins with does not begin with ends with does not end with is null is not null

·        ECCAIRS Data link attributes can be searched in documents using layouts:

ü  ECCAIRS number: when searching the former document's ECCAIRS number

Data Type	Layout	Operators
ECCAIRS Data Link	ECCAIRS number	in  not in begins with does not begin with ends with does not end with is null is not null

 

Groups can include one or more rules and one or more groups combined with and/or clauses. The Officer can click the ‘Add Rule’ button to add more rules. For groups, on the ‘Group’ tab. If the Officer wants different sets of rules to be evaluated together, the Officer has to click on the 'Add group' button. The Officer can also add groups inside other groups.

Groups and rules can be rearranged, dragging and dropping them where needed.

For each group, at the top centre of the group, there are the OCCURRENCE/ENTITY alternative tabs; if conditions of the given attributes shall be met in any instance of the Entity or if the conditions shall be met in the same instance of the Entity. Thus, when specifying restrictions in a query, the restrictions can be evaluated across all the entities of a record at once OR can be evaluated by each Entity of a record one by one. By default, the restrictions of a query will be evaluated across all the entities of a record at once, with the OCCURRENCE option selected by default.

With the selector OCCURRENCE/ENTITY, the Officer can change that, and the restrictions will be evaluated Entity by Entity. There is a limitation in creating groups based on the restriction selected here. If a group is restricted to ENTITY, no more groups can be added inside that ENTITY group.

Example of search by occurrence and by Entity:

1.       OCCURRENCE: If we want to obtain the Safety Recommendations where the Manufacturer model (39) is equal to ‘BOEING > 787’ and the Operation Type (189) is equal to ‘Commercial Air Transport > Cargo’ independently if the conditions are met in different aircraft of a safety recommendation:

 

2.       ENTITY: If we want to obtain the Safety Recommendations where the Manufacturer model (39) is equal to ‘BOEING > 787’ and the Operation Type (189) is equal to ‘Commercial Air Transport > Cargo’ when the conditions are met in the same aircraft.

The Officer can set rules with and/or clauses and combine them as needed with groups by clicking on the ‘Group tab’ and selecting and/or clauses, i.e.:

Subqueries

The Officer can add a subquery to the query through the ‘Query’ tab. The aim is to add an already existing query available for the Officer to the current query. When the Officer clicks on the ‘Query’ tab, the system will display the subquery section:

The Officer can click on ‘Select Query’, and the system will display a window to select the subquery:

The Officer must select a query from the available libraries and click ‘Add’. The system will add the subquery to the conditions of the current query. A query included as a subquery of another query cannot be edited.

4.1.4. Query action buttons

Once the query is completed, the Officer can click on:

·        Cancel button: the system will return to the Queries page.

·        Execute button: the system will display the list of results according to the conditions set by the query.

·        Save button: The system will save the query in the list of queries of the selected Library and display the table of queries with the new query on the queries page.

·        Execute button: the system will display the table of the specific reports based on the query's conditions. To re-execute the queries, the system provides the data menu that works as explained in the Data Menu – Re-execution of Queries section.

·        Export button: this button only works when the Officer selects a type of report (SR or ECR) with results for the current query. The Officer has to click on ‘Export’, and the system will display a modal window where the Officer can select the format (JSON or CSV):

·        Export in JSON: The window displays a Views tab. When the Officer clicks on it, the system displays a ‘Views’ modal window. The Officer must select one of the available views and click ‘Export’.

Once the process is finished, an email will be sent to the Officer with a zip file with the query results in the selected format.

·        Export in CSV: The Officer clicks on the ‘CSV’ tab, and the system displays a modal window where the Officer has to select between ‘Views’ and ‘Attributes’:

·        If the Officer clicks ‘Views’, the system will display a ‘Views’ modal window. The Of-ficer must select one of the available views and click ‘Export’. Once the process is finished, an email will be sent to the Officer with a zip file with the query results in CSV format.

·        If the Officer selects ‘Attributes’, the system will display an Attributes List drop-down menu and a new Attributes List button:

 

The drop-down menu will provide the Attribute Lists created through ‘My Account’. They are lists of attributes that the Officer is interested in and which they create through ‘My Account’. See Export by Attribute sectionfor details.

If the attributes needed do not match any available Attribute Lists, the Officer can click on ‘New Attribute List’, and the system will display a modal window to create it: 

The Officer has to fill in the mandatory ‘Name’ field and deactivate the ‘Metadata fields’ if needed.

 

Below, the system displays the Taxonomy tree with all the SRIS entities and attributes. The Officer can filter or navigate the tree to select the corresponding attributes. Once selected, they appear on the right-side panel. Attributes under the same Entity can be reordered by dragging and dropping them. Then, the Officer has to click on the Save button:

The Officer will select the created Attribute List on the drop-down menu and will click on the ‘Export’ button:

Once the process is completed, the Officer will receive an email with the CSV file, including the results of the executed query.

4.1.5. Management of queries – table of queries

The Queries menu displays the table of queries of the corresponding Library, category or sub-category selected in the library tree:

 

4.1.5.1. Editing a query

The Officer has to double-click on the specific query of the queries list or click on the ‘Edit’ option of the query’s 3-dot menu, and the system will display the editing page of the query:

The Officer has to introduce the needed update and click on ‘Save’, ‘Export’ or ‘Execute’, as required. See the Query action buttons section for further details.

This page also provides:

·        Elastic Query Viewer button: it accesses the ElasticSearch Query Viewer window as follows:

·        Query viewer button: it opens the query viewer window as follows

1.1.5.2             Duplicating a query

The Officer can make a copy of an existing query and modify it.

The Officer has to click on the copy icon of the 3-dot menu of the specific query of the list of queries, and the system will display the Copy query window:

It is like the editing page above but with the mandatory Library field empty. The Officer has to fill out the library field with a library different from the original one, and then the Execute and Export button will be activated. The Save button is not available unless a Library is selected. The Officer has to introduce the needed update and click on ‘Save’, ‘Export’ or ‘Execute’, as required. See the Query action buttons section for further details.

1.1.5.3             Sharing a query

Officers of the same Authority can exchange queries with each other, which will result in a notification to the receiving officers.

The Officer has to click on the Sharing option of the 3-dot menu of the specific query of the list of queries, and the system will display the Share query window:

The Officer has to select ‘Share to User’, and then the system will display the ‘Users’ tab below.

 The Officer has to click on ‘Users’, and the system will display the Available Users section:

The Officer has to check the specific Users, click the ‘ADD’ button, then click the ‘Save’ button. The system will save the updates of the query in the database and return to the list of queries that will have added a Shared icon to the specific query:

The Officer then can click on the ‘Edit share’ option that will be added to the 3-dot menu of that query and add more users or remove some of them.

1.1.5.4             Deleting a query

The Officer has to click on the ‘Delete’ option of the specific query 3-dot menu of the list of queries, and the system will delete it and update the table of queries accordingly. A query can-not be deleted if used in Batch Operations or Query-based Notifications. See the Query-based notifications section for further details.