vantage_sql/primitives/
json_extract.rs

1use std::fmt::{Debug, Display};
2
3use vantage_core::util::IntoVec;
4use vantage_expressions::{Expression, Expressive, ExpressiveEnum};
5
6/// Vendor-aware JSON field extraction.
7///
8/// Accepts a single field or a path of fields. For multi-level paths,
9/// intermediate steps use the JSON-object accessor and the final step
10/// extracts as text.
11///
12/// Renders as (extract as text by default):
13/// - **SQLite:**     `JSON_EXTRACT("col", '$.field')`
14/// - **MySQL:**      `` JSON_UNQUOTE(JSON_EXTRACT(`col`, '$.field')) ``
15/// - **PostgreSQL:** `"col" ->> 'field'` or `"col" -> 'a' ->> 'b'`
16///
17/// With `.as_json()` — return raw JSON instead of text:
18/// - **MySQL:**      `` JSON_EXTRACT(`col`, '$.field') ``
19/// - **PostgreSQL:** `"col" -> 'field'`
20///
21/// # Examples
22///
23/// ```ignore
24/// // Single field
25/// JsonExtract::new(ident("metadata"), "color")
26///
27/// // Nested path
28/// JsonExtract::new(ident("metadata"), ["specs", "voltage"])
29/// ```
30#[derive(Debug, Clone)]
31pub struct JsonExtract<T: Debug + Display + Clone> {
32    source: Expression<T>,
33    path: Vec<String>,
34    /// When true, return the raw JSON value instead of extracting as text.
35    /// Affects MySQL (drops `JSON_UNQUOTE`) and PostgreSQL (all `->` vs final `->>`)
36    as_json: bool,
37}
38
39impl<T: Debug + Display + Clone> JsonExtract<T> {
40    pub fn new(source: impl Expressive<T>, path: impl IntoVec<String>) -> Self {
41        Self {
42            source: source.expr(),
43            path: path.into_vec(),
44            as_json: false,
45        }
46    }
47
48    /// Return raw JSON instead of text (no `JSON_UNQUOTE` in MySQL, `->` in PG).
49    pub fn as_json(mut self) -> Self {
50        self.as_json = true;
51        self
52    }
53}
54
55/// Shorthand for `JsonExtract::new(source, path)`.
56pub fn json_extract<T: Debug + Display + Clone>(
57    source: impl Expressive<T>,
58    path: impl IntoVec<String>,
59) -> JsonExtract<T> {
60    JsonExtract::new(source, path)
61}
62
63/// Helper: create an inline SQL literal (not a bind parameter).
64fn sql_lit<T: Debug + Display + Clone>(s: &str) -> Expression<T> {
65    let escaped = s.replace('\'', "''");
66    Expression::new(format!("'{escaped}'"), vec![])
67}
68
69// -- SQLite: JSON_EXTRACT("col", '$.a.b') ------------------------------------
70
71#[cfg(feature = "sqlite")]
72impl Expressive<crate::sqlite::types::AnySqliteType>
73    for JsonExtract<crate::sqlite::types::AnySqliteType>
74{
75    fn expr(&self) -> Expression<crate::sqlite::types::AnySqliteType> {
76        let json_path = format!("$.{}", self.path.join("."));
77        Expression::new(
78            "JSON_EXTRACT({}, {})",
79            vec![
80                ExpressiveEnum::Nested(self.source.clone()),
81                ExpressiveEnum::Nested(sql_lit(&json_path)),
82            ],
83        )
84    }
85}
86
87// -- MySQL: JSON_UNQUOTE(JSON_EXTRACT(`col`, '$.a.b')) ------------------------
88// The function forms of MySQL's `->>` / `->`; MariaDB has only the functions.
89
90#[cfg(feature = "mysql")]
91impl Expressive<crate::mysql::types::AnyMysqlType>
92    for JsonExtract<crate::mysql::types::AnyMysqlType>
93{
94    fn expr(&self) -> Expression<crate::mysql::types::AnyMysqlType> {
95        let json_path = format!("$.{}", self.path.join("."));
96        let template = if self.as_json {
97            "JSON_EXTRACT({}, {})"
98        } else {
99            "JSON_UNQUOTE(JSON_EXTRACT({}, {}))"
100        };
101        Expression::new(
102            template,
103            vec![
104                ExpressiveEnum::Nested(self.source.clone()),
105                ExpressiveEnum::Nested(sql_lit(&json_path)),
106            ],
107        )
108    }
109}
110
111// -- PostgreSQL: "col"->'a'->>'b' --------------------------------------------
112
113#[cfg(feature = "postgres")]
114impl Expressive<crate::postgres::types::AnyPostgresType>
115    for JsonExtract<crate::postgres::types::AnyPostgresType>
116{
117    fn expr(&self) -> Expression<crate::postgres::types::AnyPostgresType> {
118        // Build chain: source -> 'a' -> 'b' ->> 'last'
119        // All intermediate steps use -> (returns jsonb), final step uses ->> (returns text)
120        assert!(
121            !self.path.is_empty(),
122            "JsonExtract requires at least one path segment"
123        );
124        let mut current = self.source.clone();
125        let last = self.path.len() - 1;
126
127        for (i, field) in self.path.iter().enumerate() {
128            let op = if i == last && !self.as_json {
129                " ->> "
130            } else {
131                " -> "
132            };
133            current = Expression::new(
134                format!("{{}}{op}{{}}"),
135                vec![
136                    ExpressiveEnum::Nested(current),
137                    ExpressiveEnum::Nested(sql_lit(field)),
138                ],
139            );
140        }
141
142        current
143    }
144}
vantage_sql/primitives/json_extract.rs

vantage_sql/primitives/
json_extract.rs
