Ingestion of nested JSON data missing

This issue is likely related to how the nested JSON structure is being handled during the ingestion and conversion process. Here are a few suggestions to address the problem:

1. Check your AWS Glue Data Catalog schema: Ensure that the schema created in AWS Glue Data Catalog accurately matches the input data structure, including the nested JSON elements. For nested JSON, use a STRUCT type in the schema that mirrors the structure of your JSON data. If the schema doesn't properly represent the nested structure, the converted data will not contain attributes that are not specified in the schema.

2. Review your Firehose configuration: Make sure that the case sensitivity setting in your Firehose configuration is appropriate. If your JSON object has capital letters and the case sensitivity is set to false, it can lead to a mismatch in JSON keys after data transformation, causing missing data in the resulting Parquet object in the S3 bucket. To fix this, set the "deserializationOption: case.insensitive" to true in the Firehose configuration.

3. Use appropriate AWS Glue ETL techniques: If you're using AWS Glue for ETL processes, consider using the 'relationalize' function to convert nested JSON columns into separate columns in your AWS Glue ETL job. Alternatively, you can use the 'jsonPath' option in your AWS Glue ETL job configuration's format option values. Another option is to use the 'unnest' function to convert nested fields into top-level objects.

4. Create a custom JSON classifier: If your AWS Glue crawler needs to read nested columns, create a custom classifier defined as a JSON classifier. Then, create a new AWS Glue crawler and add this custom JSON classifier to its list of classifiers.

5. Check Firehose record format conversion: If you're using Firehose's record format conversion feature to convert JSON to Parquet, ensure that the deserializer (e.g., OpenX JSON SerDe) is configured correctly to handle your specific JSON structure, including any nested elements.

Further, If you want to handle nested json data that is retrieved successfully in athena, You can also use https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html

By implementing these suggestions, you should be able to properly ingest and preserve the nested JSON structure in your Parquet files on S3, making the full data structure available for querying via Athena or direct S3 access.

Sources
Process JSON files in AWS Glue | AWS re:Post
Convert input data format in Amazon Data Firehose - Amazon Data Firehose
Common issues - Amazon Data Firehose