JSON Functions and Querying
Relevant source files
The following files were used as context for generating this wiki page:
JSON functions in SeaQuery allow developers to interact with JSON data types stored in databases, specifically focusing on advanced PostgreSQL extensions. The primary focus of the current implementation is the JSON_TABLE function, which enables the transformation of JSON data into a relational tabular format directly within a SQL query. This functionality is essential for querying complex, nested JSON structures as if they were standard database tables.
The library provides a type-safe builder pattern to construct these complex JSON queries, ensuring that path expressions, column mappings, and error-handling clauses are correctly formatted for the target database backend.
Sources: src/extension/postgres/func/json_table/mod.rs:1-10, README.md:90-100
PostgreSQL JSON_TABLE Support
The JSON_TABLE function is a powerful PostgreSQL extension that converts JSON data into a set of rows. SeaQuery implements this through the PgFunc::json_table builder, which supports context items, path expressions, and complex column definitions.
Architecture of the JSON_TABLE Builder
The builder follows a fluent API design, allowing users to chain methods to define the structure of the resulting table. It manages the internal state of the query, including parameters passed via the PASSING clause and the recursive structure of NESTED PATH definitions.
The diagram above illustrates the flow of constructing a JSON_TABLE expression using the builder pattern.
Sources: src/extension/postgres/func/json_table/builder.rs:15-30, src/extension/postgres/func/json_table/mod.rs:114-124
Key Components and Data Structures
| Component | Description |
|---|---|
Builder | The main struct used to accumulate JSON_TABLE configuration. |
JsonTableColumn | An enum representing different types of columns: Regular, Ordinality, Exists, or Nested. |
NestedPath | A structure allowing for recursive JSON flattening by defining a new path and a sub-set of columns. |
OnErrorClause | Defines behavior when an error occurs (e.g., ERROR, EMPTY, EMPTY ARRAY). |
Sources: src/extension/postgres/func/json_table/builder.rs:15-24, src/extension/postgres/func/json_table/mod.rs:5-12
Column Definitions and Mapping
SeaQuery supports several types of column mappings within a JSON table. These are defined using specific helper objects rather than chained sub-builders, a change introduced in version 1.0.0-rc.34.
Regular and Ordinality Columns
Regular columns map a JSON path to a specific SQL type. Ordinality columns provide a row numbering mechanism, typically used to keep track of the index of items in a JSON array.
// Example of basic column definition
PgFunc::json_table(Expr::val(json_data), "$[*]")
.for_ordinality("row_number")
.column(json_table::Column::new("name", "text").path("$.name"))
.column(json_table::Column::new("age", "int").path("$.age"))
Sources: src/extension/postgres/func/json_table/mod.rs:24-35, changelog/1.0.0-rc.34.md:52-65
Nested Paths
Nested paths allow the builder to handle complex JSON objects where arrays are nested within other arrays. This creates a parent-child relationship in the resulting rows.
The class diagram shows the recursive relationship where a NestedPath can contain multiple JsonTableColumn variants, including further nested paths.
Sources: src/extension/postgres/func/json_table/builder.rs:135-150, tests/postgres/json_table.rs:77-90
Advanced Querying Features
Parameters and Passing Clause
The PASSING clause allows for dynamic values to be used within the JSON path expression. This is useful for filtering JSON arrays based on external variables.
// Usage of passing parameters
PgFunc::json_table(Expr::val(users_json), "$.users[*] ? (@.id > $min_id)")
.passing(0, "min_id")
.column(json_table::Column::new("user_id", "int").path("$.id"))
Sources: src/extension/postgres/func/json_table/mod.rs:58-65, src/extension/postgres/func/json_table/builder.rs:48-55
Error and Empty Handling
SeaQuery provides methods to specify behavior when a JSON path is not found or when an error occurs during extraction.
- ON EMPTY: Specifies a default value or null when the path does not exist.
- ON ERROR: Specifies behavior (like throwing a SQL error or returning null) when data conversion fails.
| Method | Generated SQL Clause |
|---|---|
error_on_error() | ERROR ON ERROR |
empty_on_error() | EMPTY ON ERROR |
null_on_error() | NULL ON ERROR (on column) |
default_on_empty(val) | DEFAULT 'val' ON EMPTY |
Sources: src/extension/postgres/func/json_table/builder.rs:82-100, tests/postgres/json_table.rs:46-52
Integration with Feature Flags
JSON support is conditionally compiled. To use JSON functions, the with-json feature must be enabled in Cargo.toml. This enables serde_json integration, allowing serde_json::Value to be used directly in expressions.
Sources: Cargo.toml:68, src/lib.rs:58-62
Summary
JSON Functions and Querying in SeaQuery provide a robust interface for PostgreSQL's JSON_TABLE capabilities. Through a structured builder API, developers can define complex mappings, handle nested JSON data, and manage error states while maintaining type safety in Rust. The system is designed to handle both simple data extraction and complex relational transformations of semi-structured data.
Sources: src/extension/postgres/func/json_table/mod.rs:15-20, changelog/1.0.0-rc.34.md:52-60