JSON data is everywhere these days. Whether you're working with APIs, web applications, or modern databases, JSON has become a standard format for storing and exchanging data. However, processing JSON data in relational databases like PostgreSQL can be tricky because JSON doesn't naturally fit into the structured world of SQL.
PostgreSQL's `JSON_TABLE()` function bridges this gap by allowing you to temporarily convert JSON data into a relational table. This lets you use SQL commands to query and manipulate the JSON data as if it were a regular table. One particularly powerful feature of PostgreSQL's `JSON_TABLE()` is the **`PASSING` clause**, which allows you to pass variables into your JSON queries for dynamic calculations.
In this blog post, I'll explain how to use `JSON_TABLE()` with the `PASSING` clause to perform calculations directly on JSON data. I'll also provide simple examples with sample data and results to help you understand how this works.
---
## What is the `PASSING` Clause?
The `PASSING` clause in `JSON_TABLE()` lets you define variables that can be used within the JSON path expressions. These variables make it easy to perform dynamic calculations or comparisons without needing additional queries or complex logic.
For example, you can:
- Compare JSON values against a threshold.
- Filter rows based on conditions.
- Perform calculations using external inputs.
Let’s dive into some practical examples!
---
### Example 1: Filtering Expensive Guitars
Imagine you have a JSON object containing details about guitars, including their names and prices. You want to identify which guitars are "too expensive" based on a price limit.
#### Sample JSON Data:
```
{
"guitars": [
{"item": "Fender Telecaster", "price": 1000},
{"item": "Gibson Les Paul", "price": 2000},
{"item": "PRS semi-hollowbody", "price": 1500}
]
}
```
#### Query:
We’ll use `JSON_TABLE()` with the `PASSING` clause to set a `price_limit` variable and determine which guitars exceed this limit.
```sql
SELECT *
FROM JSON_TABLE(
'{"guitars":[{"item": "Fender Telecaster", "price": 1000},
{"item": "Gibson Les Paul", "price": 2000},
{"item": "PRS semi-hollowbody", "price": 1500}]}',
'$.guitars[*]' PASSING 1500 AS price_limit
COLUMNS (
row_num FOR ORDINALITY,
Guitar TEXT PATH '$.item',
Price INT PATH '$.price',
is_too_expensive BOOLEAN EXISTS PATH '$.price ? (@ > $price_limit)'
)
) AS guitar_table;
```
#### Result:
| row_num | Guitar | Price | is_too_expensive |
|---------|---------------------|-------|------------------|
| 1 | Fender Telecaster | 1000 | f |
| 2 | Gibson Les Paul | 2000 | t |
| 3 | PRS semi-hollowbody | 1500 | f |
**Explanation:**
- The `PASSING 1500 AS price_limit` sets the price limit to 1500.
- The `is_too_expensive` column checks if the price of each guitar exceeds the `price_limit`.
- The result shows that only the Gibson Les Paul is too expensive.
---
### Example 2: Calculating Discounts on Products
Suppose you have a JSON object representing products with their original prices. You want to calculate whether a product qualifies for a discount based on a minimum price threshold.
#### Sample JSON Data:
```json
{
"products": [
{"name": "Laptop", "price": 1200},
{"name": "Smartphone", "price": 800},
{"name": "Headphones", "price": 150}
]
}
```
#### Query:
We’ll use `JSON_TABLE()` with the `PASSING` clause to set a `discount_threshold` variable and determine which products qualify for a discount.
```sql
SELECT *
FROM JSON_TABLE(
'{"products":[{"name": "Laptop", "price": 1200},
{"name": "Smartphone", "price": 800},
{"name": "Headphones", "price": 150}]}',
'$.products[*]' PASSING 1000 AS discount_threshold
COLUMNS (
row_num FOR ORDINALITY,
Product TEXT PATH '$.name',
Price INT PATH '$.price',
qualifies_for_discount BOOLEAN EXISTS PATH '$.price ? (@ >= $discount_threshold)'
)
) AS product_table;
```
#### Result:
| row_num | Product | Price | qualifies_for_discount |
|---------|-------------|-------|------------------------|
| 1 | Laptop | 1200 | t |
| 2 | Smartphone | 800 | f |
| 3 | Headphones | 150 | f |
**Explanation:**
- The `PASSING 1000 AS discount_threshold` sets the minimum price for a discount to 1000.
- The `qualifies_for_discount` column checks if the product's price meets or exceeds the threshold.
- Only the Laptop qualifies for a discount.
---
### Example 3: Filtering Students Based on GPA
Let’s say you have a JSON object containing student data, including their names and GPAs. You want to identify students who meet a minimum GPA requirement.
#### Sample JSON Data:
```json
{
"students": [
{"name": "Alice", "gpa": 3.8},
{"name": "Bob", "gpa": 3.2},
{"name": "Charlie", "gpa": 2.9}
]
}
```
#### Query:
We’ll use `JSON_TABLE()` with the `PASSING` clause to set a `min_gpa` variable and filter students accordingly.
```sql
SELECT *
FROM JSON_TABLE(
'{"students":[{"name": "Alice", "gpa": 3.8},
{"name": "Bob", "gpa": 3.2},
{"name": "Charlie", "gpa": 2.9}]}',
'$.students[*]' PASSING 3.0 AS min_gpa
COLUMNS (
row_num FOR ORDINALITY,
Student TEXT PATH '$.name',
GPA FLOAT PATH '$.gpa',
meets_requirement BOOLEAN EXISTS PATH '$.gpa ? (@ >= $min_gpa)'
)
) AS student_table;
```
#### Result:
| row_num | Student | GPA | meets_requirement |
|---------|---------|-------|-------------------|
| 1 | Alice | 3.8 | t |
| 2 | Bob | 3.2 | t |
| 3 | Charlie | 2.9 | f |
**Explanation:**
- The `PASSING 3.0 AS min_gpa` sets the minimum GPA requirement to 3.0.
- The `meets_requirement` column checks if the student's GPA meets or exceeds the threshold.
- Alice and Bob meet the requirement, but Charlie does not.
---
## Why Use `PASSING` with `JSON_TABLE()`?
1. **Efficiency:** By performing calculations and filtering directly within the `JSON_TABLE()` call, you avoid the need for additional queries or complex logic.
2. **Dynamic Comparisons:** The `PASSING` clause allows you to dynamically set thresholds or conditions, making your queries more flexible.
3. **Readability:** Combining data extraction and calculations into a single query makes your code easier to understand and maintain.
---
## Conclusion
PostgreSQL's `JSON_TABLE()` function, combined with the `PASSING` clause, provides a powerful way to process JSON data efficiently. By defining variables and performing calculations directly within the query, you can simplify your workflow and improve performance.
Whether you're filtering products, identifying discounts, or analyzing student data, this approach ensures that your JSON data is both accessible and actionable. Try experimenting with these examples in your own PostgreSQL environment to see how they work!
