Querying STRUCT Columns

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

When all instances of the object have a regular shape, a plugin uses DuckDB's STRUCT type. The user_identity column of the aws_cloudtrail_log table is a STRUCT column, as you can verify using the typeof function.

DuckDB doesn't have a struct_keys function analogous to json_keys but you can list the keys of STRUCT by casting to JSON

The user_identity column includes an invoked_by field that you can extract using dot notation:

Because the STRUCT-defined type of invoked_by field is VARCHAR, you can compare it to a string.

The user_identity column includes a nested STRUCT, session_context. You can use dot notation to drill into it: