# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.telemetry.sh/discussion-topics/querying-nested-json.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.