drizzle_sqlite/
expressions.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
6use crate::values::SQLiteValue;
7use drizzle_core::{SQL, ToSQL};
8
9pub fn json<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
10    SQL::func("json", value.to_sql())
11}
12
13pub fn jsonb<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
14    SQL::func("jsonb", value.to_sql())
15}
16
17/// Create a JSON field equality condition using SQLite ->> operator
18///
19/// # Example
20/// ```
21/// # use drizzle_sqlite::expressions::json_eq;
22/// # use drizzle_core::SQL;
23/// # use drizzle_sqlite::values::SQLiteValue;
24/// # fn main() {
25/// let column = SQL::<SQLiteValue>::raw("metadata");
26/// let condition = json_eq(column, "theme", "dark");
27/// assert_eq!(condition.sql(), "metadata ->>'theme' = ?");
28/// # }
29/// ```
30pub fn json_eq<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
31where
32    L: ToSQL<'a, SQLiteValue<'a>>,
33    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
34{
35    left.to_sql()
36        .append(SQL::raw(format!("->>'{}' = ", field)))
37        .append(value.to_sql())
38}
39
40/// Create a JSON field inequality condition
41///
42/// # Example
43/// ```
44/// # use drizzle_sqlite::expressions::json_ne;
45/// # use drizzle_core::SQL;
46/// # use drizzle_sqlite::values::SQLiteValue;
47/// # fn main() {
48/// let column = SQL::<SQLiteValue>::raw("metadata");
49/// let condition = json_ne(column, "theme", "light");
50/// assert_eq!(condition.sql(), "metadata ->>'theme' != ?");
51/// # }
52/// ```
53pub fn json_ne<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
54where
55    L: ToSQL<'a, SQLiteValue<'a>>,
56    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
57{
58    left.to_sql()
59        .append(SQL::raw(format!("->>'{}' != ", field)))
60        .append(value.to_sql())
61}
62
63/// Create a JSON field contains condition using json_extract
64///
65/// # Example
66/// ```
67/// # use drizzle_sqlite::expressions::json_contains;
68/// # use drizzle_core::SQL;
69/// # use drizzle_sqlite::values::SQLiteValue;
70/// # fn main() {
71/// let column = SQL::<SQLiteValue>::raw("metadata");
72/// let condition = json_contains(column, "$.preferences[0]", "dark_theme");
73/// assert_eq!(condition.sql(), "json_extract( metadata , '$.preferences[0]') = ?");
74/// # }
75/// ```
76pub fn json_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
77where
78    L: ToSQL<'a, SQLiteValue<'a>>,
79    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
80{
81    SQL::raw("json_extract(")
82        .append(left.to_sql())
83        .append(SQL::raw(format!(", '{}') = ", path)))
84        .append(value.to_sql())
85}
86
87/// Create a JSON field exists condition using json_type
88///
89/// # Example
90/// ```
91/// # use drizzle_sqlite::expressions::json_exists;
92/// # use drizzle_core::SQL;
93/// # use drizzle_sqlite::values::SQLiteValue;
94/// # fn main() {
95/// let column = SQL::<SQLiteValue>::raw("metadata");
96/// let condition = json_exists(column, "$.theme");
97/// assert_eq!(condition.sql(), "json_type( metadata , '$.theme') IS NOT NULL");
98/// # }
99/// ```
100pub fn json_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
101where
102    L: ToSQL<'a, SQLiteValue<'a>>,
103{
104    SQL::raw("json_type(")
105        .append(left.to_sql())
106        .append(SQL::raw(format!(", '{}') IS NOT NULL", path)))
107}
108
109/// Create a JSON field does not exist condition
110///
111/// # Example
112/// ```
113/// # use drizzle_sqlite::expressions::json_not_exists;
114/// # use drizzle_core::SQL;
115/// # use drizzle_sqlite::values::SQLiteValue;
116/// # fn main() {
117/// let column = SQL::<SQLiteValue>::raw("metadata");
118/// let condition = json_not_exists(column, "$.theme");
119/// assert_eq!(condition.sql(), "json_type( metadata , '$.theme') IS NULL");
120/// # }
121/// ```
122pub fn json_not_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
123where
124    L: ToSQL<'a, SQLiteValue<'a>>,
125{
126    SQL::raw("json_type(")
127        .append(left.to_sql())
128        .append(SQL::raw(format!(", '{}') IS NULL", path)))
129}
130
131/// Create a JSON array contains value condition
132///
133/// # Example
134/// ```
135/// # use drizzle_sqlite::expressions::json_array_contains;
136/// # use drizzle_core::SQL;
137/// # use drizzle_sqlite::values::SQLiteValue;
138/// # fn main() {
139/// let column = SQL::<SQLiteValue>::raw("metadata");
140/// let condition = json_array_contains(column, "$.preferences", "dark_theme");
141/// assert_eq!(
142///     condition.sql(),
143///     "EXISTS(SELECT 1 FROM json_each( metadata , '$.preferences') WHERE value = ? )"
144/// );
145/// # }
146/// ```
147pub fn json_array_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
148where
149    L: ToSQL<'a, SQLiteValue<'a>>,
150    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
151{
152    SQL::raw("EXISTS(SELECT 1 FROM json_each(")
153        .append(left.to_sql())
154        .append(SQL::raw(format!(", '{}') WHERE value = ", path)))
155        .append(value.to_sql())
156        .append(SQL::raw(")"))
157}
158
159/// Create a JSON object contains key condition
160///
161/// # Example
162/// ```
163/// # use drizzle_sqlite::expressions::json_object_contains_key;
164/// # use drizzle_core::SQL;
165/// # use drizzle_sqlite::values::SQLiteValue;
166/// # fn main() {
167/// let column = SQL::<SQLiteValue>::raw("metadata");
168/// let condition = json_object_contains_key(column, "$", "theme");
169/// assert_eq!(condition.sql(), "json_type( metadata , '$.theme') IS NOT NULL");
170/// # }
171/// ```
172pub fn json_object_contains_key<'a, L>(
173    left: L,
174    path: &'a str,
175    key: &'a str,
176) -> SQL<'a, SQLiteValue<'a>>
177where
178    L: ToSQL<'a, SQLiteValue<'a>>,
179{
180    let full_path = if path.ends_with('$') || path.is_empty() {
181        format!("$.{}", key)
182    } else {
183        format!("{}.{}", path, key)
184    };
185
186    SQL::raw("json_type(")
187        .append(left.to_sql())
188        .append(SQL::raw(format!(", '{}') IS NOT NULL", full_path)))
189}
190
191/// Create a JSON text search condition using case-insensitive matching
192///
193/// # Example
194/// ```
195/// # use drizzle_sqlite::expressions::json_text_contains;
196/// # use drizzle_core::SQL;
197/// # use drizzle_sqlite::values::SQLiteValue;
198/// # fn main() {
199/// let column = SQL::<SQLiteValue>::raw("metadata");
200/// let condition = json_text_contains(column, "$.description", "user");
201/// assert_eq!(
202///     condition.sql(),
203///     "instr(lower(json_extract( metadata , '$.description')), lower( ? )) > 0"
204/// );
205/// # }
206/// ```
207pub fn json_text_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
208where
209    L: ToSQL<'a, SQLiteValue<'a>>,
210    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
211{
212    SQL::raw("instr(lower(json_extract(")
213        .append(left.to_sql())
214        .append(SQL::raw(format!(", '{}')), lower(", path)))
215        .append(value.to_sql())
216        .append(SQL::raw(")) > 0"))
217}
218
219/// Create a JSON numeric greater-than condition
220///
221/// # Example
222/// ```
223/// # use drizzle_sqlite::expressions::json_gt;
224/// # use drizzle_core::SQL;
225/// # use drizzle_sqlite::values::SQLiteValue;
226/// let column = SQL::<SQLiteValue>::raw("metadata");
227/// let condition = json_gt(column, "$.score", 85.0);
228/// assert_eq!(condition.sql(), "CAST(json_extract( metadata , '$.score') AS NUMERIC) > ?");
229/// ```
230pub fn json_gt<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
231where
232    L: ToSQL<'a, SQLiteValue<'a>>,
233    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
234{
235    SQL::raw("CAST(json_extract(")
236        .append(left.to_sql())
237        .append(SQL::raw(format!(", '{}') AS NUMERIC) > ", path)))
238        .append(value.to_sql())
239}
240
241/// Helper function for JSON extraction using ->> operator
242///
243/// # Example
244/// ```
245/// # use drizzle_sqlite::expressions::json_extract;
246/// # use drizzle_core::SQL;
247/// # use drizzle_sqlite::values::SQLiteValue;
248/// # fn main() {
249/// let column = SQL::<SQLiteValue>::raw("metadata");
250/// let extract_expr = json_extract(column, "theme");
251/// assert_eq!(extract_expr.sql(), "metadata ->>'theme'");
252/// # }
253/// ```
254pub fn json_extract<'a, L>(left: L, path: impl AsRef<str>) -> SQL<'a, SQLiteValue<'a>>
255where
256    L: ToSQL<'a, SQLiteValue<'a>>,
257{
258    left.to_sql()
259        .append(SQL::raw(format!("->>'{}'", path.as_ref())))
260}
261
262/// Helper function for JSON extraction as JSON text using -> operator
263///
264/// # Example
265/// ```
266/// # use drizzle_sqlite::expressions::json_extract_text;
267/// # use drizzle_core::SQL;
268/// # use drizzle_sqlite::values::SQLiteValue;
269/// # fn main() {
270/// let column = SQL::<SQLiteValue>::raw("metadata");
271/// let extract_expr = json_extract_text(column, "preferences");
272/// assert_eq!(extract_expr.sql(), "metadata ->'preferences'");
273/// # }
274/// ```
275pub fn json_extract_text<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
276where
277    L: ToSQL<'a, SQLiteValue<'a>>,
278{
279    left.to_sql().append(SQL::raw(format!("->'{}'", path)))
280}
drizzle_sqlite/expressions.rs

drizzle_sqlite/
expressions.rs
