Athena Query Unable to Retrieve Nested JSON Data from S3 Bucket

Hi Sreekanth, thanks for your question on re:Post.

Here are some common reasons and solutions for what might be causing your issue with queries only returning top-level values, and not the nested data.

Common Causes

1. JSON Format Issue

OpenX JSON SerDe requires each JSON record on a separate line:

{"id": 1, "user": {"name": "John", "age": 30}}
{"id": 2, "user": {"name": "Jane", "age": 25}}

This will NOT work (pretty print or single line):

[
  {
    "id": 1,
    "user": {
      "name": "John",
      "age": 30
    }
  }
]

Solution: Reformat your JSON file so each record is on a single line, or use Amazon Ion SerDe (see below).

Reference: Query JSON data

---

2. Missing Nested Structure in Table Schema

If your JSON has nested data, define it as STRUCT in the table:

CREATE EXTERNAL TABLE my_table (
  id INT,
  user STRUCT<
    name: STRING,
    age: INT
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket/path/';

Query nested fields:

SELECT id, user.name, user.age FROM my_table;

---

Solutions

Option 1: Use Amazon Ion SerDe (Handles Pretty Print)

If your JSON is in pretty print format, use Ion SerDe:

CREATE EXTERNAL TABLE my_table (
  id INT,
  user STRUCT<
    name: STRING,
    age: INT
  >
)
STORED AS ION
LOCATION 's3://your-bucket/path/';

---

Option 2: Reformat JSON File

Ensure each JSON record is on a single line:

# Using jq to reformat
jq -c '.[]' input.json > output.json

Or in Python:

import json

with open('input.json', 'r') as f:
    data = json.load(f)

with open('output.json', 'w') as f:
    for record in data:
        f.write(json.dumps(record) + '\n')

---

Option 3: Manually Define Table with Correct Schema

Drop the crawler-created table and create it manually with nested structures:

DROP TABLE IF EXISTS my_table;

CREATE EXTERNAL TABLE my_table (
  id INT,
  name STRING,
  address STRUCT<
    street: STRING,
    city: STRING,
    zip: STRING
  >,
  orders ARRAY<STRUCT<
    order_id: INT,
    amount: DOUBLE
  >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket/path/';

---

Querying Nested Data

Access STRUCT fields:

SELECT id, address.city, address.zip FROM my_table;

Access ARRAY elements:

SELECT id, order.order_id, order.amount
FROM my_table
CROSS JOIN UNNEST(orders) AS t(order);

---

References

• Query JSON data
• JSON SerDe libraries