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 tables [joins] [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

Table Alias

SELECT i.address, r.type FROM instances AS i
JOIN resources AS r ON i.resource_address = r.address AND i.state_id = r.state_id

Available Tables

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 LIKE '%aws%'

Expressions

Literals

Identifiers

Column and table names:

address
type
instances.address
r.type

Operators

Comparison Operators

Logical Operators

Arithmetic Operators

String Operators

NULL Handling

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

Pattern Matching

LIKE

SELECT * FROM instances WHERE address LIKE '%web%'
SELECT * FROM resources WHERE type LIKE 'aws_%'

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

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

Joins

INNER JOIN

SELECT i.address, s.name
FROM instances i
INNER JOIN states s ON i.state_id = s.id

LEFT JOIN

SELECT s.name, COUNT(i.address)
FROM states s
LEFT JOIN instances i ON s.id = i.state_id
GROUP BY s.name

RIGHT JOIN

SELECT i.address, s.name
FROM instances i
RIGHT JOIN states s ON i.state_id = s.id

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 LIKE '%aws%'
)
SELECT type, COUNT(*) FROM aws_resources GROUP BY type

Multiple CTEs

WITH
  aws AS (SELECT * FROM resources WHERE provider LIKE '%aws%'),
  gcp AS (SELECT * FROM resources WHERE provider LIKE '%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.

Operator Precedence

From highest to lowest:

1. :: (type cast)
2. ->, ->>, #>> (JSON)
3. - (unary negation)
4. *, /
5. +, -
6. \|\| (concatenation)
7. =, <>, <, >, <=, >=
8. IS, IS NOT, IS DISTINCT FROM, IS NOT DISTINCT FROM
9. IN, LIKE
10. NOT
11. AND
12. 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, INNER, IS, JOIN, LEFT, LIKE, LIMIT, NOT, NULL,
ON, OR, ORDER, RIGHT, 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 (join instances with resources for type filter)
SELECT
  i.address,
  i.attributes->>'instance_type' AS instance_type
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'

-- Filter by attribute
SELECT i.address
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'
  AND i.attributes->>'instance_type' = 't3.micro'

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

Joins

-- Resources with state info
SELECT
  r.address,
  r.type,
  s.name AS state_name,
  s.workspace
FROM resources r
JOIN states s ON r.state_id = s.id
WHERE r.type LIKE 'aws_%'
ORDER BY s.name, r.address

Complex Queries

-- Resources by type and workspace
WITH resource_counts AS (
  SELECT
    s.workspace,
    r.type,
    COUNT(*) AS count
  FROM resources r
  JOIN states s ON r.state_id = s.id
  GROUP BY s.workspace, r.type
)
SELECT *
FROM resource_counts
WHERE count > 10
ORDER BY workspace, 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'