MQL Syntax Reference

Complete grammar reference for MQL (Mapping Query Language).

Grammar Overview

MQL is a SQL-like query language designed for querying Terraform state data.

query := [cte] SELECT select_list FROM table [WHERE expr] [GROUP BY exprs] [HAVING expr] [ORDER BY order_exprs] [LIMIT n]

SELECT Statement

Basic Syntax

SELECT columns FROM table

Select All Columns

SELECT * FROM instances

Select Specific Columns

SELECT address, type, provider FROM resources

Column Aliases

SELECT address AS resource_address, type AS resource_type FROM resources

FROM Clause

Single Table

SELECT * FROM instances

Available Tables

Table	Description
`instances`	Resource instances (attributes, dependencies, state)
`resources`	Resource definitions (type, provider, module)
`states`	Terraform states
`providers`	Provider information
`outputs`	State outputs
`transactions`	Transaction history
`transaction_logs`	Transaction log entries
`tenants`	Tenant information
`users`	User information
`check_results`	Check results
`check_entries`	Check entries

See Query (MQL) for complete column documentation.

WHERE Clause

Basic Filtering

SELECT * FROM resources WHERE type = 'aws_instance'

Multiple Conditions

SELECT * FROM resources
WHERE type = 'aws_instance'
  AND provider = 'registry.terraform.io/hashicorp/aws'

Expressions

Literals

Type	Examples
String	`'hello'`, `'aws_instance'`
Integer	`42`, `-1`, `0`
Float	`3.14`, `-0.5`
Boolean	`true`, `false`
Null	`null`

Identifiers

Column and table names:

address
type
instances.address

Operators

Comparison Operators

Operator	Description	Example
`=`	Equal	`type = 'aws_instance'`
`<>`	Not equal	`type <> 'null_resource'`
`<`	Less than	`count < 10`
`>`	Greater than	`count > 100`
`<=`	Less or equal	`count <= 50`
`>=`	Greater or equal	`count >= 5`

Logical Operators

Operator	Description	Example
`AND`	Logical AND	`a = 1 AND b = 2`
`OR`	Logical OR	`a = 1 OR b = 2`
`NOT`	Logical NOT	`NOT a = 1`

Arithmetic Operators

Operator	Description	Example
`+`	Addition	`a + b`
`-`	Subtraction	`a - b`
`*`	Multiplication	`a * b`
`/`	Division	`a / b`
`-` (unary)	Negation	`-a`

String Operators

Operator	Description	Example
`\\|\\|`	Concatenation	`'hello' \\|\\| ' world'`

NULL Handling

Expression	Description
`IS NULL`	Value is null
`IS NOT NULL`	Value is not null
`IS DISTINCT FROM`	NULL-safe not equal
`IS NOT DISTINCT FROM`	NULL-safe equal

SELECT * FROM resources WHERE module IS NULL
SELECT * FROM resources WHERE module IS NOT NULL

Pattern Matching

IN

SELECT * FROM resources
WHERE type IN ('aws_instance', 'aws_ebs_volume', 'aws_eip')

Type Casting

SELECT count::text FROM summary
SELECT attributes::jsonb FROM instances

Syntax: expression::type

JSON Operators

Access JSON data stored in the attributes column.

Arrow Operators

Operator	Description	Returns
`->`	Get JSON value	JSON
`->>`	Get JSON as text	Text
`#>>`	Get nested path as text	Text

Examples

-- Get JSON value
SELECT attributes->'tags' FROM instances

-- Get as text
SELECT attributes->>'instance_type' FROM instances

-- Nested path
SELECT attributes#>>'{tags,Name}' FROM instances

JSON Contains

-- Check if JSON contains value
SELECT * FROM instances
WHERE attributes->'tags' @> '{"Environment": "production"}'

Aggregation

COUNT

SELECT COUNT(*) FROM instances
SELECT COUNT(*) FROM resources

GROUP BY

SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type

HAVING

SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
HAVING COUNT(*) > 10

Ordering

ORDER BY

SELECT * FROM resources ORDER BY type
SELECT * FROM resources ORDER BY type ASC
SELECT * FROM resources ORDER BY type DESC

Multiple Columns

SELECT * FROM resources ORDER BY type ASC, address DESC

Limiting Results

LIMIT

SELECT * FROM instances LIMIT 100

Common Table Expressions (CTEs)

WITH Clause

WITH aws_resources AS (
  SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/aws'
)
SELECT type, COUNT(*) FROM aws_resources GROUP BY type

Multiple CTEs

WITH
  aws AS (SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/aws'),
  gcp AS (SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/google')
SELECT 'aws' AS cloud, COUNT(*) FROM aws
UNION
SELECT 'gcp' AS cloud, COUNT(*) FROM gcp

Functions

Built-in Functions

Note: Function names are case-sensitive and must be lowercase.

Function	Description	Example
`count(*)`	Count rows	`count(*)`
`sum(expr)`	Sum values	`sum(count)`
`avg(expr)`	Average values	`avg(count)`
`nullif(a, b)`	Return null if a = b	`nullif(value, 0)`
`to_char(expr, fmt)`	Format as string	`to_char(created_at, 'YYYY-MM-DD')`
`json_build_object(...)`	Build JSON object	`json_build_object('key', value)`

Operator Precedence

From highest to lowest:

:: (type cast)
->, ->>, #>> (JSON)
- (unary negation)
*, /
+, -
\|\| (concatenation)
=, <>, <, >, <=, >=
IS, IS NOT, IS DISTINCT FROM, IS NOT DISTINCT FROM
IN
NOT
AND
OR

Use parentheses to override precedence:

SELECT * FROM instances WHERE (a = 1 OR b = 2) AND c = 3

Reserved Keywords

AND, AS, ASC, BY, COUNT, DESC, DISTINCT, FALSE, FROM, GROUP,
HAVING, IN, IS, LIMIT, NOT, NULL,
ON, OR, ORDER, SELECT, TRUE, WHERE, WITH

Keywords are case-insensitive.

Comments

MQL does not currently support comments in queries.

Query Examples

Basic Queries

-- All instances
SELECT * FROM instances

-- All resources
SELECT * FROM resources

-- Specific type (use resources table)
SELECT * FROM resources WHERE type = 'aws_instance'

-- Multiple conditions
SELECT * FROM resources
WHERE type = 'aws_instance'
  AND module IS NOT NULL

Aggregations

-- Count by type
SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
ORDER BY count DESC

-- With minimum count
SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
HAVING COUNT(*) > 5
ORDER BY count DESC

JSON Queries

-- Get specific attribute
SELECT
  address,
  attributes->>'instance_type' AS instance_type
FROM instances

-- Filter by attribute
SELECT address
FROM instances
WHERE attributes->>'instance_type' = 't3.micro'

-- Check tags
SELECT address
FROM instances
WHERE attributes->'tags'->>'Environment' = 'production'

Complex Queries

-- Resources by type with counts
SELECT type, COUNT(*) AS count
FROM resources
WHERE provider = 'registry.terraform.io/hashicorp/aws'
GROUP BY type
ORDER BY count DESC

Error Handling

Syntax Errors

Invalid syntax returns an error with position information:

Error at line 1, column 15: Expected 'FROM'

Type Errors

Type mismatches return descriptive errors:

Error: Cannot compare string with integer

Unknown Columns

References to non-existent columns:

Error: Unknown column 'foo'