drizzle_sqlite/
expr.rs

1//! SQLite-specific SQL expressions and JSON helpers.
2//!
3//! This module provides `SQLite` dialect functions and JSON expressions.
4//! For standard SQL expressions, use `drizzle_core::expr`.
5
6#[cfg(not(feature = "std"))]
7use crate::prelude::*;
8use crate::values::SQLiteValue;
9use drizzle_core::{SQL, ToSQL};
10
11/// Wraps a value with the `SQLite` `json()` function, validating and returning JSON text.
12pub fn json<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
13    SQL::func("json", value.to_sql())
14}
15
16/// Wraps a value with the `SQLite` `jsonb()` function, validating and returning JSON in binary format.
17pub fn jsonb<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
18    SQL::func("jsonb", value.to_sql())
19}
20
21/// Create a JSON field equality condition using `SQLite` ->> operator
22///
23/// # Example
24/// ```
25/// # use drizzle_sqlite::expr::json_eq;
26/// # use drizzle_core::SQL;
27/// # use drizzle_sqlite::values::SQLiteValue;
28/// # fn main() {
29/// let column = SQL::<SQLiteValue>::raw("metadata");
30/// let condition = json_eq(column, "theme", "dark");
31/// assert_eq!(condition.sql(), "metadata ->> ? = ?");
32/// # }
33/// ```
34pub fn json_eq<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
35where
36    L: ToSQL<'a, SQLiteValue<'a>>,
37    R: Into<SQLiteValue<'a>>,
38{
39    left.to_sql()
40        .append(SQL::raw(" ->> "))
41        .append(SQL::param(SQLiteValue::from(field)))
42        .append(SQL::raw(" = "))
43        .append(SQL::param(value.into()))
44}
45
46/// Create a JSON field inequality condition
47///
48/// # Example
49/// ```
50/// # use drizzle_sqlite::expr::json_ne;
51/// # use drizzle_core::SQL;
52/// # use drizzle_sqlite::values::SQLiteValue;
53/// # fn main() {
54/// let column = SQL::<SQLiteValue>::raw("metadata");
55/// let condition = json_ne(column, "theme", "light");
56/// assert_eq!(condition.sql(), "metadata ->> ? != ?");
57/// # }
58/// ```
59pub fn json_ne<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
60where
61    L: ToSQL<'a, SQLiteValue<'a>>,
62    R: Into<SQLiteValue<'a>>,
63{
64    left.to_sql()
65        .append(SQL::raw(" ->> "))
66        .append(SQL::param(SQLiteValue::from(field)))
67        .append(SQL::raw(" != "))
68        .append(SQL::param(value.into()))
69}
70
71/// Create a JSON field contains condition using `json_extract`
72///
73/// # Example
74/// ```
75/// # use drizzle_sqlite::expr::json_contains;
76/// # use drizzle_core::SQL;
77/// # use drizzle_sqlite::values::SQLiteValue;
78/// # fn main() {
79/// let column = SQL::<SQLiteValue>::raw("metadata");
80/// let condition = json_contains(column, "$.preferences[0]", "dark_theme");
81/// assert_eq!(condition.sql(), "json_extract( metadata , ? ) = ?");
82/// # }
83/// ```
84pub fn json_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
85where
86    L: ToSQL<'a, SQLiteValue<'a>>,
87    R: Into<SQLiteValue<'a>>,
88{
89    SQL::raw("json_extract(")
90        .append(left.to_sql())
91        .append(SQL::raw(", "))
92        .append(SQL::param(SQLiteValue::from(path)))
93        .append(SQL::raw(") = "))
94        .append(SQL::param(value.into()))
95}
96
97/// Create a JSON field exists condition using `json_type`
98///
99/// # Example
100/// ```
101/// # use drizzle_sqlite::expr::json_exists;
102/// # use drizzle_core::SQL;
103/// # use drizzle_sqlite::values::SQLiteValue;
104/// # fn main() {
105/// let column = SQL::<SQLiteValue>::raw("metadata");
106/// let condition = json_exists(column, "$.theme");
107/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NOT NULL");
108/// # }
109/// ```
110pub fn json_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
111where
112    L: ToSQL<'a, SQLiteValue<'a>>,
113{
114    SQL::raw("json_type(")
115        .append(left.to_sql())
116        .append(SQL::raw(", "))
117        .append(SQL::param(SQLiteValue::from(path)))
118        .append(SQL::raw(") IS NOT NULL"))
119}
120
121/// Create a JSON field does not exist condition
122///
123/// # Example
124/// ```
125/// # use drizzle_sqlite::expr::json_not_exists;
126/// # use drizzle_core::SQL;
127/// # use drizzle_sqlite::values::SQLiteValue;
128/// # fn main() {
129/// let column = SQL::<SQLiteValue>::raw("metadata");
130/// let condition = json_not_exists(column, "$.theme");
131/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NULL");
132/// # }
133/// ```
134pub fn json_not_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
135where
136    L: ToSQL<'a, SQLiteValue<'a>>,
137{
138    SQL::raw("json_type(")
139        .append(left.to_sql())
140        .append(SQL::raw(", "))
141        .append(SQL::param(SQLiteValue::from(path)))
142        .append(SQL::raw(") IS NULL"))
143}
144
145/// Create a JSON array contains value condition
146///
147/// # Example
148/// ```
149/// # use drizzle_sqlite::expr::json_array_contains;
150/// # use drizzle_core::SQL;
151/// # use drizzle_sqlite::values::SQLiteValue;
152/// # fn main() {
153/// let column = SQL::<SQLiteValue>::raw("metadata");
154/// let condition = json_array_contains(column, "$.preferences", "dark_theme");
155/// assert_eq!(
156///     condition.sql(),
157///     "EXISTS(SELECT 1 FROM json_each( metadata , ? ) WHERE value = ? )"
158/// );
159/// # }
160/// ```
161pub fn json_array_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
162where
163    L: ToSQL<'a, SQLiteValue<'a>>,
164    R: Into<SQLiteValue<'a>>,
165{
166    SQL::raw("EXISTS(SELECT 1 FROM json_each(")
167        .append(left.to_sql())
168        .append(SQL::raw(", "))
169        .append(SQL::param(SQLiteValue::from(path)))
170        .append(SQL::raw(") WHERE value = "))
171        .append(SQL::param(value.into()))
172        .append(SQL::raw(")"))
173}
174
175/// Create a JSON object contains key condition
176///
177/// # Example
178/// ```
179/// # use drizzle_sqlite::expr::json_object_contains_key;
180/// # use drizzle_core::SQL;
181/// # use drizzle_sqlite::values::SQLiteValue;
182/// # fn main() {
183/// let column = SQL::<SQLiteValue>::raw("metadata");
184/// let condition = json_object_contains_key(column, "$", "theme");
185/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NOT NULL");
186/// # }
187/// ```
188pub fn json_object_contains_key<'a, L>(
189    left: L,
190    path: &'a str,
191    key: &'a str,
192) -> SQL<'a, SQLiteValue<'a>>
193where
194    L: ToSQL<'a, SQLiteValue<'a>>,
195{
196    let full_path = if path.ends_with('$') || path.is_empty() {
197        format!("$.{key}")
198    } else {
199        format!("{path}.{key}")
200    };
201
202    SQL::raw("json_type(")
203        .append(left.to_sql())
204        .append(SQL::raw(", "))
205        .append(SQL::param(SQLiteValue::from(full_path)))
206        .append(SQL::raw(") IS NOT NULL"))
207}
208
209/// Create a JSON text search condition using case-insensitive matching
210///
211/// # Example
212/// ```
213/// # use drizzle_sqlite::expr::json_text_contains;
214/// # use drizzle_core::SQL;
215/// # use drizzle_sqlite::values::SQLiteValue;
216/// # fn main() {
217/// let column = SQL::<SQLiteValue>::raw("metadata");
218/// let condition = json_text_contains(column, "$.description", "user");
219/// assert_eq!(
220///     condition.sql(),
221///     "instr(lower(json_extract( metadata , ? )), lower( ? )) > 0"
222/// );
223/// # }
224/// ```
225pub fn json_text_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
226where
227    L: ToSQL<'a, SQLiteValue<'a>>,
228    R: Into<SQLiteValue<'a>>,
229{
230    SQL::raw("instr(lower(json_extract(")
231        .append(left.to_sql())
232        .append(SQL::raw(", "))
233        .append(SQL::param(SQLiteValue::from(path)))
234        .append(SQL::raw(")), lower("))
235        .append(SQL::param(value.into()))
236        .append(SQL::raw(")) > 0"))
237}
238
239/// Create a JSON numeric greater-than condition
240///
241/// # Example
242/// ```
243/// # use drizzle_sqlite::expr::json_gt;
244/// # use drizzle_core::SQL;
245/// # use drizzle_sqlite::values::SQLiteValue;
246/// let column = SQL::<SQLiteValue>::raw("metadata");
247/// let condition = json_gt(column, "$.score", 85.0);
248/// assert_eq!(condition.sql(), "CAST(json_extract( metadata , ? ) AS NUMERIC) > ?");
249/// ```
250pub fn json_gt<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
251where
252    L: ToSQL<'a, SQLiteValue<'a>>,
253    R: Into<SQLiteValue<'a>>,
254{
255    SQL::raw("CAST(json_extract(")
256        .append(left.to_sql())
257        .append(SQL::raw(", "))
258        .append(SQL::param(SQLiteValue::from(path)))
259        .append(SQL::raw(") AS NUMERIC) > "))
260        .append(SQL::param(value.into()))
261}
262
263/// Helper function for JSON extraction using ->> operator
264///
265/// # Example
266/// ```
267/// # use drizzle_sqlite::expr::json_extract;
268/// # use drizzle_core::SQL;
269/// # use drizzle_sqlite::values::SQLiteValue;
270/// # fn main() {
271/// let column = SQL::<SQLiteValue>::raw("metadata");
272/// let extract_expr = json_extract(column, "theme");
273/// assert_eq!(extract_expr.sql(), "metadata ->> ?");
274/// # }
275/// ```
276pub fn json_extract<'a, L>(left: L, path: impl AsRef<str>) -> SQL<'a, SQLiteValue<'a>>
277where
278    L: ToSQL<'a, SQLiteValue<'a>>,
279{
280    left.to_sql()
281        .append(SQL::raw(" ->> "))
282        .append(SQL::param(SQLiteValue::from(path.as_ref().to_owned())))
283}
284
285/// Helper function for JSON extraction as JSON text using -> operator
286///
287/// # Example
288/// ```
289/// # use drizzle_sqlite::expr::json_extract_text;
290/// # use drizzle_core::SQL;
291/// # use drizzle_sqlite::values::SQLiteValue;
292/// # fn main() {
293/// let column = SQL::<SQLiteValue>::raw("metadata");
294/// let extract_expr = json_extract_text(column, "preferences");
295/// assert_eq!(extract_expr.sql(), "metadata -> ?");
296/// # }
297/// ```
298pub fn json_extract_text<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
299where
300    L: ToSQL<'a, SQLiteValue<'a>>,
301{
302    left.to_sql()
303        .append(SQL::raw(" -> "))
304        .append(SQL::param(SQLiteValue::from(path)))
305}
306
307#[cfg(test)]
308mod tests {
309    use super::*;
310
311    #[test]
312    fn json_paths_are_parameters_in_stable_order() {
313        let expression = json_contains(
314            SQL::param(SQLiteValue::from("document")),
315            "$.preferences['quoted']",
316            "dark",
317        );
318
319        assert_eq!(expression.sql(), "json_extract( ? , ? ) = ?");
320        let document = SQLiteValue::from("document");
321        let path = SQLiteValue::from("$.preferences['quoted']");
322        let value = SQLiteValue::from("dark");
323        assert_eq!(
324            expression.params().collect::<Vec<_>>(),
325            vec![&document, &path, &value]
326        );
327    }
328
329    #[test]
330    fn json_object_key_is_bound_as_data() {
331        let expression =
332            json_object_contains_key(SQL::<SQLiteValue>::raw("metadata"), "$", "quote'key");
333
334        assert_eq!(expression.sql(), "json_type( metadata , ? ) IS NOT NULL");
335        let path = SQLiteValue::from("$.quote'key");
336        assert_eq!(expression.params().collect::<Vec<_>>(), vec![&path]);
337    }
338}
drizzle_sqlite/expr.rs

drizzle_sqlite/
expr.rs
