drizzle_sqlite/
expr.rs

1//! `SQLite` JSON functions and conditions.
2//!
3//! These helpers build untyped [`SQL`] fragments for JSON stored in TEXT or
4//! BLOB columns. JSON paths, keys and values are sent as bound parameters.
5//! For standard, typed expressions use `drizzle_core::expr`.
6
7#[cfg(not(feature = "std"))]
8use crate::prelude::*;
9use crate::values::SQLiteValue;
10use drizzle_core::{SQL, ToSQL};
11
12/// Wraps `value` in `json(..)`, which checks that it is valid JSON and
13/// returns it as minified JSON text.
14///
15/// # Examples
16///
17/// ```
18/// # use drizzle_sqlite::expr::json;
19/// # use drizzle_core::SQL;
20/// # use drizzle_sqlite::values::SQLiteValue;
21/// let expr = json(SQL::<SQLiteValue>::raw("metadata"));
22/// assert_eq!(expr.sql(), "json (metadata)");
23/// ```
24pub fn json<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
25    SQL::func("json", value.to_sql())
26}
27
28/// Wraps `value` in `jsonb(..)`, which checks that it is valid JSON and
29/// returns it in `SQLite`'s binary JSONB format.
30///
31/// # Examples
32///
33/// ```
34/// # use drizzle_sqlite::expr::jsonb;
35/// # use drizzle_core::SQL;
36/// # use drizzle_sqlite::values::SQLiteValue;
37/// let expr = jsonb(SQL::<SQLiteValue>::raw("metadata"));
38/// assert_eq!(expr.sql(), "jsonb (metadata)");
39/// ```
40pub fn jsonb<'a>(value: impl ToSQL<'a, SQLiteValue<'a>>) -> SQL<'a, SQLiteValue<'a>> {
41    SQL::func("jsonb", value.to_sql())
42}
43
44/// Builds `left ->> field = value`: the JSON field `field` equals `value`.
45///
46/// `field` may be a key name (`"theme"`) or a JSON path (`"$.theme"`).
47///
48/// # Examples
49/// ```
50/// # use drizzle_sqlite::expr::json_eq;
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_eq(column, "theme", "dark");
56/// assert_eq!(condition.sql(), "metadata ->> ? = ?");
57/// # }
58/// ```
59pub fn json_eq<'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/// Builds `left ->> field != value`: the JSON field `field` does not equal
72/// `value`.
73///
74/// `field` may be a key name or a JSON path. Like any SQL comparison, this
75/// is not true when the field is missing (`NULL`).
76///
77/// # Examples
78/// ```
79/// # use drizzle_sqlite::expr::json_ne;
80/// # use drizzle_core::SQL;
81/// # use drizzle_sqlite::values::SQLiteValue;
82/// # fn main() {
83/// let column = SQL::<SQLiteValue>::raw("metadata");
84/// let condition = json_ne(column, "theme", "light");
85/// assert_eq!(condition.sql(), "metadata ->> ? != ?");
86/// # }
87/// ```
88pub fn json_ne<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
89where
90    L: ToSQL<'a, SQLiteValue<'a>>,
91    R: Into<SQLiteValue<'a>>,
92{
93    left.to_sql()
94        .append(SQL::raw(" ->> "))
95        .append(SQL::param(SQLiteValue::from(field)))
96        .append(SQL::raw(" != "))
97        .append(SQL::param(value.into()))
98}
99
100/// Builds `json_extract(left, path) = value`: the value at `path` equals
101/// `value`.
102///
103/// Despite the name, this is an equality test, not a substring or
104/// array-membership test. Use [`json_array_contains`] or
105/// [`json_text_contains`] for those.
106///
107/// # Examples
108/// ```
109/// # use drizzle_sqlite::expr::json_contains;
110/// # use drizzle_core::SQL;
111/// # use drizzle_sqlite::values::SQLiteValue;
112/// # fn main() {
113/// let column = SQL::<SQLiteValue>::raw("metadata");
114/// let condition = json_contains(column, "$.preferences[0]", "dark_theme");
115/// assert_eq!(condition.sql(), "json_extract( metadata , ? ) = ?");
116/// # }
117/// ```
118pub fn json_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
119where
120    L: ToSQL<'a, SQLiteValue<'a>>,
121    R: Into<SQLiteValue<'a>>,
122{
123    SQL::raw("json_extract(")
124        .append(left.to_sql())
125        .append(SQL::raw(", "))
126        .append(SQL::param(SQLiteValue::from(path)))
127        .append(SQL::raw(") = "))
128        .append(SQL::param(value.into()))
129}
130
131/// Builds `json_type(left, path) IS NOT NULL`: the JSON document has a
132/// value at `path` (a JSON `null` counts as present).
133///
134/// # Examples
135/// ```
136/// # use drizzle_sqlite::expr::json_exists;
137/// # use drizzle_core::SQL;
138/// # use drizzle_sqlite::values::SQLiteValue;
139/// # fn main() {
140/// let column = SQL::<SQLiteValue>::raw("metadata");
141/// let condition = json_exists(column, "$.theme");
142/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NOT NULL");
143/// # }
144/// ```
145pub fn json_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
146where
147    L: ToSQL<'a, SQLiteValue<'a>>,
148{
149    SQL::raw("json_type(")
150        .append(left.to_sql())
151        .append(SQL::raw(", "))
152        .append(SQL::param(SQLiteValue::from(path)))
153        .append(SQL::raw(") IS NOT NULL"))
154}
155
156/// Builds `json_type(left, path) IS NULL`: the JSON document has no value
157/// at `path`.
158///
159/// # Examples
160/// ```
161/// # use drizzle_sqlite::expr::json_not_exists;
162/// # use drizzle_core::SQL;
163/// # use drizzle_sqlite::values::SQLiteValue;
164/// # fn main() {
165/// let column = SQL::<SQLiteValue>::raw("metadata");
166/// let condition = json_not_exists(column, "$.theme");
167/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NULL");
168/// # }
169/// ```
170pub fn json_not_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
171where
172    L: ToSQL<'a, SQLiteValue<'a>>,
173{
174    SQL::raw("json_type(")
175        .append(left.to_sql())
176        .append(SQL::raw(", "))
177        .append(SQL::param(SQLiteValue::from(path)))
178        .append(SQL::raw(") IS NULL"))
179}
180
181/// Builds an `EXISTS` test that is true when the JSON array at `path`
182/// has an element equal to `value`.
183///
184/// # Examples
185/// ```
186/// # use drizzle_sqlite::expr::json_array_contains;
187/// # use drizzle_core::SQL;
188/// # use drizzle_sqlite::values::SQLiteValue;
189/// # fn main() {
190/// let column = SQL::<SQLiteValue>::raw("metadata");
191/// let condition = json_array_contains(column, "$.preferences", "dark_theme");
192/// assert_eq!(
193///     condition.sql(),
194///     "EXISTS(SELECT 1 FROM json_each( metadata , ? ) WHERE value = ? )"
195/// );
196/// # }
197/// ```
198pub fn json_array_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
199where
200    L: ToSQL<'a, SQLiteValue<'a>>,
201    R: Into<SQLiteValue<'a>>,
202{
203    SQL::raw("EXISTS(SELECT 1 FROM json_each(")
204        .append(left.to_sql())
205        .append(SQL::raw(", "))
206        .append(SQL::param(SQLiteValue::from(path)))
207        .append(SQL::raw(") WHERE value = "))
208        .append(SQL::param(value.into()))
209        .append(SQL::raw(")"))
210}
211
212/// Builds a test that is true when the JSON object at `path` has the key
213/// `key`.
214///
215/// The key is appended to the path (`"$"` and `""` mean the root object),
216/// and the result is checked with `json_type(..) IS NOT NULL`. `key` is not
217/// quoted, so it must be a plain key name.
218///
219/// # Examples
220/// ```
221/// # use drizzle_sqlite::expr::json_object_contains_key;
222/// # use drizzle_core::SQL;
223/// # use drizzle_sqlite::values::SQLiteValue;
224/// # fn main() {
225/// let column = SQL::<SQLiteValue>::raw("metadata");
226/// let condition = json_object_contains_key(column, "$", "theme");
227/// assert_eq!(condition.sql(), "json_type( metadata , ? ) IS NOT NULL");
228/// # }
229/// ```
230pub fn json_object_contains_key<'a, L>(
231    left: L,
232    path: &'a str,
233    key: &'a str,
234) -> SQL<'a, SQLiteValue<'a>>
235where
236    L: ToSQL<'a, SQLiteValue<'a>>,
237{
238    let full_path = if path.ends_with('$') || path.is_empty() {
239        format!("$.{key}")
240    } else {
241        format!("{path}.{key}")
242    };
243
244    SQL::raw("json_type(")
245        .append(left.to_sql())
246        .append(SQL::raw(", "))
247        .append(SQL::param(SQLiteValue::from(full_path)))
248        .append(SQL::raw(") IS NOT NULL"))
249}
250
251/// Builds a case-insensitive substring test: the text at `path` contains
252/// `value`.
253///
254/// Uses `instr(lower(..), lower(..)) > 0`, so case folding only applies to
255/// ASCII letters.
256///
257/// # Examples
258/// ```
259/// # use drizzle_sqlite::expr::json_text_contains;
260/// # use drizzle_core::SQL;
261/// # use drizzle_sqlite::values::SQLiteValue;
262/// # fn main() {
263/// let column = SQL::<SQLiteValue>::raw("metadata");
264/// let condition = json_text_contains(column, "$.description", "user");
265/// assert_eq!(
266///     condition.sql(),
267///     "instr(lower(json_extract( metadata , ? )), lower( ? )) > 0"
268/// );
269/// # }
270/// ```
271pub fn json_text_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
272where
273    L: ToSQL<'a, SQLiteValue<'a>>,
274    R: Into<SQLiteValue<'a>>,
275{
276    SQL::raw("instr(lower(json_extract(")
277        .append(left.to_sql())
278        .append(SQL::raw(", "))
279        .append(SQL::param(SQLiteValue::from(path)))
280        .append(SQL::raw(")), lower("))
281        .append(SQL::param(value.into()))
282        .append(SQL::raw(")) > 0"))
283}
284
285/// Builds `CAST(json_extract(left, path) AS NUMERIC) > value`: the number
286/// at `path` is greater than `value`.
287///
288/// # Examples
289/// ```
290/// # use drizzle_sqlite::expr::json_gt;
291/// # use drizzle_core::SQL;
292/// # use drizzle_sqlite::values::SQLiteValue;
293/// let column = SQL::<SQLiteValue>::raw("metadata");
294/// let condition = json_gt(column, "$.score", 85.0);
295/// assert_eq!(condition.sql(), "CAST(json_extract( metadata , ? ) AS NUMERIC) > ?");
296/// ```
297pub fn json_gt<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
298where
299    L: ToSQL<'a, SQLiteValue<'a>>,
300    R: Into<SQLiteValue<'a>>,
301{
302    SQL::raw("CAST(json_extract(")
303        .append(left.to_sql())
304        .append(SQL::raw(", "))
305        .append(SQL::param(SQLiteValue::from(path)))
306        .append(SQL::raw(") AS NUMERIC) > "))
307        .append(SQL::param(value.into()))
308}
309
310/// Builds `left ->> path`, which returns the value at `path` as an SQL
311/// value (TEXT, INTEGER, REAL or NULL).
312///
313/// `path` may be a key name or a JSON path. Use [`json_extract_text`] to
314/// get JSON text instead.
315///
316/// # Examples
317/// ```
318/// # use drizzle_sqlite::expr::json_extract;
319/// # use drizzle_core::SQL;
320/// # use drizzle_sqlite::values::SQLiteValue;
321/// # fn main() {
322/// let column = SQL::<SQLiteValue>::raw("metadata");
323/// let extract_expr = json_extract(column, "theme");
324/// assert_eq!(extract_expr.sql(), "metadata ->> ?");
325/// # }
326/// ```
327pub fn json_extract<'a, L>(left: L, path: impl AsRef<str>) -> SQL<'a, SQLiteValue<'a>>
328where
329    L: ToSQL<'a, SQLiteValue<'a>>,
330{
331    left.to_sql()
332        .append(SQL::raw(" ->> "))
333        .append(SQL::param(SQLiteValue::from(path.as_ref().to_owned())))
334}
335
336/// Builds `left -> path`, which returns the value at `path` as JSON text
337/// (strings stay quoted, objects and arrays stay JSON).
338///
339/// Use [`json_extract`] to get a plain SQL value instead.
340///
341/// # Examples
342/// ```
343/// # use drizzle_sqlite::expr::json_extract_text;
344/// # use drizzle_core::SQL;
345/// # use drizzle_sqlite::values::SQLiteValue;
346/// # fn main() {
347/// let column = SQL::<SQLiteValue>::raw("metadata");
348/// let extract_expr = json_extract_text(column, "preferences");
349/// assert_eq!(extract_expr.sql(), "metadata -> ?");
350/// # }
351/// ```
352pub fn json_extract_text<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
353where
354    L: ToSQL<'a, SQLiteValue<'a>>,
355{
356    left.to_sql()
357        .append(SQL::raw(" -> "))
358        .append(SQL::param(SQLiteValue::from(path)))
359}
360
361#[cfg(test)]
362mod tests {
363    use super::*;
364
365    #[test]
366    fn json_paths_are_parameters_in_stable_order() {
367        let expression = json_contains(
368            SQL::param(SQLiteValue::from("document")),
369            "$.preferences['quoted']",
370            "dark",
371        );
372
373        assert_eq!(expression.sql(), "json_extract( ? , ? ) = ?");
374        let document = SQLiteValue::from("document");
375        let path = SQLiteValue::from("$.preferences['quoted']");
376        let value = SQLiteValue::from("dark");
377        assert_eq!(
378            expression.params().collect::<Vec<_>>(),
379            vec![&document, &path, &value]
380        );
381    }
382
383    #[test]
384    fn json_object_key_is_bound_as_data() {
385        let expression =
386            json_object_contains_key(SQL::<SQLiteValue>::raw("metadata"), "$", "quote'key");
387
388        assert_eq!(expression.sql(), "json_type( metadata , ? ) IS NOT NULL");
389        let path = SQLiteValue::from("$.quote'key");
390        assert_eq!(expression.params().collect::<Vec<_>>(), vec![&path]);
391    }
392}
drizzle_sqlite/expr.rs

drizzle_sqlite/
expr.rs
