In Athena how can I structure a select statement to query the below by timestamp? The data is stored as a string
[{
"data": [{
"ct": "26.7"
}, {
"ct": "24.9",
}, {
"ct": "26.8",
}],
"timestamp": "1658102460"
}, {
"data": [{
"ct": "26.7",
}, {
"ct": "25.0",
}],
"timestamp": "1658102520"
}]
I tried the below but it just came back empty.
SELECT json_extract_scalar(insights, '$.timestamp') as ts
FROM history
What I am trying to get to is returning only the data where a timestamp is between X & Y.
When I try doing this as a struct and a cross join with unnest it's very very slow so I am trying to find another way.