Find AWS resource identifiers by tag

Several AWS services are ID-centric rather than name-centric: resources such as VPCs, subnets and security groups are addressable only by a generated identifier, and their human-meaningful names live in tags. This query resolves tags to identifiers through the resource groups tagging API: filter by resource type and a tag, get back the ARN, the extracted id and every tag on each matching resource. The identifiers feed WHERE clauses in follow-up queries against the resource's own service.

Query

SELECT resource_arn, resource_id, key, value FROM
(
SELECT
ResourceARN as resource_arn,
split_part(ResourceARN, '/', -1) as resource_id,
json_extract(json_each.value, '$.Key') as key,
json_extract(json_each.value, '$.Value') as value
FROM awscc.tagging.tagged_resources, json_each(tags)
WHERE region = '{{region}}'
AND ResourceTypeFilters = '["{{resource_type}}"]'
AND TagFilters = '[{"Key": "{{tag_key}}", "Values": ["{{tag_value}}"]}]'
) t;

Variation: discover tags for a resource type

Drop TagFilters to list every tagged resource of the type with its tags - useful for discovering what keys and values exist before filtering:

SELECT
ResourceARN as resource_arn,
split_part(ResourceARN, '/', -1) as resource_id,
json_extract(json_each.value, '$.Key') as key,
json_extract(json_each.value, '$.Value') as value
FROM awscc.tagging.tagged_resources, json_each(tags)
WHERE region = '{{region}}'
AND ResourceTypeFilters = '["{{resource_type}}"]';

PostgreSQL backend dialect

The JSON functions above are the default embedded SQLite backend's dialect. On a PostgreSQL-backed stackql instance, explode the tags array with json_array_elements_text and extract with json_extract_path_text instead:

SELECT
ResourceARN as resource_arn,
split_part(ResourceARN, '/', -1) as resource_id,
json_extract_path_text(tag::json, 'Key') as key,
json_extract_path_text(tag::json, 'Value') as value
FROM awscc.tagging.tagged_resources,
json_array_elements_text(tags::json) as tag
WHERE region = '{{region}}'
AND ResourceTypeFilters = '["{{resource_type}}"]'
AND TagFilters = '[{"Key": "{{tag_key}}", "Values": ["{{tag_value}}"]}]';

Notes

TagFilters is a JSON array: multiple filter objects must all match (AND), while multiple entries in one filter's Values list match any (OR), e.g. '[{"Key": "stackql:stack-name", "Values": ["sqlserver-migration-lab"]}, {"Key": "stackql:stack-env", "Values": ["dev"]}]'. ResourceTypeFilters also accepts multiple entries. All tags of each matching resource are returned, not just the filtered key. resource_id takes the last /-delimited ARN segment, which suits most ARN formats (vpc/vpc-..., subnet/subnet-...); for resource types whose ARN ends in a :-delimited id, split on ':' instead. The JSON exploding functions are backend dialect: json_each/json_extract on the default embedded SQLite backend, json_array_elements_text with json_extract_path_text on PostgreSQL.