Querying JSON Columns

Logs can contain complex data represented as JSON. Tailpipe plugins store such objects as one of two native DuckDB types: JSON or STRUCT. Learn about JSON idioms here, see Querying STRUCT Columns for STRUCT idioms.

When an object's instances have irregular shape, a plugin uses DuckDB's JSON type. The request_parameters column of the aws_cloudtrail_log table is a JSON column, as you can verify using the typeof function.

You can list the keys of a JSON object:

The request_parameters column contains a JSON object that includes a Host key whose value you can extract with a function:

Or with the JSON operator that returns a stringified representation of an element:

Both methods return a string that you can compare.

In this case, DuckDB's precedence rules require you to parenthesize the ->> expression. To avoid confusion we often prefer functions over operators in Tailpipe mods.

The resource column of aws_cloudtrail_log is a JSON array of objects. You use 0-based indexing to access elements of an array. To access the first element:

Alternatively you can use the JSON -> operator:

Either method returns a JSON object that you can drill into.