CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenapresto
IanPoli
IanPoli

Reputation: 109

AWS Athena Create Table with JSON

I'm trying to create a table with this below data value:

{"name": "Tommy", "Age": 16, "date":{"string": "2020-10-10"}} 
{"name": "Will", "Age": 20, "date":{"string": "2020-10-10"}}

but when I try to access data from a select it comes: {"string":"2020-10-10"} and I just need data value

there is any option to solve this on create table step? For exemple, to create a table looking to the date["string"] value.

I know, this is very specific, but if someone knows I'll be very happy! Thks

Upvotes: 0

Views: 198

Answers (1)

Theo
Theo

Reputation: 132862

One common way to solve this kind of situation is to use a view. Assuming you have a table called nested_Data with name, age, and date columns where the date column is defined as struct<string:string>, you can create a view like this:

CREATE VIEW flat_data AS
SELECT name, age, date['string'] AS date
FROM nested_data

when you run a query like SELECT date FROM flat_data you will get only the date value.

Views are often used like this when the raw data needs a bit of pre-processing and you want to avoid having to include all that pre-processing in every query.

Upvotes: 1

Related Questions