I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date

Count ROW type item Athena / Presto

I have an Athena query like this

SELECT typeof(records)
FROM "default"."table"
WHERE date = '2021-07-24'
AND device_id = 'abc'

and the result is

_col0
row(1579560177000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579518772000 row(ds decimal(38,9)), 1579529572000 row(ds decimal(38,9)), 1579554776000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579556575000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579517872000 row(ds decimal(38,9)), 1579547574000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579525072000 row(ds decimal(38,9)), 1579551176000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579555676000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579540372000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579564676000 row(ds decimal(38,9)), 1579548475000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579530472000 row(ds decimal(38,9)), 1579528672000 row(ds decimal(38,9)), 1579552076000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579516072000 row(ds decimal(38,9)), 1579519672000 row(ds decimal(38,9)), 1579520572000 row(ds decimal(38,9)), 1579561076000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579535872000 row(ds decimal(38,9)), 1579544873000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579557476000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579561976000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579546674000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579516972000 row(ds decimal(38,9)), 1579523272000 row(ds decimal(38,9)), 1579538572000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579525972000 row(ds decimal(38,9)), 1579542173000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579559276000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579521472000 row(ds decimal(38,9)), 1579522372000 row(ds decimal(38,9)), 1579533172000 row(ds decimal(38,9)), 1579537672000 row(ds decimal(38,9)), 1579526872000 row(ds decimal(38,9)), 1579562876000 row(ds decimal(38,9)), 1579545773000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579550275000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579558376000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579532272000 row(ds decimal(38,9)), 1579536772000 row(ds decimal(38,9)), 1579549375000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579552976000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579563776000 row(ds decimal(38,9)), 1579524172000 row(ds decimal(38,9)), 1579543973000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579553876000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579534972000 row(ds decimal(38,9)), 1579539472000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579541272000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579543073000 row(ab decimal(38,9), bc  decimal(38,9), cd decimal(38,9), de decimal(38,9), ef decimal(38,9), fg decimal(38,9), gh decimal(38,9), hi decimal(38,9), ij decimal(38,9), jk decimal(38,9)), 1579531372000 row(ds decimal(38,9)), 1579527772000 row(ds decimal(38,9)), 1579534072000 row(ds decimal(38,9)))

I would like to count the number of records per day per devices to have a result like this

device_id, date, recordsNb
abc, 2021-07-24, 37

EDIT

My dataset is actually like this

device_id, date, records
abc, 2021-07-24, {"1": {}, "2":{}}
def, 2021-07-24, {"a": {}, "b":{}, "c": {}}

Here the expected results would be :

device_id, date, recordsNb
abc, 2021-07-24, 2
def, 2021-07-24, 3

Answer

You can cast your json to map and count number of keys:

WITH dataset(device_id, date, records) AS (
    values 
        ('abc', date '2021-07-24', JSON '{"1": {}, "2":{}}'),
        ('def', date '2021-07-24', JSON '{"a": {}, "b":{}, "c": {}}')
)

SELECT device_id,
    date,
    cardinality(map_keys(cast(records as MAP(VARCHAR, JSON)))) recordsNb
FROM dataset

Output:

device_id	date	recordsNb
abc	2021-07-24	2
def	2021-07-24	3

UPD

Since you have ROWs, not json values, you can try next:

SELECT device_id,
    date,
    json_array_length(cast(records as json)) recordsNb
FROM table

Advertisement

Answer