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

prax_query/
json.rs
