CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
Patrick Ward
Patrick Ward

Reputation: 422

Athena Query JSON Fields Stored as String

I have an Athena table which has a field

fields (string)

The data looks like

{"field_1":"value_1","field_2":"value_2"}
{"field_1":"value_3","field_2":"value_4"}
...

I would like to query such as:

select "fields"."field_1" as field_1 from table_name where field_1 = "something"

I have tried many methods and only either gotten an error or only empty values for field_1. Any help would be greatly appreciated.

Upvotes: 2

Views: 3058

Answers (1)

Philipp Johannis
Philipp Johannis

Reputation: 2956

Based on the sample data it looks like fields is a JSON. So the first thing I would do is to use json_parse to convert it.

The syntax "fields"."field_1" would work for a row column - however, it looks like it's not possible to cast a json to a row in Athena (which is based on Presto 0.172) - see Cast from JSON in the 0.172 Presto Release Documentation.

You may convert it to a map<varchar,varchar> and then access it via Subscript Operator: []

Example:

with example as
(
    SELECT * FROM (
    VALUES
        ('{"field_1":"value_1","field_2":"value_2"}'),
        ('{"field_1":"value_3","field_2":"value_4"}')
    ) AS t (fields)
)
,example_cast as
(
    select *
    ,cast(json_parse(fields) AS map<varchar,varchar>) fields_parsed
    from example 
)
select *
,fields_parsed['field_1'] fields_field_1
from example_cast;

Upvotes: 5

Related Questions