# Querying Nested JSON

Handling nested JSON in SQL can be a powerful way to manage complex data structures. With Telemetry, you have the flexibility to ingest any arbitrary JSON, making it a versatile tool for data analysis. This document provides an overview of how to handle nested JSON using SQL, particularly focusing on querying deeply nested elements in your JSON blobs.

### JSON Structure Example: Uber Ride Data

Consider the following JSON blob that Telemetry can ingest, which represents data from an Uber ride:

```json
{
  "ride_id": "12345",
  "driver": "John Doe",
  "passenger": "Jane Smith",
  "pricing": {
    "base_fare": 500,
    "cents": 1500,
    "currency": "USD",
    "details": {
      "surge_multiplier": 1.2,
      "distance_cost": 300
    }
  },
  "timestamp": "2023-08-05T10:22:00.000Z"
}
```

In this example, the JSON has nested objects within the `pricing` key.

### Querying Nested JSON

One of the great strengths of using Telemetry is its ability to handle nested JSON fields effectively. Let's explore how to perform such queries using SQL.

#### Extracting Top-Level Fields

For top-level fields, querying is straightforward. For example, to get the average `cents` value from a table named `ride_log`, you can use:

```sql
SELECT AVG(pricing.cents) FROM ride_log;
```

#### Extracting Nested Fields

DataFusion, the popular SQL query engine we use under the hood, currently does not support deeply nested operators directly. However, with Telemetry, you can still access these deeply nested fields using nested queries, demonstrating the flexibility and power of your product.

**Example: Extracting `pricing.details.surge_multiplier`**

To extract `pricing.details.surge_multiplier`, you can use a subquery to first extract the nested object `pricing.details` and then further extract `surge_multiplier` from it:

```sql
SELECT details.surge_multiplier 
FROM (
  SELECT pricing.details AS details 
  FROM ride_log
) AS subquery;
```

This approach allows you to navigate through multiple layers of nested JSON, ensuring you can access even the most deeply nested fields in your data.

### Conclusion

With Telemetry's capability to ingest any arbitrary JSON, you have a highly flexible and powerful tool at your disposal for data analysis. Whether you are working with top-level fields or deeply nested structures, SQL queries can be crafted to extract the exact data you need. Embrace the versatility of Telemetry to unlock the full potential of your JSON data.