drizzle_sqlite/
conditions.rs

1// SQLite specific condition functions, particularly for JSON
2
3use crate::values::SQLiteValue;
4use drizzle_core::{SQL, ToSQL};
5
6// JSON field equality - column->>'field' = value (using SQLite ->> operator for text comparison)
7pub fn json_eq<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
8where
9    L: ToSQL<'a, SQLiteValue<'a>>,
10    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
11{
12    left.to_sql()
13        .append(SQL::raw(format!("->>'{}' = ", field)))
14        .append(value.to_sql())
15}
16
17// JSON field inequality - column->>'field' != value
18pub fn json_ne<'a, L, R>(left: L, field: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
19where
20    L: ToSQL<'a, SQLiteValue<'a>>,
21    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
22{
23    left.to_sql()
24        .append(SQL::raw(format!("->>'{}' != ", field)))
25        .append(value.to_sql())
26}
27
28// JSON field contains - json_extract(column, '$.field') = value
29pub fn json_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
30where
31    L: ToSQL<'a, SQLiteValue<'a>>,
32    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
33{
34    SQL::raw("json_extract(")
35        .append(left.to_sql())
36        .append(SQL::raw(format!(", '{}') = ", path)))
37        .append(value.to_sql())
38}
39
40// JSON field exists - json_type(column, '$.field') IS NOT NULL
41pub fn json_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
42where
43    L: ToSQL<'a, SQLiteValue<'a>>,
44{
45    SQL::raw("json_type(")
46        .append(left.to_sql())
47        .append(SQL::raw(format!(", '{}') IS NOT NULL", path)))
48}
49
50// JSON field does not exist - json_type(column, '$.field') IS NULL
51pub fn json_not_exists<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
52where
53    L: ToSQL<'a, SQLiteValue<'a>>,
54{
55    SQL::raw("json_type(")
56        .append(left.to_sql())
57        .append(SQL::raw(format!(", '{}') IS NULL", path)))
58}
59
60// JSON array contains value - EXISTS(SELECT 1 FROM json_each(column, '$.path') WHERE value = ?)
61pub fn json_array_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
62where
63    L: ToSQL<'a, SQLiteValue<'a>>,
64    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
65{
66    SQL::raw("EXISTS(SELECT 1 FROM json_each(")
67        .append(left.to_sql())
68        .append(SQL::raw(format!(", '{}') WHERE value = ", path)))
69        .append(value.to_sql())
70        .append_raw(")")
71}
72
73// JSON object contains key - json_type(column, '$.path.key') IS NOT NULL
74pub fn json_object_contains_key<'a, L>(
75    left: L,
76    path: &'a str,
77    key: &'a str,
78) -> SQL<'a, SQLiteValue<'a>>
79where
80    L: ToSQL<'a, SQLiteValue<'a>>,
81{
82    let full_path = if path.ends_with('$') || path.is_empty() {
83        format!("$.{}", key)
84    } else {
85        format!("{}.{}", path, key)
86    };
87
88    SQL::raw("json_type(")
89        .append(left.to_sql())
90        .append(SQL::raw(format!(", '{}') IS NOT NULL", full_path)))
91}
92
93// JSON text search in value - instr(lower(json_extract(column, '$.path')), lower(?)) > 0
94pub fn json_text_contains<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
95where
96    L: ToSQL<'a, SQLiteValue<'a>>,
97    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
98{
99    SQL::raw("instr(lower(json_extract(")
100        .append(left.to_sql())
101        .append(SQL::raw(format!(", '{}'))), lower(", path)))
102        .append(value.to_sql())
103        .append_raw(")) > 0")
104}
105
106// JSON comparison functions for numbers
107pub fn json_gt<'a, L, R>(left: L, path: &'a str, value: R) -> SQL<'a, SQLiteValue<'a>>
108where
109    L: ToSQL<'a, SQLiteValue<'a>>,
110    R: Into<SQLiteValue<'a>> + ToSQL<'a, SQLiteValue<'a>>,
111{
112    SQL::raw("CAST(json_extract(")
113        .append(left.to_sql())
114        .append(SQL::raw(format!(", '{}') AS NUMERIC) > ", path)))
115        .append(value.to_sql())
116}
117
118// Helper function for the JSON arrow-arrow operators (extract as Value)
119pub fn json_extract<'a, L>(left: L, path: impl AsRef<str>) -> SQL<'a, SQLiteValue<'a>>
120where
121    L: ToSQL<'a, SQLiteValue<'a>>,
122{
123    left.to_sql()
124        .append(SQL::raw(format!("->>'{}'", path.as_ref())))
125}
126
127// Helper function for the JSON arrow operators (extract as JSON text)
128pub fn json_extract_text<'a, L>(left: L, path: &'a str) -> SQL<'a, SQLiteValue<'a>>
129where
130    L: ToSQL<'a, SQLiteValue<'a>>,
131{
132    left.to_sql().append(SQL::raw(format!("->'{}'", path)))
133}
drizzle_sqlite/conditions.rs

drizzle_sqlite/
conditions.rs
