prax_query/
json.rs

1//! JSON and document operations support.
2//!
3//! This module provides types for working with JSON columns and document
4//! operations across different database backends.
5//!
6//! # Supported Features
7//!
8//! | Feature           | PostgreSQL | MySQL    | SQLite   | MSSQL       | MongoDB     |
9//! |-------------------|------------|----------|----------|-------------|-------------|
10//! | JSON column type  | ✅ JSONB   | ✅ JSON  | ✅ JSON  | ✅          | ✅ Native   |
11//! | JSON path queries | ✅ @>, ->  | ✅ ->, ->>| ✅ ->, ->>| ✅ JSON_VALUE| ✅ Dot     |
12//! | JSON indexing     | ✅ GIN     | ✅ Gen cols| ❌      | ✅          | ✅ Native   |
13//! | JSON aggregation  | ✅         | ✅       | ✅       | ✅          | ✅          |
14//! | Array operations  | ✅         | ✅       | ❌       | ✅          | ✅ Native   |
15//!
16//! # Example Usage
17//!
18//! ```rust,ignore
19//! use prax_query::json::{JsonPath, JsonOp, JsonFilter};
20//!
21//! // Path query
22//! let filter = JsonPath::new("metadata")
23//!     .field("role")
24//!     .equals("admin");
25//!
26//! // JSON mutation
27//! let update = JsonOp::set("metadata", JsonPath::new("$.settings.theme"), "dark");
28//! ```
29
30use serde::{Deserialize, Serialize};
31use serde_json::Value as JsonValue;
32
33use crate::error::{QueryError, QueryResult};
34use crate::filter::FilterValue;
35use crate::sql::DatabaseType;
36
37/// A JSON path expression for navigating JSON documents.
38#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
39pub struct JsonPath {
40    /// The column name containing JSON.
41    pub column: String,
42    /// Path segments (field names or array indices).
43    pub segments: Vec<PathSegment>,
44    /// Whether to return text (::text in PostgreSQL).
45    pub as_text: bool,
46}
47
48/// A segment in a JSON path.
49#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
50pub enum PathSegment {
51    /// Field access (e.g., .name).
52    Field(String),
53    /// Array index access (e.g., [0]).
54    Index(i64),
55    /// Array wildcard (e.g., [*]).
56    Wildcard,
57    /// Recursive descent (e.g., ..).
58    RecursiveDescent,
59}
60
61impl JsonPath {
62    /// Create a new JSON path starting from a column.
63    pub fn new(column: impl Into<String>) -> Self {
64        Self {
65            column: column.into(),
66            segments: Vec::new(),
67            as_text: false,
68        }
69    }
70
71    /// Create a path from a JSONPath string (e.g., "$.user.name").
72    pub fn from_path(column: impl Into<String>, path: &str) -> Self {
73        let mut json_path = Self::new(column);
74
75        // Parse simple JSONPath syntax
76        let path = path.trim_start_matches('$').trim_start_matches('.');
77
78        for segment in path.split('.') {
79            if segment.is_empty() {
80                continue;
81            }
82
83            // Check if segment contains array index like "addresses[0]"
84            if let Some(bracket_pos) = segment.find('[') {
85                // Extract field name before bracket
86                let field_name = &segment[..bracket_pos];
87                if !field_name.is_empty() {
88                    json_path.segments.push(PathSegment::Field(field_name.to_string()));
89                }
90
91                // Extract index
92                if let Some(end_pos) = segment.find(']') {
93                    let idx_str = &segment[bracket_pos + 1..end_pos];
94                    if idx_str == "*" {
95                        json_path.segments.push(PathSegment::Wildcard);
96                    } else if let Ok(i) = idx_str.parse::<i64>() {
97                        json_path.segments.push(PathSegment::Index(i));
98                    }
99                }
100            } else {
101                json_path.segments.push(PathSegment::Field(segment.to_string()));
102            }
103        }
104
105        json_path
106    }
107
108    /// Add a field access segment.
109    pub fn field(mut self, name: impl Into<String>) -> Self {
110        self.segments.push(PathSegment::Field(name.into()));
111        self
112    }
113
114    /// Add an array index segment.
115    pub fn index(mut self, idx: i64) -> Self {
116        self.segments.push(PathSegment::Index(idx));
117        self
118    }
119
120    /// Add an array wildcard segment.
121    pub fn all(mut self) -> Self {
122        self.segments.push(PathSegment::Wildcard);
123        self
124    }
125
126    /// Return the value as text instead of JSON.
127    pub fn text(mut self) -> Self {
128        self.as_text = true;
129        self
130    }
131
132    /// Convert to PostgreSQL JSON path expression.
133    pub fn to_postgres_expr(&self) -> String {
134        let mut expr = self.column.clone();
135
136        for segment in &self.segments {
137            match segment {
138                PathSegment::Field(name) => {
139                    if self.as_text && self.segments.last() == Some(segment) {
140                        expr.push_str(" ->> '");
141                    } else {
142                        expr.push_str(" -> '");
143                    }
144                    expr.push_str(name);
145                    expr.push('\'');
146                }
147                PathSegment::Index(idx) => {
148                    if self.as_text && self.segments.last() == Some(segment) {
149                        expr.push_str(" ->> ");
150                    } else {
151                        expr.push_str(" -> ");
152                    }
153                    expr.push_str(&idx.to_string());
154                }
155                PathSegment::Wildcard => {
156                    // PostgreSQL doesn't directly support [*] in -> operators
157                    // Use jsonb_array_elements for this
158                    expr = format!("jsonb_array_elements({})", expr);
159                }
160                PathSegment::RecursiveDescent => {
161                    // Use jsonb_path_query for recursive descent
162                    expr = format!("jsonb_path_query({}, '$.**')", expr);
163                }
164            }
165        }
166
167        expr
168    }
169
170    /// Convert to MySQL JSON path expression.
171    pub fn to_mysql_expr(&self) -> String {
172        let path = self.to_jsonpath_string();
173
174        if self.as_text {
175            format!("JSON_UNQUOTE(JSON_EXTRACT({}, '{}'))", self.column, path)
176        } else {
177            format!("JSON_EXTRACT({}, '{}')", self.column, path)
178        }
179    }
180
181    /// Convert to SQLite JSON path expression.
182    pub fn to_sqlite_expr(&self) -> String {
183        let path = self.to_jsonpath_string();
184
185        if self.as_text {
186            format!("json_extract({}, '{}')", self.column, path)
187        } else {
188            format!("json({}, '{}')", self.column, path)
189        }
190    }
191
192    /// Convert to MSSQL JSON path expression.
193    pub fn to_mssql_expr(&self) -> String {
194        let path = self.to_jsonpath_string();
195
196        if self.as_text {
197            format!("JSON_VALUE({}, '{}')", self.column, path)
198        } else {
199            format!("JSON_QUERY({}, '{}')", self.column, path)
200        }
201    }
202
203    /// Convert to standard JSONPath string.
204    pub fn to_jsonpath_string(&self) -> String {
205        let mut path = String::from("$");
206
207        for segment in &self.segments {
208            match segment {
209                PathSegment::Field(name) => {
210                    path.push('.');
211                    path.push_str(name);
212                }
213                PathSegment::Index(idx) => {
214                    path.push('[');
215                    path.push_str(&idx.to_string());
216                    path.push(']');
217                }
218                PathSegment::Wildcard => {
219                    path.push_str("[*]");
220                }
221                PathSegment::RecursiveDescent => {
222                    path.push_str("..");
223                }
224            }
225        }
226
227        path
228    }
229
230    /// Convert to MongoDB dot notation.
231    pub fn to_mongodb_path(&self) -> String {
232        let mut parts = vec![self.column.clone()];
233
234        for segment in &self.segments {
235            match segment {
236                PathSegment::Field(name) => parts.push(name.clone()),
237                PathSegment::Index(idx) => parts.push(idx.to_string()),
238                PathSegment::Wildcard => parts.push("$".to_string()),
239                PathSegment::RecursiveDescent => {} // MongoDB uses different syntax
240            }
241        }
242
243        parts.join(".")
244    }
245
246    /// Convert to SQL expression for the specified database.
247    pub fn to_sql(&self, db_type: DatabaseType) -> String {
248        match db_type {
249            DatabaseType::PostgreSQL => self.to_postgres_expr(),
250            DatabaseType::MySQL => self.to_mysql_expr(),
251            DatabaseType::SQLite => self.to_sqlite_expr(),
252            DatabaseType::MSSQL => self.to_mssql_expr(),
253        }
254    }
255}
256
257/// A JSON filter operation.
258#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
259pub enum JsonFilter {
260    /// Check if path equals a value.
261    Equals(JsonPath, JsonValue),
262    /// Check if path does not equal a value.
263    NotEquals(JsonPath, JsonValue),
264    /// Check if JSON contains another JSON value (PostgreSQL @>).
265    Contains(String, JsonValue),
266    /// Check if JSON is contained by another JSON value (PostgreSQL <@).
267    ContainedBy(String, JsonValue),
268    /// Check if any of the keys exist (PostgreSQL ?|).
269    HasAnyKey(String, Vec<String>),
270    /// Check if all of the keys exist (PostgreSQL ?&).
271    HasAllKeys(String, Vec<String>),
272    /// Check if a key exists (PostgreSQL ?).
273    HasKey(String, String),
274    /// Check if path value is greater than.
275    GreaterThan(JsonPath, JsonValue),
276    /// Check if path value is less than.
277    LessThan(JsonPath, JsonValue),
278    /// Check if path exists.
279    Exists(JsonPath),
280    /// Check if path is null.
281    IsNull(JsonPath),
282    /// Check if path is not null.
283    IsNotNull(JsonPath),
284    /// Check if array contains value.
285    ArrayContains(JsonPath, JsonValue),
286    /// Check value using JSONPath predicate (PostgreSQL @?).
287    PathMatch(String, String),
288}
289
290impl JsonFilter {
291    /// Create an equals filter.
292    pub fn equals(path: JsonPath, value: impl Into<JsonValue>) -> Self {
293        Self::Equals(path, value.into())
294    }
295
296    /// Create a contains filter.
297    pub fn contains(column: impl Into<String>, value: impl Into<JsonValue>) -> Self {
298        Self::Contains(column.into(), value.into())
299    }
300
301    /// Create a has key filter.
302    pub fn has_key(column: impl Into<String>, key: impl Into<String>) -> Self {
303        Self::HasKey(column.into(), key.into())
304    }
305
306    /// Create an exists filter.
307    pub fn exists(path: JsonPath) -> Self {
308        Self::Exists(path)
309    }
310
311    /// Generate PostgreSQL SQL for this filter.
312    pub fn to_postgres_sql(&self) -> (String, Vec<FilterValue>) {
313        let mut params = Vec::new();
314
315        let sql = match self {
316            Self::Equals(path, value) => {
317                let expr = path.to_postgres_expr();
318                params.push(FilterValue::Json(value.clone()));
319                format!("{} = $1::jsonb", expr)
320            }
321            Self::NotEquals(path, value) => {
322                let expr = path.to_postgres_expr();
323                params.push(FilterValue::Json(value.clone()));
324                format!("{} <> $1::jsonb", expr)
325            }
326            Self::Contains(col, value) => {
327                params.push(FilterValue::Json(value.clone()));
328                format!("{} @> $1::jsonb", col)
329            }
330            Self::ContainedBy(col, value) => {
331                params.push(FilterValue::Json(value.clone()));
332                format!("{} <@ $1::jsonb", col)
333            }
334            Self::HasKey(col, key) => {
335                params.push(FilterValue::String(key.clone()));
336                format!("{} ? $1", col)
337            }
338            Self::HasAnyKey(col, keys) => {
339                let placeholders: Vec<String> = (1..=keys.len())
340                    .map(|i| format!("${}", i))
341                    .collect();
342                for key in keys {
343                    params.push(FilterValue::String(key.clone()));
344                }
345                format!("{} ?| ARRAY[{}]", col, placeholders.join(", "))
346            }
347            Self::HasAllKeys(col, keys) => {
348                let placeholders: Vec<String> = (1..=keys.len())
349                    .map(|i| format!("${}", i))
350                    .collect();
351                for key in keys {
352                    params.push(FilterValue::String(key.clone()));
353                }
354                format!("{} ?& ARRAY[{}]", col, placeholders.join(", "))
355            }
356            Self::GreaterThan(path, value) => {
357                let expr = path.to_postgres_expr();
358                params.push(FilterValue::Json(value.clone()));
359                format!("({})::numeric > ($1::jsonb)::numeric", expr)
360            }
361            Self::LessThan(path, value) => {
362                let expr = path.to_postgres_expr();
363                params.push(FilterValue::Json(value.clone()));
364                format!("({})::numeric < ($1::jsonb)::numeric", expr)
365            }
366            Self::Exists(path) => {
367                format!("{} IS NOT NULL", path.to_postgres_expr())
368            }
369            Self::IsNull(path) => {
370                format!("{} IS NULL", path.to_postgres_expr())
371            }
372            Self::IsNotNull(path) => {
373                format!("{} IS NOT NULL", path.to_postgres_expr())
374            }
375            Self::ArrayContains(path, value) => {
376                params.push(FilterValue::Json(value.clone()));
377                format!("{} @> $1::jsonb", path.to_postgres_expr())
378            }
379            Self::PathMatch(col, predicate) => {
380                params.push(FilterValue::String(predicate.clone()));
381                format!("{} @? $1::jsonpath", col)
382            }
383        };
384
385        (sql, params)
386    }
387
388    /// Generate MySQL SQL for this filter.
389    pub fn to_mysql_sql(&self) -> (String, Vec<FilterValue>) {
390        let mut params = Vec::new();
391
392        let sql = match self {
393            Self::Equals(path, value) => {
394                let expr = path.to_mysql_expr();
395                params.push(FilterValue::Json(value.clone()));
396                format!("{} = CAST(? AS JSON)", expr)
397            }
398            Self::NotEquals(path, value) => {
399                let expr = path.to_mysql_expr();
400                params.push(FilterValue::Json(value.clone()));
401                format!("{} <> CAST(? AS JSON)", expr)
402            }
403            Self::Contains(col, value) => {
404                params.push(FilterValue::Json(value.clone()));
405                format!("JSON_CONTAINS({}, ?)", col)
406            }
407            Self::HasKey(col, key) => {
408                params.push(FilterValue::String(format!("$.{}", key)));
409                format!("JSON_CONTAINS_PATH({}, 'one', ?)", col)
410            }
411            Self::Exists(path) => {
412                format!("{} IS NOT NULL", path.to_mysql_expr())
413            }
414            Self::IsNull(path) => {
415                format!("{} IS NULL", path.to_mysql_expr())
416            }
417            Self::IsNotNull(path) => {
418                format!("{} IS NOT NULL", path.to_mysql_expr())
419            }
420            Self::ArrayContains(path, value) => {
421                params.push(FilterValue::Json(value.clone()));
422                format!("JSON_CONTAINS({}, ?)", path.column)
423            }
424            _ => "1=1".to_string(), // Unsupported filter
425        };
426
427        (sql, params)
428    }
429
430    /// Generate SQL for the specified database.
431    pub fn to_sql(&self, db_type: DatabaseType) -> QueryResult<(String, Vec<FilterValue>)> {
432        match db_type {
433            DatabaseType::PostgreSQL => Ok(self.to_postgres_sql()),
434            DatabaseType::MySQL => Ok(self.to_mysql_sql()),
435            DatabaseType::SQLite => {
436                // SQLite has limited JSON support
437                let (sql, params) = match self {
438                    Self::Equals(path, value) => {
439                        let expr = path.to_sqlite_expr();
440                        (format!("{} = json(?)", expr), vec![FilterValue::Json(value.clone())])
441                    }
442                    Self::IsNull(path) => {
443                        (format!("{} IS NULL", path.to_sqlite_expr()), vec![])
444                    }
445                    Self::IsNotNull(path) => {
446                        (format!("{} IS NOT NULL", path.to_sqlite_expr()), vec![])
447                    }
448                    _ => return Err(QueryError::unsupported("This JSON filter is not supported in SQLite")),
449                };
450                Ok((sql, params))
451            }
452            DatabaseType::MSSQL => {
453                let (sql, params) = match self {
454                    Self::Equals(path, value) => {
455                        let expr = path.to_mssql_expr();
456                        (format!("{} = ?", expr), vec![FilterValue::Json(value.clone())])
457                    }
458                    Self::IsNull(path) => {
459                        (format!("{} IS NULL", path.to_mssql_expr()), vec![])
460                    }
461                    Self::IsNotNull(path) => {
462                        (format!("{} IS NOT NULL", path.to_mssql_expr()), vec![])
463                    }
464                    _ => return Err(QueryError::unsupported("This JSON filter is not supported in MSSQL")),
465                };
466                Ok((sql, params))
467            }
468        }
469    }
470}
471
472/// JSON mutation operations.
473#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
474pub enum JsonOp {
475    /// Set a value at a path.
476    Set { column: String, path: String, value: JsonValue },
477    /// Insert a value at a path (only if not exists).
478    Insert { column: String, path: String, value: JsonValue },
479    /// Replace a value at a path (only if exists).
480    Replace { column: String, path: String, value: JsonValue },
481    /// Remove a key/element.
482    Remove { column: String, path: String },
483    /// Append to an array.
484    ArrayAppend { column: String, path: String, value: JsonValue },
485    /// Prepend to an array.
486    ArrayPrepend { column: String, path: String, value: JsonValue },
487    /// Merge two JSON objects.
488    Merge { column: String, value: JsonValue },
489    /// Increment a numeric value.
490    Increment { column: String, path: String, amount: f64 },
491}
492
493impl JsonOp {
494    /// Create a set operation.
495    pub fn set(column: impl Into<String>, path: impl Into<String>, value: impl Into<JsonValue>) -> Self {
496        Self::Set {
497            column: column.into(),
498            path: path.into(),
499            value: value.into(),
500        }
501    }
502
503    /// Create an insert operation.
504    pub fn insert(column: impl Into<String>, path: impl Into<String>, value: impl Into<JsonValue>) -> Self {
505        Self::Insert {
506            column: column.into(),
507            path: path.into(),
508            value: value.into(),
509        }
510    }
511
512    /// Create a remove operation.
513    pub fn remove(column: impl Into<String>, path: impl Into<String>) -> Self {
514        Self::Remove {
515            column: column.into(),
516            path: path.into(),
517        }
518    }
519
520    /// Create an array append operation.
521    pub fn array_append(column: impl Into<String>, path: impl Into<String>, value: impl Into<JsonValue>) -> Self {
522        Self::ArrayAppend {
523            column: column.into(),
524            path: path.into(),
525            value: value.into(),
526        }
527    }
528
529    /// Create a merge operation.
530    pub fn merge(column: impl Into<String>, value: impl Into<JsonValue>) -> Self {
531        Self::Merge {
532            column: column.into(),
533            value: value.into(),
534        }
535    }
536
537    /// Create an increment operation.
538    pub fn increment(column: impl Into<String>, path: impl Into<String>, amount: f64) -> Self {
539        Self::Increment {
540            column: column.into(),
541            path: path.into(),
542            amount,
543        }
544    }
545
546    /// Generate PostgreSQL SQL expression.
547    pub fn to_postgres_expr(&self) -> (String, Vec<FilterValue>) {
548        let mut params = Vec::new();
549
550        let expr = match self {
551            Self::Set { column, path, value } => {
552                params.push(FilterValue::Json(value.clone()));
553                format!("jsonb_set({}, '{{{}}}', $1::jsonb)", column, path.replace('.', ","))
554            }
555            Self::Insert { column, path, value } => {
556                params.push(FilterValue::Json(value.clone()));
557                format!("jsonb_set({}, '{{{}}}', $1::jsonb, true)", column, path.replace('.', ","))
558            }
559            Self::Replace { column, path, value } => {
560                params.push(FilterValue::Json(value.clone()));
561                format!("jsonb_set({}, '{{{}}}', $1::jsonb, false)", column, path.replace('.', ","))
562            }
563            Self::Remove { column, path } => {
564                format!("{} #- '{{{}}}' ", column, path.replace('.', ","))
565            }
566            Self::ArrayAppend { column, path, value } => {
567                params.push(FilterValue::Json(value.clone()));
568                if path.is_empty() || path == "$" {
569                    format!("{} || $1::jsonb", column)
570                } else {
571                    format!(
572                        "jsonb_set({}, '{{{}}}', ({} -> '{}') || $1::jsonb)",
573                        column, path.replace('.', ","), column, path
574                    )
575                }
576            }
577            Self::ArrayPrepend { column, path, value } => {
578                params.push(FilterValue::Json(value.clone()));
579                if path.is_empty() || path == "$" {
580                    format!("$1::jsonb || {}", column)
581                } else {
582                    format!(
583                        "jsonb_set({}, '{{{}}}', $1::jsonb || ({} -> '{}'))",
584                        column, path.replace('.', ","), column, path
585                    )
586                }
587            }
588            Self::Merge { column, value } => {
589                params.push(FilterValue::Json(value.clone()));
590                format!("{} || $1::jsonb", column)
591            }
592            Self::Increment { column, path, amount } => {
593                params.push(FilterValue::Float(*amount));
594                format!(
595                    "jsonb_set({}, '{{{}}}', to_jsonb((({} -> '{}')::numeric + $1)))",
596                    column, path.replace('.', ","), column, path
597                )
598            }
599        };
600
601        (expr, params)
602    }
603
604    /// Generate MySQL SQL expression.
605    pub fn to_mysql_expr(&self) -> (String, Vec<FilterValue>) {
606        let mut params = Vec::new();
607
608        let expr = match self {
609            Self::Set { column, path, value } => {
610                params.push(FilterValue::Json(value.clone()));
611                format!("JSON_SET({}, '$.{}', CAST(? AS JSON))", column, path)
612            }
613            Self::Insert { column, path, value } => {
614                params.push(FilterValue::Json(value.clone()));
615                format!("JSON_INSERT({}, '$.{}', CAST(? AS JSON))", column, path)
616            }
617            Self::Replace { column, path, value } => {
618                params.push(FilterValue::Json(value.clone()));
619                format!("JSON_REPLACE({}, '$.{}', CAST(? AS JSON))", column, path)
620            }
621            Self::Remove { column, path } => {
622                format!("JSON_REMOVE({}, '$.{}')", column, path)
623            }
624            Self::ArrayAppend { column, path, value } => {
625                params.push(FilterValue::Json(value.clone()));
626                if path.is_empty() || path == "$" {
627                    format!("JSON_ARRAY_APPEND({}, '$', CAST(? AS JSON))", column)
628                } else {
629                    format!("JSON_ARRAY_APPEND({}, '$.{}', CAST(? AS JSON))", column, path)
630                }
631            }
632            Self::Merge { column, value } => {
633                params.push(FilterValue::Json(value.clone()));
634                format!("JSON_MERGE_PATCH({}, CAST(? AS JSON))", column)
635            }
636            _ => column_name_from_op(self).to_string(),
637        };
638
639        (expr, params)
640    }
641
642    /// Generate SQL for the specified database.
643    pub fn to_sql(&self, db_type: DatabaseType) -> QueryResult<(String, Vec<FilterValue>)> {
644        match db_type {
645            DatabaseType::PostgreSQL => Ok(self.to_postgres_expr()),
646            DatabaseType::MySQL => Ok(self.to_mysql_expr()),
647            DatabaseType::SQLite => {
648                match self {
649                Self::Set { column, path, value } => {
650                    Ok((
651                        format!("json_set({}, '$.{}', json(?))", column, path),
652                        vec![FilterValue::Json(value.clone())],
653                    ))
654                }
655                    Self::Remove { column, path } => {
656                        Ok((format!("json_remove({}, '$.{}')", column, path), vec![]))
657                    }
658                    _ => Err(QueryError::unsupported("This JSON operation is not supported in SQLite")),
659                }
660            }
661            DatabaseType::MSSQL => {
662                match self {
663                    Self::Set { column, path, value } => {
664                        Ok((
665                            format!("JSON_MODIFY({}, '$.{}', JSON_QUERY(?))", column, path),
666                            vec![FilterValue::Json(value.clone())],
667                        ))
668                    }
669                    _ => Err(QueryError::unsupported("This JSON operation is not supported in MSSQL")),
670                }
671            }
672        }
673    }
674}
675
676fn column_name_from_op(op: &JsonOp) -> &str {
677    match op {
678        JsonOp::Set { column, .. }
679        | JsonOp::Insert { column, .. }
680        | JsonOp::Replace { column, .. }
681        | JsonOp::Remove { column, .. }
682        | JsonOp::ArrayAppend { column, .. }
683        | JsonOp::ArrayPrepend { column, .. }
684        | JsonOp::Merge { column, .. }
685        | JsonOp::Increment { column, .. } => column,
686    }
687}
688
689/// JSON aggregation operations.
690#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
691pub enum JsonAgg {
692    /// Aggregate rows into a JSON array.
693    ArrayAgg { column: String, distinct: bool, order_by: Option<String> },
694    /// Aggregate rows into a JSON object.
695    ObjectAgg { key_column: String, value_column: String },
696    /// Build a JSON object from key-value pairs.
697    BuildObject { pairs: Vec<(String, String)> },
698    /// Build a JSON array from expressions.
699    BuildArray { elements: Vec<String> },
700}
701
702impl JsonAgg {
703    /// Create an array aggregation.
704    pub fn array_agg(column: impl Into<String>) -> Self {
705        Self::ArrayAgg {
706            column: column.into(),
707            distinct: false,
708            order_by: None,
709        }
710    }
711
712    /// Create an object aggregation.
713    pub fn object_agg(key: impl Into<String>, value: impl Into<String>) -> Self {
714        Self::ObjectAgg {
715            key_column: key.into(),
716            value_column: value.into(),
717        }
718    }
719
720    /// Generate PostgreSQL SQL.
721    pub fn to_postgres_sql(&self) -> String {
722        match self {
723            Self::ArrayAgg { column, distinct, order_by } => {
724                let mut sql = String::from("jsonb_agg(");
725                if *distinct {
726                    sql.push_str("DISTINCT ");
727                }
728                sql.push_str(column);
729                if let Some(order) = order_by {
730                    sql.push_str(" ORDER BY ");
731                    sql.push_str(order);
732                }
733                sql.push(')');
734                sql
735            }
736            Self::ObjectAgg { key_column, value_column } => {
737                format!("jsonb_object_agg({}, {})", key_column, value_column)
738            }
739            Self::BuildObject { pairs } => {
740                let args: Vec<String> = pairs
741                    .iter()
742                    .flat_map(|(k, v)| vec![format!("'{}'", k), v.clone()])
743                    .collect();
744                format!("jsonb_build_object({})", args.join(", "))
745            }
746            Self::BuildArray { elements } => {
747                format!("jsonb_build_array({})", elements.join(", "))
748            }
749        }
750    }
751
752    /// Generate MySQL SQL.
753    pub fn to_mysql_sql(&self) -> String {
754        match self {
755            Self::ArrayAgg { column, .. } => {
756                format!("JSON_ARRAYAGG({})", column)
757            }
758            Self::ObjectAgg { key_column, value_column } => {
759                format!("JSON_OBJECTAGG({}, {})", key_column, value_column)
760            }
761            Self::BuildObject { pairs } => {
762                let args: Vec<String> = pairs
763                    .iter()
764                    .flat_map(|(k, v)| vec![format!("'{}'", k), v.clone()])
765                    .collect();
766                format!("JSON_OBJECT({})", args.join(", "))
767            }
768            Self::BuildArray { elements } => {
769                format!("JSON_ARRAY({})", elements.join(", "))
770            }
771        }
772    }
773
774    /// Generate SQL for the specified database.
775    pub fn to_sql(&self, db_type: DatabaseType) -> String {
776        match db_type {
777            DatabaseType::PostgreSQL => self.to_postgres_sql(),
778            DatabaseType::MySQL => self.to_mysql_sql(),
779            DatabaseType::SQLite => {
780                match self {
781                    Self::ArrayAgg { column, .. } => format!("json_group_array({})", column),
782                    Self::ObjectAgg { key_column, value_column } => {
783                        format!("json_group_object({}, {})", key_column, value_column)
784                    }
785                    Self::BuildObject { pairs } => {
786                        let args: Vec<String> = pairs
787                            .iter()
788                            .flat_map(|(k, v)| vec![format!("'{}'", k), v.clone()])
789                            .collect();
790                        format!("json_object({})", args.join(", "))
791                    }
792                    Self::BuildArray { elements } => {
793                        format!("json_array({})", elements.join(", "))
794                    }
795                }
796            }
797            DatabaseType::MSSQL => {
798                // MSSQL uses FOR JSON
799                match self {
800                    Self::ArrayAgg { .. } => "-- Use FOR JSON AUTO".to_string(),
801                    _ => "-- Use FOR JSON PATH".to_string(),
802                }
803            }
804        }
805    }
806}
807
808/// MongoDB document operations.
809pub mod mongodb {
810    use serde::{Deserialize, Serialize};
811    use serde_json::Value as JsonValue;
812
813    /// MongoDB update operators for documents.
814    #[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
815    pub enum UpdateOp {
816        /// Set a field value ($set).
817        Set(String, JsonValue),
818        /// Unset a field ($unset).
819        Unset(String),
820        /// Increment a numeric field ($inc).
821        Inc(String, f64),
822        /// Multiply a numeric field ($mul).
823        Mul(String, f64),
824        /// Rename a field ($rename).
825        Rename(String, String),
826        /// Set field to current date ($currentDate).
827        CurrentDate(String),
828        /// Set minimum value ($min).
829        Min(String, JsonValue),
830        /// Set maximum value ($max).
831        Max(String, JsonValue),
832        /// Set on insert only ($setOnInsert).
833        SetOnInsert(String, JsonValue),
834    }
835
836    /// MongoDB array update operators.
837    #[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
838    pub enum ArrayOp {
839        /// Push element to array ($push).
840        Push { field: String, value: JsonValue, position: Option<i32> },
841        /// Push all elements ($push with $each).
842        PushAll { field: String, values: Vec<JsonValue> },
843        /// Pull element from array ($pull).
844        Pull { field: String, value: JsonValue },
845        /// Pull all matching elements ($pullAll).
846        PullAll { field: String, values: Vec<JsonValue> },
847        /// Add to set if not exists ($addToSet).
848        AddToSet { field: String, value: JsonValue },
849        /// Add all to set ($addToSet with $each).
850        AddToSetAll { field: String, values: Vec<JsonValue> },
851        /// Remove first or last element ($pop).
852        Pop { field: String, first: bool },
853    }
854
855    impl UpdateOp {
856        /// Create a $set operation.
857        pub fn set(field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
858            Self::Set(field.into(), value.into())
859        }
860
861        /// Create an $unset operation.
862        pub fn unset(field: impl Into<String>) -> Self {
863            Self::Unset(field.into())
864        }
865
866        /// Create an $inc operation.
867        pub fn inc(field: impl Into<String>, amount: f64) -> Self {
868            Self::Inc(field.into(), amount)
869        }
870
871        /// Convert to BSON document.
872        pub fn to_bson(&self) -> serde_json::Value {
873            match self {
874                Self::Set(field, value) => serde_json::json!({ "$set": { field: value } }),
875                Self::Unset(field) => serde_json::json!({ "$unset": { field: "" } }),
876                Self::Inc(field, amount) => serde_json::json!({ "$inc": { field: amount } }),
877                Self::Mul(field, amount) => serde_json::json!({ "$mul": { field: amount } }),
878                Self::Rename(old, new) => serde_json::json!({ "$rename": { old: new } }),
879                Self::CurrentDate(field) => serde_json::json!({ "$currentDate": { field: true } }),
880                Self::Min(field, value) => serde_json::json!({ "$min": { field: value } }),
881                Self::Max(field, value) => serde_json::json!({ "$max": { field: value } }),
882                Self::SetOnInsert(field, value) => serde_json::json!({ "$setOnInsert": { field: value } }),
883            }
884        }
885    }
886
887    impl ArrayOp {
888        /// Create a $push operation.
889        pub fn push(field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
890            Self::Push {
891                field: field.into(),
892                value: value.into(),
893                position: None,
894            }
895        }
896
897        /// Create a $pull operation.
898        pub fn pull(field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
899            Self::Pull {
900                field: field.into(),
901                value: value.into(),
902            }
903        }
904
905        /// Create an $addToSet operation.
906        pub fn add_to_set(field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
907            Self::AddToSet {
908                field: field.into(),
909                value: value.into(),
910            }
911        }
912
913        /// Convert to BSON document.
914        pub fn to_bson(&self) -> serde_json::Value {
915            match self {
916                Self::Push { field, value, position } => {
917                    if let Some(pos) = position {
918                        serde_json::json!({
919                            "$push": { field: { "$each": [value], "$position": pos } }
920                        })
921                    } else {
922                        serde_json::json!({ "$push": { field: value } })
923                    }
924                }
925                Self::PushAll { field, values } => {
926                    serde_json::json!({ "$push": { field: { "$each": values } } })
927                }
928                Self::Pull { field, value } => {
929                    serde_json::json!({ "$pull": { field: value } })
930                }
931                Self::PullAll { field, values } => {
932                    serde_json::json!({ "$pullAll": { field: values } })
933                }
934                Self::AddToSet { field, value } => {
935                    serde_json::json!({ "$addToSet": { field: value } })
936                }
937                Self::AddToSetAll { field, values } => {
938                    serde_json::json!({ "$addToSet": { field: { "$each": values } } })
939                }
940                Self::Pop { field, first } => {
941                    let direction = if *first { -1 } else { 1 };
942                    serde_json::json!({ "$pop": { field: direction } })
943                }
944            }
945        }
946    }
947
948    /// Builder for MongoDB update operations.
949    #[derive(Debug, Clone, Default)]
950    pub struct UpdateBuilder {
951        ops: Vec<serde_json::Value>,
952    }
953
954    impl UpdateBuilder {
955        /// Create a new update builder.
956        pub fn new() -> Self {
957            Self::default()
958        }
959
960        /// Add a $set operation.
961        pub fn set(mut self, field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
962            self.ops.push(UpdateOp::set(field, value).to_bson());
963            self
964        }
965
966        /// Add an $unset operation.
967        pub fn unset(mut self, field: impl Into<String>) -> Self {
968            self.ops.push(UpdateOp::unset(field).to_bson());
969            self
970        }
971
972        /// Add an $inc operation.
973        pub fn inc(mut self, field: impl Into<String>, amount: f64) -> Self {
974            self.ops.push(UpdateOp::inc(field, amount).to_bson());
975            self
976        }
977
978        /// Add a $push operation.
979        pub fn push(mut self, field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
980            self.ops.push(ArrayOp::push(field, value).to_bson());
981            self
982        }
983
984        /// Add a $pull operation.
985        pub fn pull(mut self, field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
986            self.ops.push(ArrayOp::pull(field, value).to_bson());
987            self
988        }
989
990        /// Add an $addToSet operation.
991        pub fn add_to_set(mut self, field: impl Into<String>, value: impl Into<JsonValue>) -> Self {
992            self.ops.push(ArrayOp::add_to_set(field, value).to_bson());
993            self
994        }
995
996        /// Build the combined update document.
997        pub fn build(self) -> serde_json::Value {
998            // Merge all operations into a single document
999            let mut result = serde_json::Map::new();
1000
1001            for op in self.ops {
1002                if let serde_json::Value::Object(map) = op {
1003                    for (key, value) in map {
1004                        if let Some(existing) = result.get_mut(&key) {
1005                            if let (serde_json::Value::Object(existing_map), serde_json::Value::Object(new_map)) =
1006                                (existing, value)
1007                            {
1008                                for (k, v) in new_map {
1009                                    existing_map.insert(k, v);
1010                                }
1011                            }
1012                        } else {
1013                            result.insert(key, value);
1014                        }
1015                    }
1016                }
1017            }
1018
1019            serde_json::Value::Object(result)
1020        }
1021    }
1022
1023    /// Helper to create an update builder.
1024    pub fn update() -> UpdateBuilder {
1025        UpdateBuilder::new()
1026    }
1027}
1028
1029/// JSON index definition.
1030#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1031pub struct JsonIndex {
1032    /// Index name.
1033    pub name: String,
1034    /// Table name.
1035    pub table: String,
1036    /// JSON column name.
1037    pub column: String,
1038    /// JSON path to index.
1039    pub path: Option<String>,
1040    /// Whether this is a GIN index (PostgreSQL).
1041    pub gin: bool,
1042}
1043
1044impl JsonIndex {
1045    /// Create a new JSON index builder.
1046    pub fn builder(name: impl Into<String>) -> JsonIndexBuilder {
1047        JsonIndexBuilder::new(name)
1048    }
1049
1050    /// Generate PostgreSQL CREATE INDEX SQL.
1051    pub fn to_postgres_sql(&self) -> String {
1052        if let Some(ref path) = self.path {
1053            // Index specific path
1054            format!(
1055                "CREATE INDEX {} ON {} USING {} (({} -> '{}'));",
1056                self.name,
1057                self.table,
1058                if self.gin { "GIN" } else { "BTREE" },
1059                self.column,
1060                path
1061            )
1062        } else {
1063            // Index whole column
1064            format!(
1065                "CREATE INDEX {} ON {} USING GIN ({});",
1066                self.name, self.table, self.column
1067            )
1068        }
1069    }
1070
1071    /// Generate MySQL generated column + index SQL.
1072    pub fn to_mysql_sql(&self) -> Vec<String> {
1073        if let Some(ref path) = self.path {
1074            let gen_col = format!("{}_{}_{}", self.table, self.column, path.replace('.', "_"));
1075            vec![
1076                format!(
1077                    "ALTER TABLE {} ADD COLUMN {} VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT({}, '$.{}'))) STORED;",
1078                    self.table, gen_col, self.column, path
1079                ),
1080                format!("CREATE INDEX {} ON {} ({});", self.name, self.table, gen_col),
1081            ]
1082        } else {
1083            vec!["-- MySQL requires generated columns for JSON indexing".to_string()]
1084        }
1085    }
1086}
1087
1088/// Builder for JSON indexes.
1089#[derive(Debug, Clone)]
1090pub struct JsonIndexBuilder {
1091    name: String,
1092    table: Option<String>,
1093    column: Option<String>,
1094    path: Option<String>,
1095    gin: bool,
1096}
1097
1098impl JsonIndexBuilder {
1099    /// Create a new builder.
1100    pub fn new(name: impl Into<String>) -> Self {
1101        Self {
1102            name: name.into(),
1103            table: None,
1104            column: None,
1105            path: None,
1106            gin: true,
1107        }
1108    }
1109
1110    /// Set the table name.
1111    pub fn on_table(mut self, table: impl Into<String>) -> Self {
1112        self.table = Some(table.into());
1113        self
1114    }
1115
1116    /// Set the JSON column.
1117    pub fn column(mut self, column: impl Into<String>) -> Self {
1118        self.column = Some(column.into());
1119        self
1120    }
1121
1122    /// Set the path to index.
1123    pub fn path(mut self, path: impl Into<String>) -> Self {
1124        self.path = Some(path.into());
1125        self
1126    }
1127
1128    /// Use GIN index (PostgreSQL).
1129    pub fn gin(mut self) -> Self {
1130        self.gin = true;
1131        self
1132    }
1133
1134    /// Use BTREE index.
1135    pub fn btree(mut self) -> Self {
1136        self.gin = false;
1137        self
1138    }
1139
1140    /// Build the index definition.
1141    pub fn build(self) -> QueryResult<JsonIndex> {
1142        let table = self.table.ok_or_else(|| {
1143            QueryError::invalid_input("table", "Must specify table with on_table()")
1144        })?;
1145        let column = self.column.ok_or_else(|| {
1146            QueryError::invalid_input("column", "Must specify column with column()")
1147        })?;
1148
1149        Ok(JsonIndex {
1150            name: self.name,
1151            table,
1152            column,
1153            path: self.path,
1154            gin: self.gin,
1155        })
1156    }
1157}
1158
1159#[cfg(test)]
1160mod tests {
1161    use super::*;
1162
1163    #[test]
1164    fn test_json_path_basic() {
1165        let path = JsonPath::new("metadata")
1166            .field("user")
1167            .field("name");
1168
1169        assert_eq!(path.to_jsonpath_string(), "$.user.name");
1170    }
1171
1172    #[test]
1173    fn test_json_path_with_index() {
1174        let path = JsonPath::new("items")
1175            .field("tags")
1176            .index(0);
1177
1178        assert_eq!(path.to_jsonpath_string(), "$.tags[0]");
1179    }
1180
1181    #[test]
1182    fn test_json_path_from_string() {
1183        let path = JsonPath::from_path("data", "$.user.addresses[0].city");
1184
1185        assert_eq!(path.segments.len(), 4);
1186        assert_eq!(path.to_jsonpath_string(), "$.user.addresses[0].city");
1187    }
1188
1189    #[test]
1190    fn test_postgres_path_expr() {
1191        let path = JsonPath::new("metadata")
1192            .field("role")
1193            .text();
1194
1195        let expr = path.to_postgres_expr();
1196        assert!(expr.contains(" ->> "));
1197    }
1198
1199    #[test]
1200    fn test_mysql_path_expr() {
1201        let path = JsonPath::new("data")
1202            .field("name")
1203            .text();
1204
1205        let expr = path.to_mysql_expr();
1206        assert!(expr.contains("JSON_UNQUOTE"));
1207        assert!(expr.contains("JSON_EXTRACT"));
1208    }
1209
1210    #[test]
1211    fn test_mongodb_path() {
1212        let path = JsonPath::new("address")
1213            .field("city");
1214
1215        assert_eq!(path.to_mongodb_path(), "address.city");
1216    }
1217
1218    #[test]
1219    fn test_json_filter_contains() {
1220        let filter = JsonFilter::contains("metadata", serde_json::json!({"role": "admin"}));
1221        let (sql, params) = filter.to_postgres_sql();
1222
1223        assert!(sql.contains("@>"));
1224        assert_eq!(params.len(), 1);
1225    }
1226
1227    #[test]
1228    fn test_json_filter_has_key() {
1229        let filter = JsonFilter::has_key("settings", "theme");
1230        let (sql, params) = filter.to_postgres_sql();
1231
1232        assert!(sql.contains("?"));
1233        assert_eq!(params.len(), 1);
1234    }
1235
1236    #[test]
1237    fn test_json_op_set() {
1238        let op = JsonOp::set("metadata", "theme", serde_json::json!("dark"));
1239        let (expr, params) = op.to_postgres_expr();
1240
1241        assert!(expr.contains("jsonb_set"));
1242        assert_eq!(params.len(), 1);
1243    }
1244
1245    #[test]
1246    fn test_json_op_remove() {
1247        let op = JsonOp::remove("metadata", "old_field");
1248        let (expr, _) = op.to_postgres_expr();
1249
1250        assert!(expr.contains("#-"));
1251    }
1252
1253    #[test]
1254    fn test_json_op_array_append() {
1255        let op = JsonOp::array_append("tags", "$", serde_json::json!("new_tag"));
1256        let (expr, params) = op.to_postgres_expr();
1257
1258        assert!(expr.contains("||"));
1259        assert_eq!(params.len(), 1);
1260    }
1261
1262    #[test]
1263    fn test_json_op_merge() {
1264        let op = JsonOp::merge("settings", serde_json::json!({"new_key": "value"}));
1265        let (expr, params) = op.to_postgres_expr();
1266
1267        assert!(expr.contains("||"));
1268        assert_eq!(params.len(), 1);
1269    }
1270
1271    #[test]
1272    fn test_json_agg_array() {
1273        let agg = JsonAgg::array_agg("name");
1274        let sql = agg.to_postgres_sql();
1275
1276        assert_eq!(sql, "jsonb_agg(name)");
1277    }
1278
1279    #[test]
1280    fn test_json_agg_object() {
1281        let agg = JsonAgg::object_agg("key", "value");
1282        let sql = agg.to_postgres_sql();
1283
1284        assert_eq!(sql, "jsonb_object_agg(key, value)");
1285    }
1286
1287    #[test]
1288    fn test_json_index_postgres() {
1289        let index = JsonIndex::builder("users_metadata_idx")
1290            .on_table("users")
1291            .column("metadata")
1292            .gin()
1293            .build()
1294            .unwrap();
1295
1296        let sql = index.to_postgres_sql();
1297        assert!(sql.contains("USING GIN"));
1298    }
1299
1300    #[test]
1301    fn test_json_index_with_path() {
1302        let index = JsonIndex::builder("users_role_idx")
1303            .on_table("users")
1304            .column("metadata")
1305            .path("role")
1306            .btree()
1307            .build()
1308            .unwrap();
1309
1310        let sql = index.to_postgres_sql();
1311        assert!(sql.contains("USING BTREE"));
1312        assert!(sql.contains("-> 'role'"));
1313    }
1314
1315    mod mongodb_tests {
1316        use super::super::mongodb::*;
1317
1318        #[test]
1319        fn test_update_set() {
1320            let op = UpdateOp::set("name", "John");
1321            let bson = op.to_bson();
1322
1323            assert!(bson["$set"]["name"].is_string());
1324        }
1325
1326        #[test]
1327        fn test_update_inc() {
1328            let op = UpdateOp::inc("count", 1.0);
1329            let bson = op.to_bson();
1330
1331            assert_eq!(bson["$inc"]["count"], 1.0);
1332        }
1333
1334        #[test]
1335        fn test_array_push() {
1336            let op = ArrayOp::push("tags", "new_tag");
1337            let bson = op.to_bson();
1338
1339            assert!(bson["$push"]["tags"].is_string());
1340        }
1341
1342        #[test]
1343        fn test_array_add_to_set() {
1344            let op = ArrayOp::add_to_set("roles", "admin");
1345            let bson = op.to_bson();
1346
1347            assert!(bson["$addToSet"]["roles"].is_string());
1348        }
1349
1350        #[test]
1351        fn test_update_builder() {
1352            let update = update()
1353                .set("name", "John")
1354                .inc("visits", 1.0)
1355                .push("tags", "active")
1356                .build();
1357
1358            assert!(update["$set"].is_object());
1359            assert!(update["$inc"].is_object());
1360            assert!(update["$push"].is_object());
1361        }
1362    }
1363}
1364
prax_query/json.rs

prax_query/
json.rs
