51ExportRelationships.json structure

Available with Aviation Charting license.

The 51ExportRelationships.json file contains a series of queries represented as object parts for each primary table. The purpose of each query is to produce related data on a primary table. Related data is produced by joining the primary table with at least one associated table. The results of these queries are used by the 51ExportTemplate.xml file and the fields listed in each query can be referenced in the template. Review AIS Tables mapped to exported AIXM 5.1 features for a listing of all primary tables and related tables.

Caution:

It is recommended that you create backup copies of the outlined configuration files before editing them to avoid data loss.

Query object parts

The queries are grouped by primary table. Each query is represented as an object with fields, tables, where, and specialkeys sections. These sections are described in the table below.

Learn more about queries in 51ExportRelationships

Section	Description
fields	The fields section lists all the fields that will be selected in a query. The fields section is similar to the SELECT clause in an SQL query. Each field must contain a table prefix. Each table must also exist in the tables section. All fields in this section become available for reference in the 51ExportTemplate.xml file.
tables	The tables section lists all the tables that will be part of the query. Tables in this section will be joined together. The tables section is similar to the FROM clause in an SQL query. The tables section must contain at least two tables: the primary table and at least one associated table. An implicit join will be performed on the tables using the criteria in the where section. Each table in this section can be referenced in the fields section.
where	The where section joins the tables together by comparing the primary key from one table to the foreign key in another table. The where section is similar to the WHERE clause in SQL. More conditions can be added in the where section to limit the query results.
specialkeys	The specialkeys section is optional and allows the query to rename one or more fields. It is typically used to rename unique identifier fields: fields that are referenced in the duplicate attributes of the 51ExportTemplate.xml file. The main benefit of the specialkeys section is to prevent conflict between similarly named unique identifier fields and other queries in the same primary table. The specialkeys sections is similar to SQL aliases and uses the AS keyword. The specialkeys section is an object that contains mappings of field names to alias names. The alias name becomes input to the 51ExportTemplate. As a result, the original field name is not accessible to the 51ExportTemplate.

Example

The following example shows two query objects and demonstrates the use of the four sections: fields, tables, where, and specialkeys:

{
  "primarytable_a": [
    {
      "fields": [
        "primarytable_a.objectid",
        "notes.objectid",
        "notes.type_code",
        "notes.note_txt"
      ],
      "tables": [
        "primarytable_a", 
        "notes"
      ],
      "where": "primarytable_a.objectid = notes.reference_id"
    },
    {
      "fields": [
        "primarytable_a.objectid",
        "relatedtable.clientkey_id",
        "notes.objectid", 
        "notes.type_code",
        "notes.note_txt"
      ],
      "tables": [
        "primarytable_a", 
        "relatedtable",
        "notes"
      ],
      "where": "primarytable_a.gfid = relatedtable.related_id AND relatedtable.objectid = notes.reference_id",
      "specialkeys": {
        "notes.objectid": "relatedtable_notes_oid"
      }
    }
  ]
}

The first query joins the primary table to notes and makes notes.objectid available as a duplicate reference ID to the 51ExportTemplate.

The second query joins the primary table to the intermediate related table, and joins the related table to notes. The specialkeys section creates an alias for relatedtable_notes_oid to resolve conflicts with duplicated notes.objectid from the first query. This allows the 51ExportTemplate to write notes from the related table using related_notes_oid as the reference ID. Refer to duplicate attribute in the 51ExportTemplate for more information on the use of duplicate.