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

drizzle_sqlite/
expr.rs
