DMAP GraphQL API

Overview

The DMAP API is a GraphQL-like API that will allow users to query all the tables in the Envirofacts database. The API is located at https://data.epa.gov/dmapservice/query.

Quickstart

The basic structure of a query consists of a schema, table, and fields. All examples below are for tables and schemas which do not currently exist. They are simplified examples to clearly indicate functionality.

Example

query quickstart {
    icis__icis_activity_report (limit: 10) {
        activity_name
        activity_status_desc
        actual_begin_date_fy
    }
}

This query will select the fields activity_name, activity_status_desc, and actual_begin_date_fy from the icis_activity_report table in the ICIS schema. The results will be limited to 10 records.

Performing a Query

API queries can be performed with any GraphQL client and can also be accessed through user interfaces like Postman or ThunderClient.

Postman Example

To perform an API query in Postman, click the New button and select an HTTP request. Then select make the request a POST request and set the address to https://data.epa.gov/dmapservice/query. Go to the "Body" tab and select "GraphQL" to enter the query. In the Query field, enter the GraphQL text and click the "Send" button to view the response.

Schema and Table

A schema and table must be supplied when querying data. The format is schema__table (note the double underscore). For example, when querying the ref_city table in the icis schema, the format would be icis__ref_city.

Table Aliases

A table can be renamed in the resulting data set with the use of the alias function.

query aliasQuery {
    icis__ref_facility_type(alias: fac_types) {
        facility_type_desc
        facility_type_code
    }
}

In the example above, the ref_facility_type data will be under the "fac_types" element in the resulting data set. The original schema and table name are not referenced in the results. The table alias is not used when exporting results in the graphql format.

Selecting Fields

At least one field must be supplied (if not querying an aggregate column, see below) in a query. Fields are specified as a nested list within the table element. There is no delimiter between fields. In the query:

query fieldsQuery {
    icis__ref_naics {
        naics_code
        naics_desc
        status_flag
    }
}

The 3 fields naics_code, naics_desc, and status_flag will be returned in the result set.

All fields

If you want to return all the fields in a table, use the special field __all_columns__. You do not need to include any other columns in the table.

query fieldsQuery {
    icis__ref_naics {
        __all_columns__
    }
}

Field Aliases

To rename a column’s name for the purposes of a query, use the alias function with the value you want the column name's output to be in the result set.

query fieldsQuery {
    icis__ref_naics {
        naics_code
        naics_desc (alias: description)
        status_flag
    }
}

In the example above, the naics_desc column will be returned as description in the results.

Field Casts

To cast a selected field to a different type when it is returned, add the cast attribute to the field in the query

query fieldCast {
    icis__icis_activity_report (limit: 20) {
        activity_name
        activity_status_desc
        fiscal_year (cast: "text")
    }
}

In this example, the fiscal_year field will be returned as a text type instead of an integer.

Valid Cast Types

• date
• decimal
• float
• integer
• number
• text

Aggregations

To perform an aggregation method on a table (e.g. count, min, max), an aggregate clause needs to be supplied. The table can still have any filter and join applied to it. Below is an example of getting the count of the icis_activity_report table.

query aggregateCount {
    icis__icis_activity_report {
        aggregate {
            count
        }
    }
}

Aggregation parameters

For aggregation methods additional parameters can be supplied. These parameters can include an alias value. The count method can also include a distinct parameter and a fields parameter to adjust the fields used to determine distinctness which will affect the count's value.

query aggregateCount {
    icis__icis_activity_report {
        aggregate {
            count (distinct: true, fields: ["actual_end_date_fy", "activity_name"], alias: "field_count")
        }
    }
}

The query above will perform a count, the results will be returned with an alias of "field_count". The count method will be performed with check for distinct "actual_end_date_fy" and "activity_name" fields.

Available aggregation methods

• count
  • Purpose: Returns the total count of records.
  • Format: count
• max
  • Purpose: Get the maximum value of a column.
  • Format: __max__column_name
• min
  • Purpose: Get the minimum value of a column.
  • Format: __min__column_name

Distinct

To add a distinct clause in the query, add an argument to the primary table that specifies distinct: true.

query distinctQuery {
    icis__ref_naics (distinct: true) {
        naics_code
        naics_desc (alias: description)
        status_flag
    }
}

User Functions

To select an user function in the results, add a parameters parameter that contains an array of the data to pass to the function. If the parameters are just text or numbers, the parameters can be specified as just a list of values, e.g. ["Delaware", "Maryland", "New Jersey"]. If the parameters are referring to a database column, use the format [{type: "column", value: "column_name"}].

query userFunctionQuery {
    tri__tri_reporting_form (limit: 10) {
        tri_facility_id
        reporting_year
        tri__getchemname (parameters: [{type: "column", value: "doc_ctrl_num"}])
    }
}

Standard Functions

To use a standard function in the query, add fn__ before the function name. Currently only coalesce, coalesceText and count are supported. Pass any parameters in the parameters argument.

fn__add

Adds the values of any columns and numbers passed in as parameters.

fn__avg

Returns the average of the values of the chosen column. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__coalesce

Returns the value in the database field supplied as the second parameter if the database field in the first parameter has a NULL value.

fn__coalesceText

Similar to fn__coalesce except it returns the text supplied as the second parameter if the database field in the first parameter has a NULL value.

fn__count

Return the count of the database field supplied in the first parameter. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__dateadd

Adds or subtracts the interval provided to the date supplied in the first parameter. The second parameter is the date part to be added or subtracted. Valid values for the date part are: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium. The third parameter is the interval to be applied.

fn__datetrunc

Truncates a date database field supplied in the first parameter. The second parameter is the precision of the date field to be retrieved. Valid values for precision are: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium. The third parameter is optional and is the time zone (e.g.& America/New_York).

fn__divide

Divides the values of any columns and numbers passed in as parameters.

fn__extract (fn__datepart is an alias)

Returns the specified part from a date database field supplied in the first parameter. The second parameter is the part of the date field to be retrieved. Valid values for the date part are: microseconds, milliseconds, second, minute, hour, day, dow (day of week), day (day of year), week, month, quarter, year, decade, century, millennium.

fn__lpad

Pads the database field supplied in the first parameter. The second parameter is the length of the total number of characters to be returned. The third parameter is the character used to pad the database field, by default it is an empty space (" " ).

fn__ltrim

Trims from the left beginning of the database field supplied in the first parameter. The trimming will occur on the leading characters. The second parameter is optional (it defaults to a blank space, " ") and is the characters that are to be removed.

fn__lower

Returns the lowercase version of the database field supplied in the first parameter.

fn__max

An aggregate function and will return the highest value based on the criteria supplied for the database field supplied in the first parameter. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__min

An aggregate function and will return the lowest value based on the criteria supplied for the database field supplied in the first parameter. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__multiply

Multiplies the values of any columns and numbers passed in as parameters.

fn__replace

Returns a modified version of the database field supplied in the first parameter. The second parameter is the pattern to search for in the field's data. The third parameter is optional (it defaults to an empty string) and is the characters that will replace the pattern.

fn__round

Rounds the value of the supplied database field in the first parameter. The second parameter is optional and is the number of decimal places after the field is rounded (it defaults to 0).

fn__rtrim

Trims the right ending of the database field supplied in the first parameter. The trimming will occur on the trailing characters. The second parameter is optional (it defaults to a blank space, " ") and is the characters that are to be removed.

fn__stddev

Calculates the standard deviation of the values of the chosen column. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__substring

Takes a substring of the database field supplied in the first parameter. The second parameter is the index to start on (1 is the start of the field). The third parameter sets how many characters to return.

fn__subtract

Subtracts the values of any columns and numbers passed in as parameters.

fn__stringagg

Aggregates the values for the column passed in as the first parameter. The optional second parameter is the separator (defaults to " "). The optional third parameter is the field to order by. The optional fourth parameter is the order to order by ("ASC" or "DESC").

fn__sum

Sums the values of the chosen column. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

fn__trim

Trims the database field supplied in the first parameter. The trimming will occur on the leading and trailing characters. The second parameter is optional (it defaults to a blank space, " ") and is the characters that are to be removed.

fn__upper

Returns the uppercase version of the database field supplied in the first parameter.

fn__variance

Calculates the variance of the values of the chosen column. If this is selected alongside other fields, it needs to be used in conjunction with a group by clause.

Function Example

query coalesceField {
    icis__icis_activity_report (limit: 10) {
        activity_status_desc
        fn__coalesce(parameters: ["activity_name", "activity_type_code"], alias: "activity_name")
    }
}

The above request will query the icis_activity_report table and get the activity_status_desc field along with the activity_name field. Since the activity_name column is queried using the coalesce function, if activity_name has an empty value, the activity_type_code field will be used in its place.

Aggregation Functions (Avg, Sum)

The fn__avg and fn__sum functions returns the average or sum, respectively of the values of the chosen column. As they are an aggregation function, if there are any other columns being selected, they need to be in a groupBy clause.

query sumField {
    icis__icis_activity_report (limit: 10, groupBy: [activity_name]) {
        activity_name
        fn__sum(parameters: ["total_hours"], alias: "total_hours")
    }
}

The above request will query the icis_activity_report table to get the sum of the total hours according to the activity_name field. Note that the activity_name field was included in a groupBy expression which allows the total_hours sum to be based on the activity_name field.

Selecting from a Function

To select results from a function, query the function as if it was a table and specify the parameters that the function requires.

query functionSelect {
    icis__air_compliance_monitoring_info (parameters: ["MI00000000000B4127"]) {
        __all_columns__
    }
}

Case Statements

To enable if/else functionality when selecting fields, you can use a case statement in the query.

query case {
    icis__icis_activity_report (limit: 10) {
        activity_name
        activity_type_code
        state_code (case: {if: {equals: "CA", then: "California"}, elseif: {equals: "TX", then: "Texas"}, else: "Other State"})
    }
}

In the query above, when the results are returned, if the state_code field is "CA", then "California" will be returned in the record. Likewise if the state_code field is "TX", it will return "Texas". If the state_code field is any other value, "Other State" will be returned.

Filtering

Any table can have filters applied to it. When creating the query, put a parentheses after the table with the filter criteria using the where operator.

query basicFilter {
    icis__icis_activity_report (limit: 10) {
        activity_name
        activity_type_code
        state_code (case: {if: {equals: "CA", then: "California"}, elseif: {equals: "TX", then: "Texas"}, else: "Other State"})
    }
}

In this query, if the state code is "CA", "California" will be returned, if the state code is "TX", "Texas" will be returned, otherwise "Other State" will be returned..

Multiple filters can be applied to a table. By default, multiple filters are applied using and logic.

query multipleFilters {
    icis__icis_activity_report (limit: 10, where: {state_code: {in: ["CT", "RI"]}, activity_type_code: {equals: "INS"}}) {
        activity_name
        activity_type_code
        state_code
    }
}

Here activity reports will be returned where the state_code is either "CT" or "RI" and the activity_type_code equals "INS".

Multiple filter conditions can also be applied with “OR” logic.

query orFilter {
    icis__icis_activity_report (limit: 10, where: {air_lead_agency_type_desc: {equals: "U.S. EPA"}, or: {air_lcon_code: {equals: "SJV"}}}) {
        activity_name
        activity_type_code
        air_lead_agency_type_desc
        air_lcon_code
    }
}

This query will return all records where the air_lead_agency_type_desc equals "U.S. EPA" or the air_lcon_code equals "SJV".

Filter conditions can also be nested (the equivalent of parentheses in order of operators).

query nestedFilter {
    icis__icis_facility_interest (limit: 10, where: {state_code: {equals: "CT"}, or: {city: {equals: "Providence"}, and: {location_address: {like: "%Main%"}}}}) {
        facility_name
        location_address
        city
        state_code
    }
}

This query will get up to 10 records where the state code equals "CT" or they live in the city of "Providence" and their street address contains "Main".

To provide more explicit nested expressions, use the parentheses operator.

query nestedFilter {
    icis__icis_facility_interest (limit: 10, where: {state_code: {equals: "CT"}, or: {parentheses: {city: {equals: "Providence"}, and: {location_address: {like: "%Main%"}}}}}) {
        facility_name
        location_address
        city
        state_code
    }
}

This query will behave the same as the previous, the only difference is that the parentheses clause is used to make the nesting more explicit.

Available Filter Operators

When working with string values, most operators are case-insensitive. If applicable, there will be a case-sensitive version available in the format {operator}Sensitive.

beginsWith

• Purpose: Finds values that will start with the supplied value.
• Example: location_address: {beginsWith: "100"}
• Expected Result: All records with addresses that start with 100 will be returned.
• beginsWithSensitive is available for case-sensitive queries.

between

• Purpose: Finds values that are between two values, inclusive of the lower and upper values.
• Example: total: {between: {lower: 20, upper: 30}}
• Expected Result: All records which have a total greater than or equal to 20 and less than or equal to 30 will be returned.
• betweenSensitive is available for case-sensitive queries.

contains

• Purpose: Finds records that have the supplied value anywhere in the field.
• Example: location_address: {contains: "Main"}
• Expected Result: All records that have an address that contains "Main" will be returned.
• containsSensitive is available for case-sensitive queries.

endsWith

• Purpose: Finds values that will end with the supplied value.
• Example: location_address: {endsWith: "St."}
• Expected Result: All records with an address that end with St. will be returned.
• endsWithSensitive is available for case-sensitive queries.

equals

• Purpose: Finds values that will equal the supplied value.
• Example: state_code: {equals: "CA"}
• Expected Result: All records in California will be returned.
• equalsSensitive is available for case-sensitive queries.

excludes

• Purpose: Finds values that will not be like the supplied value anywhere in the field (the opposite of contains).
• Example: location_address: {excludes: "Main"}
• Expected Result: All records where the address does not include Main will be returned.
• excludesSensitive is available for case-sensitive queries.

greaterThan

• Purpose: Finds values that are greater than the supplied value.
• Example: total: {greaterThan: 30}
• Expected Result: All records with a total of 31 or higher will be returned.
• greaterThanSensitive is available for case-sensitive queries.

greaterThanEqual

• Purpose: Finds values that are greater than or equal to the supplied value.
• Example: total: {greaterThanEqual: 30}
• Expected Result: All records with a total of 30 or higher will be returned.
• greaterThanEqualSensitive is available for case-sensitive queries.

in

• Purpose: Finds values that are in the supplied list.
• Example: state_code: {in: ["MS", "AL", "LA"]}
• Expected Result: All records in the states of Mississippi, Alabama, and Lousiana will be returned.
• inSensitive is available for case-sensitive queries.

inSubQuery

• Purpose: Finds values that are in a subquery in the query.
• Example: state_code: {inSubQuery: "state_query"}}
• Expected Result: All users whose state is supplied in the subquery aliased as "state_query" will be returned.
• inSensitive is available for case-sensitive queries.

lessThan

• Purpose: Finds values that are less than the supplied value.
• Example: total: {lessThan: 30}
• Expected Result: All records with a total of 29 or lower will be returned.
• lessThanSensitive is available for case-sensitive queries.

lessThanEqual

• Purpose: Finds values that are less than or equal to the supplied value.
• Example: total: {lessThanEqual: 30}
• Expected Result: All records with a total of 30 or lower will be returned.
• lessThanEqualSensitive is available for case-sensitive queries.

like

• Purpose: Finds values that are similar to the supplied value. Uses the % character as a wildcard.
• Example: location_address: {like: "%Main St."}
• Expected Result: All records with any address that begins with Main St. will be returned.
• likeSensitive is available for case-sensitive queries.

notContains

• Purpose: Finds records that do not have the supplied value anywhere in the field.
• Example: location_address: {notContains: "Main"}
• Expected Result: All records that have an address that do not contain "Main" will be returned.
• notContainsSensitive is available for case-sensitive queries.

notBeginsWith

• Purpose: Finds values that will not start with the supplied value.
• Example: location_address: {notBeginsWith: "100"}
• Expected Result: All records with addresses that do not start with 100 will be returned.
• notBeginsWithSensitive is available for case-sensitive queries.

notEndsWith

• Purpose: Finds values that will not end with the supplied value.
• Example: location_address: {notEndsWith: "St."}
• Expected Result: All records with addresses that do not end with St. will be returned.
• notEndsWithSensitive is available for case-sensitive queries.

notEquals

• Purpose: Finds values that are not equal to the supplied value.
• Example: state_code: {notEquals: "FL"}
• Expected Result: All records where the state does not equal Florida will be returned.
• notEqualsSensitive is available for case-sensitive queries.

notIn

• Purpose: Finds values that are not in the supplied list.
• Example: state_code: {notIn: ["TX", "OK", "KS"]}
• Expected Result: All records where the state is not Texas, Oklahoma, and Kansas will be returned.
• notInSensitive is available for case-sensitive queries.

notLike

• Purpose: Finds values that are not similar to the supplied value. Uses the % character as a wildcard.
• Example: location_address: {notLike: "%Main St."}
• Expected Result: All records with users with an any address that do not begin with Main St. will be returned.
• likeSensitive is available for case-sensitive queries.

null

• Purpose: Finds values that are either null or not null.
• Example: state_code: {null: true}
• Expected Result: All records that do not have a value for state_code will be returned.
• Example: state_abbr: {null: false}
• Expected Result: All records that have a value for state will be returned.

regex

• Purpose: Finds values that match the supplied regular expression.
• Example: location_address: {regex: "(main){1,3}"}
• Expected Result: All records that have an address that contains main at least once and at most three times.
• regexSensitive is available for case-sensitive queries.

Casting fields in filters

When needing to cast a field to a different type to work with a filter, use the cast attribute in the filter.

query castFilter {
    icis__icis_activity_report (limit: 10, where: {planned_begin_date_fy: {greaterThan: 2010, cast: "integer"}}) {
        activity_name
        activity_type_code
        planned_begin_date_fy
    }
}

In the example above, the results will be filtered by the planned_begin_date_fy. Since the column is a text format, it is cast to an integer so the greaterThan operator can be used.

Valid Field Cast Types

• date
• decimal
• float
• integer
• number
• text

Using functions in a filter

You can use the lpad, replace, and trim (along with ltrim and rtrim) functions in the filter. To do so, add the function as an attribute for the field in the where clause.

lpad

Left-pads a string with a specified character. Example: (where: {total: {like: "%1", lpad: [3, "0"}}). The first parameter is the length, the second is the character to pad with, the default pad character is a single space (" ").

replace

Replaces the pattern in the database field values with the replacement text. Example: (where: {email: {like: "%.gov%", replace: ["_","."]}}). The first parameter is the pattern to search for, the second parameter is the text to replace the pattern with, the default replacement text is an empty string.

trim

Trim the string. Example: (where: {age: {like: "%1", trim: "123"}}). The parameter is the characters to trim, the default trim is a single space (" ").

ltrim and rtrim

Trim the left or right of the string, similar to trim.

Dates

You can filter by dates and datetimes for fields that are of a date data type. For dates, the value must be in the format YYYY-MM-DD. For datetimes, the value must be in the format YYYY-MM-DD HH:MM:SS. Datetimes use the 24 hour format.

query castFilter {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2021-01-01"}}) {
        activity_name
        activity_type_code
        created_date
    }
}

In the example above, the results will be filtered to get the records that were created after January 1, 2021.

query castFilter {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2023-08-03 23:00:00"}}) {
        activity_name
        activity_type_code
        created_date
    }
}

In the example above, the results will be filtered to get the records that were created after August 3, 2023 at 11:00 PM.

Joins

Tables can be joined to each other in queries. A joined table can still have filters, orderBy, and groupBy clauses applied to it.

Tables can be joined using the following operators:

• equals
• equalsInsensitive
• notEquals
• notEqualsInsensitive
• greaterThan
• greaterThanInsensitive
• greaterThanEqual
• greaterThanEqualInsensitive
• lessThan
• lessThanInsensitive
• lessThanEqual
• lessThanEqualInsensitive

query join {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2024-01-01"}}) {
        activity_name
        activity_type_code
        created_date
        icis__ref_activity_type (inner_join: {icis__icis_activity_report__activity_type_code: {equals: activity_type_code}}) {
            activity_type_desc
        }
    }
}

This query will be run against the icis_activity_report and ref_activity_type tables the icis schema. An inner join will be performed on the icis_activity_report table's activity_type_code column and the ref_activity_types table's activity_type_code column.

Join Comparisons to Static Values

If you need to compare a field to a static value in a join, append "Value" to the end of the operator.

query joinValue {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2024-01-01"}}) {
        activity_name
        activity_type_code
        created_date
        icis__ref_activity_type (inner_join: {icis__icis_activity_report__activity_type_code: {equalsValue: "INS"}}) {
            activity_type_desc
        }
    }
}

In the query above, the join will occur with the icis_activity_report table's activity_type_codeorder_name being joined to the value of "INS".

Join Operator Types

• cross_join: Cross Join
• full_outer_join: Full Outer Join
• hash_join: Hash Join
• inner_join: Inner Join
• left_join: Left Join
• left_outer_join: Left Outer Join
• right_join: Right Join
• right_outer_join: Right Outer Join
• outer_join: Outer Join

Functions in Joins

Joins support the use of functions when joining tables. Trim (along with LTrim and RTrim), LPad, Replace, Substring, and Cast are supported. Here is an example of a join using Trim to ensure that the joined field matches the format on the parent table:

query joinFunction {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2024-01-01"}}) {
        activity_name
        activity_type_code
        created_date
        icis__ref_activity_type (inner_join: {icis__icis_activity_report__activity_type_code: {equals: {activity_type_code: {trim: [" "]}}}}) {
            activity_type_desc
        }
    }
}

Filters in Joins

Filters can be applied to joined tables just as the primary table in the query. The where clause should be specified in the block with the joined table.

query join {
    icis__icis_activity_report (limit: 10) {
        activity_name
        activity_type_code
        created_date
        icis__ref_activity_type (inner_join: {icis__icis_activity_report__activity_type_code: {equals: activity_type_code}}, where: {activity_group_code: {equals: "CMM"}}) {
            activity_type_desc
        }
    }
}

In the above query, the query will be filtered by the activity_group_code column in the ref_activity_type table being equal to "CMM".

Subqueries

The API supports a limited version of subqueries. A subquery can be created similar to a join, only using the subquery parameters. A subquery can still have filters, orderBy, and groupBy clauses applied to it. The insubquery parameter in the primary table allows the primary table to be filtered by the results of the subquery. If this parameter is used, the subquery must have an alias applied to it to reference in the parameter value.

query subquery {
  rcra__br_reporting(
    where: {and: {report_cycle: {equalsSensitive: 2019}}, and: {handler_id: {insubquery: "receiverIds"}}}
    distinct: true
  ) {
    handler_id
    handler_name
    rcra__br_reporting(
      subquery: {alias: "receiverIds"}
      where: {and: {br_form: {equals: "GM"}}, and: {management_location: {equals: "offsite"}}, and: {gen_waste_included_in_nbr: {equals: "y"}}, and: {ship_waste_included_in_nbr: {equals: "y"}}, and: {handler_id: {equals: "AK9690330742"}}, and: {report_cycle: {equalsSensitive: 2019}}}
      distinct: true
    ) {
      receiver_id
      rcra__rcr_hd_basic(
        inner_join: {rcra__br_reporting__handler_id: {equals: handler_id}}
      )
    }
  }
}

This query will be run against the br_reporting table in the rcra schema. A subquery will be performed on the br_reporting table itself to filter results.

Pagination

To paginate through results, the limit and offset parameters can be supplied.

query paging {
    icis__icis_activity_report (limit: 10, offset: 20) {
        activity_name
        activity_status_desc
        actual_begin_date_fy
    }
}

In this query, a maximum of 10 records will be returned. The first 20 matching records in the icis_activity_report table will be skipped and the next 10 records will be returned.

Ordering Results

Results can be ordered through the orderBy query parameter. "asc", "ascNullsFirst", "ascNullsLast","desc", "descNullsFirst", and "descNullsLast" are the only valid values for orderBy.

query orderBy {
    icis__icis_activity_report (limit: 10, orderBy: {activity_name: "asc", activity_status_desc: "desc"}) {
        activity_name
        activity_status_desc
        actual_begin_date_fy
    }
}

The results in this example will be ordered by first the activity_name field ascending and then by the activity_status_desc field descending.

Ordering with Specific Sequencing

Some client libraries may not put order by elements in the correct sequence when serializing the query and instead always put the fields in the alphabetical order. To force the correct order, use a list to specific the elements to order by.

query orderBySpecify {
    icis__icis_activity_report (limit: 10, orderBy: [{activity_status_desc: "desc"}, {activity_name: "asc"}]) {
        activity_name
        activity_status_desc
        actual_begin_date_fy
    }
}

The results in this example will be ordered by first the activity_status_desc field descending and then by the activity_name field ascending.

Ordering Joined Columns

When joining tables and wanting to order the results by columns in a joined table, specify all the joins in the primary table. For the columns in the joined table, use the format schema__table__column to specify the column that should be joined. (The schema, table, and column are separated by double underscores __.)

query joinOrderBy {
    icis__icis_activity_report (limit: 10, where: {created_date: {greaterThan: "2024-01-01"}}, orderBy: {activity_name: "asc", icis__ref_activity_type__activity_type_desc: "desc"}) {
        activity_name
        activity_type_code
        created_date
        icis__ref_activity_type (inner_join: {icis__icis_activity_report__activity_type_code: {equals: activity_type_code}}) {
            activity_type_desc
        }
    }
}

In this query, the results will be ordered first in ascending order by the activity_name column in the icis_activity_report table and then by the activity_type_desc column from the ref_activity_type table in descending order.

Grouping Results

Results can be grouped via the groupBy parameter, which is an array of the fields to group. This is necessary if there are other fields in the query that are being aggregated.

query groupBy {
    icis__icis_activity_report (limit: 10, groupBy: ["activity_name", "activity_status_desc"]) {
        activity_name
        activity_status_desc
        fn__max(parameters: ["activity_status_date"], alias: "activity_status_date")
    }
}

The results in this example will be grouped by the activity_name and activity_status_desc fields because the maximum activity_status_date field is being queried.

Variables

As with normal GraphQL queries, variables can be substituted for values in query parameters, for example in the limit field or the filter values. The allowed types of values are String, Int, Float, and OrderBy. The String, Int, and Float values can also be stored in arrays.

In the query below the limit, offset, and orderBy values are provided by variables.

Query

query variables($limit: Int!, $offset: Int!, $orderBy: OrderBy!) {
    icis__icis_activity_report (limit: $limit, offset: $offset, orderBy: $orderBy) {
        activity_name
        activity_status_desc
        activity_status_date
    }
}

Variables

{"limit": 10, "offset": 30, "orderBy": {"activity_name": "asc", "activity_status_desc": "desc"}}

Here the limit, offset, and orderBy variables will be substituted in the query when it is executed against the data source. The $limit, $offset variables are required integers. The $orderBy variable is a required variable in the order by clause structure.

A query example with the where clause values being supplied in variables.

Query

query filterVariables($state: [String]) {
    icis__icis_activity_report (limit: 10, where: {state_code: {in: $state}}) {
        activity_name
        activity_type_code
        state_code
    }
}

Variables

{"state": ["MI", "WI"]}

Here the $state variable is an array of strings. The values of "MI", and "WI" will be provided to the query when it runs against the database.

Multiple queries

Multiple queries can be sent in the same request.

query multiple {
    icis__icis_activity_report (limit: 10) {
        activity_name
        activity_status_desc
        actual_begin_date_fy
    }
    icis__icis_activity_report {
        aggregate {
            count (distinct: true, fields: ["actual_end_date_fy", "activity_name"], alias: "field_count")
        }
    }
}

They will be run sequentially. This increases the chance of a timeout on the request with no results being returned.

Returned data

Data is returned from the service in a nested JSON object. The primary element is called data. Each table's results will be returned as a property within the data element. For example:

{
    "data": {
        "icis__icis_activity_report": [
            {
                "activity_name": "Inspection",
                "activity_status_desc": "Active",
                "actual_begin_date_fy": "2015"
            },
            {
                "activity_name": null,
                "activity_status_desc": "Active",
                "actual_begin_date_fy": null
            },
        ],
        "icis__icis_activity_report2": [
            {
                "field_count": 1874078
            }
        ]
    },
    "status": "COMPLETED"
}

Export formats

Data can also be returned in the following formats: CSV, Excel, JSON, JSONP, XML, Parquet, HTML, PDF, and GraphQL. To export a query’s results to a different format, append the format to the end of the URL. If using JSONP, add the callback to wrap the result in with the "callback" query parameter.

• CSV: /query/csv
• Excel: /query/excel
• XML: /query/xml
• JSON: /query/json
• JSONP: /query/jsonp?callback=callback-value
• Parquet: /query/parquet
• HTML: /query/html
• PDF: /query/pdf
• GraphQL: /query/graphql

The GraphQL export format is similar to the regular format (/query) with the exception that it will nested results from joined tables. This format is provided to have greater compatibility with some GraphQL clients.

Limitations

The API has a current limit of returning maximum 100,000,000 records. However, there is a hard cutoff of 15 minutes for a query. Any query that exceeds that limit will not return results. If this is a factor in designing a query, it is recommended to use the limit and offset operators to page through the data set.

This API is not a true GraphQL implementation. It does not support mutation, query fragments, introspection, or have a schema file. It merely allows a GraphQL-compliant syntax to perform complex queries that are not possible within the DMAP REST Service.