Data Adapter queries
Use these examples to execute SQL-like queries on your data
Overview
Amazon Athena allows users to query data using a subset of standard SQL syntax. Read more about the statements, functions, operators, and data types supported by Athena in their SQL reference.
These are likely to be the most useful guides for most users:
- DML statements guide - Explains how you can extract and manipulate data.
- SQL functions guide - Lists the supported SQL functions.
To help you get started, this guide provides examples of some of the most important SQL statements that you can use to query your data on Root.
Dates
All dates, excluding dates stored in JSON objects, are stored as a TIMESTAMP
data type in the UTC timezone. Check out the date and time functions guide on AWS for the full list of available functions.
Timezones
All standard dates are in the UTC timezone. To determine the date in a different timezone you can convert the date using a UTC offset. To do this you can use the at_timezone
function.
SELECT
policy_number,
at_timezone(start_date, 'UTC+2') AS "start_date",
at_timezone(created_at, 'UTC+2') AS "created_at"
FROM
policies;
Intervals
Timestamps allow you to perform operations like adding and subtracting time, which is useful when you want to return results based on a date condition (e.g. for a specific period).
To add or subtract time from a timestamp you can use the INTERVAL
statement.
SELECT
policy_number,
start_date,
created_at
FROM
policies
WHERE
start_date > now() - INTERVAL '30' DAY;
Dates included in JSON objects
Dates included in JSON objects are in the ISO 8601 format and need to be parsed into a TIMESTAMP
data type before using the date and time functions on them. To parse the date you can use the from_iso8601_timestamp
function .
select
from_iso8601_timestamp(json_extract_scalar(module,
'$.module_data_date')) as module_data_date
from
policies
JSON objects
JSON objects are saved as a string (VARCHAR
) on tables. You can extract data from and find values in JSON objects.
Extracting properties
To extract properties from a JSON object or array you can use the json_extract
function. The json_extract
function searches the JSON string using a JSON path-like expression using dot .
and [x]
notation, similar to how you would access properties in JavaScript objects or arrays.
SELECT
policy_number,
json_extract(module,'$.type') as type,
json_extract(module,'$.charges[0].description') as charges_description,
json_extract(module,'$.charges[0].amount') as charges_amount
FROM
policies
Working with extracted properties
When you use the json_extract
function, the extracted data is returned as a JSON string. To perform additional tasks on the returned data you need to convert it to a different data type.
One option is to use the json_extract_scalar
function which returns the extracted data as a VARCHAR
type. The selected property must be a scalar value (boolean, number or string) for the function to work.
SELECT
policy_number,
json_extract(module,'$.charges[0].description') as charges_description,
json_extract(module,'$.charges[0].amount') as charges_amount
FROM
policies
WHERE
json_extract_scalar(module, '$.type') = 'template_funeral';
Another option is to cast the data to a different type after you have extracted it using the json_extract
function.
SELECT
policy_number,
json_extract(module,'$.charges[0].description') as charges_description,
json_extract(module,'$.charges[0].amount') as charges_amount
FROM
policies
WHERE
CAST(json_extract(module, '$.type') AS VARCHAR) = 'template_funeral';
Join tables
Data related to a particular entity can be distributed across multiple tables. For example, policy attachments are stored in the attachments
table. You can use standard SQL JOIN
statements to combine related data across different tables.
SELECT
pol.policy_number,
att."type",
att.filename,
att.url
FROM
policies pol
JOIN
attachments att ON pol.policy_id = att.resource_id
WHERE
pol.status = 'active'
Miscellaneous examples
Return the top 10 agents (dashboard users), by number of policies issued, over the past 30 days.
SELECT
u.first_name,
u.last_name,
u.email,
count(*)
FROM
policies p
JOIN
users u ON u.id = cast(json_extract(p.created_by, '$.id') as varchar)
WHERE
p.created_at > now() - interval '30' day
AND p.environment = 'production'
GROUP BY
u.first_name,
u.last_name,
u.email
ORDER BY
count(*) DESC
LIMIT 10;
Return blocked payment methods for policies issued in the past 30 days.
SELECT
pm.*
FROM
policies p
LEFT JOIN payment_methods pm ON
p.payment_method_id = pm.payment_method_id
WHERE
p.environment = 'production'
AND p.status = 'active'
AND p.created_at > now() - interval '30' day
AND pm.blocked_reason IS NOT NULL;
Determine in which areas policyholders live
SELECT
country,
city,
suburb,
area_code,
count(*)
FROM
policyholders
WHERE
environment = 'production'
GROUP BY
country,
city,
suburb,
area_code
ORDER BY
country ASC;
Updated about 2 months ago