Query String Nested JSON Data in New S3 Table Buckets (Iceberg) with DuckDB via IRCC

In the rapidly evolving data landscape, the ability to efficiently store and query complex JSON data has become increasingly important. This blog post explores a powerful combination: AWS S3 Table Buckets + Apache Iceberg + Nested JSON + DuckDB - a solution that delivers blazing-fast in-memory analytics.

The Perfect Combo: Why It Works

AWS S3 Table Buckets provide a fully managed Iceberg solution on AWS, giving you an optimized storage layer for your data. While variant type support is still developing, you can already store nested JSON as strings and query them efficiently with DuckDB, an in-memory analytical database that excels at processing complex data.

This approach gives you:

• Scalability of S3 storage
• Performance of DuckDB in-memory analytics
• Flexibility of JSON for nested structures
• Versioning and time travel capabilities of Apache Iceberg

Creating an Iceberg Table with Nested JSON

Let's first create an Iceberg table that stores nested JSON data as strings. The following Python script uses PyIceberg to create a table and insert sample customer data with nested contact information:

The key insight here is that we're storing complex JSON structures as strings in the contact_info column. This approach provides flexibility while Iceberg continues to develop its native variant/struct support.

Querying Nested JSON with DuckDB

Now for the exciting part—querying this data with DuckDB. DuckDB's in-memory processing combined with its robust JSON extraction functions delivers impressive performance.

Understanding the Query

Let's break down what's happening in the query:

1. Setup and Authentication: We install and load all necessary extensions, set up AWS credentials, and connect to our Iceberg catalog.
2. Basic Query: First, we query all data to verify the table structure.
3. JSON Extraction: The magic happens with json_extract() functions that let us:Extract top-level fields like email and phoneNavigate nested structures like address.street and address.city

his approach gives you full SQL query capabilities over nested JSON data without waiting for Varient support in Iceberg.

Performance Benefits

This solution offers several performance advantages:

1. In-memory Processing: DuckDB loads data into memory for blazing-fast analysis
2. Columnar Storage: Iceberg's columnar format enables efficient data access
3. Selective Querying: Only extract the JSON fields you need
4. Parallelization: DuckDB can parallelize JSON extraction across multiple cores

Looking Ahead

While storing JSON as strings is a powerful approach today, keep an eye on upcoming Iceberg features:

• Native variant support that will make querying even faster Ticket
• Nested column pruning improvements
• JSON-specific optimizations in the Iceberg format

Conclusion

The combination of AWS S3 Table Buckets, Apache Iceberg, and DuckDB provides a flexible and high-performance solution for working with nested JSON data. This approach bridges the gap while waiting for full variant support and offers immediate benefits for organizations with complex data structures.

By leveraging the techniques described in this blog post, you can achieve both the flexibility of JSON and the analytical power of a modern data lake architecture.