drizzle_sqlite/
conditions.rs

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

drizzle_sqlite/
conditions.rs
