drizzle_postgres/expr/
json_ops.rs

1//! `PostgreSQL` JSON/JSONB operators.
2//!
3//! Provides type-safe access to `PostgreSQL` JSON operators:
4//! - `->` (get JSON object field by key, returns JSON)
5//! - `->>` (get JSON object field by key, returns text)
6//! - `#>` (get JSON object at path, returns JSON)
7//! - `#>>` (get JSON object at path, returns text)
8//! - `@>` (JSON contains)
9//! - `?` (JSON key exists)
10
11#[cfg(not(feature = "std"))]
12use crate::prelude::*;
13use crate::values::PostgresValue;
14use drizzle_core::ToSQL;
15use drizzle_core::expr::{Expr, NonNull, Null, SQLExpr, Scalar};
16use drizzle_core::sql::{SQL, SQLChunk, Token};
17
18/// `CAST($n AS type)` around an operator argument.
19///
20/// PostgreSQL infers untyped parameters at prepare time and picks the `text`
21/// overload of `->` / `->>` / `#>`; without the cast the driver binds an
22/// integer or array where the server expects text and the query fails.
23/// `CAST(operand AS JSONB)` for containment operands.
24///
25/// A bound `serde_json::Value` is declared as `json` by the drivers and a text
26/// literal as `text`; neither resolves `jsonb @> ...` without the cast.
27fn jsonb_operand<'a>(operand: SQL<'a, PostgresValue<'a>>) -> SQL<'a, PostgresValue<'a>> {
28    SQL::func("CAST", operand.push(Token::AS).append(SQL::raw("JSONB")))
29}
30
31fn typed_param<'a>(
32    value: PostgresValue<'a>,
33    type_name: &'static str,
34) -> SQL<'a, PostgresValue<'a>> {
35    SQL::func(
36        "CAST",
37        SQL::param(value)
38            .push(Token::AS)
39            .append(SQL::raw(type_name)),
40    )
41}
42
43use drizzle_types::postgres::types::{Boolean, Json, Text};
44
45/// `PostgreSQL` `->` operator - get JSON object field by key, returns JSON.
46///
47/// # Example
48///
49/// ```
50/// # use drizzle_postgres::expr::json_get;
51/// # use drizzle_core::{SQL, ToSQL};
52/// # use drizzle_postgres::values::PostgresValue;
53/// let data = SQL::<PostgresValue>::raw("data");
54/// let field = json_get(data, "name");
55/// assert!(field.to_sql().sql().contains("->"));
56/// ```
57pub fn json_get<'a, E>(expr: E, key: &'a str) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar>
58where
59    E: Expr<'a, PostgresValue<'a>>,
60{
61    SQLExpr::new(
62        expr.to_sql()
63            .push(SQLChunk::Raw("->".into()))
64            .append(typed_param(PostgresValue::Text(key.into()), "TEXT")),
65    )
66}
67
68/// `PostgreSQL` `->` operator with integer index - get JSON array element.
69///
70/// # Example
71///
72/// ```
73/// # use drizzle_postgres::expr::json_get_idx;
74/// # use drizzle_core::{SQL, ToSQL};
75/// # use drizzle_postgres::values::PostgresValue;
76/// let data = SQL::<PostgresValue>::raw("data");
77/// let elem = json_get_idx(data, 0);
78/// assert!(elem.to_sql().sql().contains("->"));
79/// ```
80pub fn json_get_idx<'a, E>(
81    expr: E,
82    index: i32,
83) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar>
84where
85    E: Expr<'a, PostgresValue<'a>>,
86{
87    SQLExpr::new(
88        expr.to_sql()
89            .push(SQLChunk::Raw("->".into()))
90            .append(typed_param(PostgresValue::Integer(index), "INTEGER")),
91    )
92}
93
94/// `PostgreSQL` `->>` operator - get JSON object field as text.
95///
96/// # Example
97///
98/// ```
99/// # use drizzle_postgres::expr::json_get_text;
100/// # use drizzle_core::{SQL, ToSQL};
101/// # use drizzle_postgres::values::PostgresValue;
102/// let data = SQL::<PostgresValue>::raw("data");
103/// let name = json_get_text(data, "name");
104/// assert!(name.to_sql().sql().contains("->>"));
105/// ```
106pub fn json_get_text<'a, E>(
107    expr: E,
108    key: &'a str,
109) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar>
110where
111    E: Expr<'a, PostgresValue<'a>>,
112{
113    SQLExpr::new(
114        expr.to_sql()
115            .push(SQLChunk::Raw("->>".into()))
116            .append(typed_param(PostgresValue::Text(key.into()), "TEXT")),
117    )
118}
119
120/// `PostgreSQL` `->>` operator with integer index - get JSON array element as text.
121///
122/// # Example
123///
124/// ```
125/// # use drizzle_postgres::expr::json_get_text_idx;
126/// # use drizzle_core::{SQL, ToSQL};
127/// # use drizzle_postgres::values::PostgresValue;
128/// let data = SQL::<PostgresValue>::raw("data");
129/// let elem = json_get_text_idx(data, 0);
130/// assert!(elem.to_sql().sql().contains("->>"));
131/// ```
132pub fn json_get_text_idx<'a, E>(
133    expr: E,
134    index: i32,
135) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar>
136where
137    E: Expr<'a, PostgresValue<'a>>,
138{
139    SQLExpr::new(
140        expr.to_sql()
141            .push(SQLChunk::Raw("->>".into()))
142            .append(typed_param(PostgresValue::Integer(index), "INTEGER")),
143    )
144}
145
146/// `PostgreSQL` `#>` operator - get JSON object at specified path, returns JSON.
147///
148/// # Example
149///
150/// ```
151/// # use drizzle_postgres::expr::json_get_path;
152/// # use drizzle_core::{SQL, ToSQL};
153/// # use drizzle_postgres::values::PostgresValue;
154/// let data = SQL::<PostgresValue>::raw("data");
155/// let nested = json_get_path(data, "{a,b}");
156/// assert!(nested.to_sql().sql().contains("#>"));
157/// ```
158pub fn json_get_path<'a, E>(
159    expr: E,
160    path: &'a str,
161) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar>
162where
163    E: Expr<'a, PostgresValue<'a>>,
164{
165    SQLExpr::new(
166        expr.to_sql()
167            .push(SQLChunk::Raw("#>".into()))
168            .append(typed_param(PostgresValue::Text(path.into()), "TEXT[]")),
169    )
170}
171
172/// `PostgreSQL` `#>>` operator - get JSON object at specified path as text.
173///
174/// # Example
175///
176/// ```
177/// # use drizzle_postgres::expr::json_get_path_text;
178/// # use drizzle_core::{SQL, ToSQL};
179/// # use drizzle_postgres::values::PostgresValue;
180/// let data = SQL::<PostgresValue>::raw("data");
181/// let nested = json_get_path_text(data, "{a,b}");
182/// assert!(nested.to_sql().sql().contains("#>>"));
183/// ```
184pub fn json_get_path_text<'a, E>(
185    expr: E,
186    path: &'a str,
187) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar>
188where
189    E: Expr<'a, PostgresValue<'a>>,
190{
191    SQLExpr::new(
192        expr.to_sql()
193            .push(SQLChunk::Raw("#>>".into()))
194            .append(typed_param(PostgresValue::Text(path.into()), "TEXT[]")),
195    )
196}
197
198/// `PostgreSQL` `@>` operator for JSONB - left JSON contains right JSON.
199///
200/// # Example
201///
202/// ```
203/// # use drizzle_postgres::expr::jsonb_contains;
204/// # use drizzle_core::{SQL, ToSQL};
205/// # use drizzle_postgres::values::PostgresValue;
206/// let data = SQL::<PostgresValue>::raw("data");
207/// let cond = jsonb_contains(data, r#"{"key": "value"}"#);
208/// assert!(cond.to_sql().sql().contains("@>"));
209/// ```
210pub fn jsonb_contains<'a, L, R>(
211    left: L,
212    right: R,
213) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
214where
215    L: Expr<'a, PostgresValue<'a>>,
216    R: ToSQL<'a, PostgresValue<'a>>,
217{
218    SQLExpr::new(
219        left.to_sql()
220            .push(SQLChunk::Raw("@>".into()))
221            .append(jsonb_operand(right.to_sql())),
222    )
223}
224
225/// `PostgreSQL` `<@` operator for JSONB - left JSON is contained by right JSON.
226///
227/// # Example
228///
229/// ```
230/// # use drizzle_postgres::expr::jsonb_contained;
231/// # use drizzle_core::{SQL, ToSQL};
232/// # use drizzle_postgres::values::PostgresValue;
233/// let data = SQL::<PostgresValue>::raw("data");
234/// let cond = jsonb_contained(data, r#"{"key": "value", "other": 1}"#);
235/// assert!(cond.to_sql().sql().contains("<@"));
236/// ```
237pub fn jsonb_contained<'a, L, R>(
238    left: L,
239    right: R,
240) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
241where
242    L: Expr<'a, PostgresValue<'a>>,
243    R: ToSQL<'a, PostgresValue<'a>>,
244{
245    SQLExpr::new(
246        left.to_sql()
247            .push(SQLChunk::Raw("<@".into()))
248            .append(jsonb_operand(right.to_sql())),
249    )
250}
251
252/// `PostgreSQL` `?` operator for JSONB - does the key exist in the JSON object?
253///
254/// # Example
255///
256/// ```
257/// # use drizzle_postgres::expr::jsonb_exists_key;
258/// # use drizzle_core::{SQL, ToSQL};
259/// # use drizzle_postgres::values::PostgresValue;
260/// let data = SQL::<PostgresValue>::raw("data");
261/// let cond = jsonb_exists_key(data, "name");
262/// assert!(cond.to_sql().sql().contains("?"));
263/// ```
264pub fn jsonb_exists_key<'a, E>(
265    expr: E,
266    key: &'a str,
267) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
268where
269    E: Expr<'a, PostgresValue<'a>>,
270{
271    SQLExpr::new(
272        expr.to_sql()
273            .push(SQLChunk::Raw("?".into()))
274            .append(typed_param(PostgresValue::Text(key.into()), "TEXT")),
275    )
276}
277
278/// `PostgreSQL` `?|` operator for JSONB - do any of the keys exist?
279///
280/// # Example
281///
282/// ```
283/// # use drizzle_postgres::expr::jsonb_exists_any;
284/// # use drizzle_core::{SQL, ToSQL};
285/// # use drizzle_postgres::values::PostgresValue;
286/// let data = SQL::<PostgresValue>::raw("data");
287/// let cond = jsonb_exists_any(data, &["name", "email"]);
288/// assert!(cond.to_sql().sql().contains("?|"));
289/// ```
290pub fn jsonb_exists_any<'a, E>(
291    expr: E,
292    keys: &[&'a str],
293) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
294where
295    E: Expr<'a, PostgresValue<'a>>,
296{
297    let arr: Vec<PostgresValue<'a>> = keys
298        .iter()
299        .map(|k| PostgresValue::Text((*k).into()))
300        .collect();
301    SQLExpr::new(
302        expr.to_sql()
303            .push(SQLChunk::Raw("?|".into()))
304            .append(SQL::param(PostgresValue::Array(arr))),
305    )
306}
307
308/// `PostgreSQL` `?&` operator for JSONB - do all of the keys exist?
309///
310/// # Example
311///
312/// ```
313/// # use drizzle_postgres::expr::jsonb_exists_all;
314/// # use drizzle_core::{SQL, ToSQL};
315/// # use drizzle_postgres::values::PostgresValue;
316/// let data = SQL::<PostgresValue>::raw("data");
317/// let cond = jsonb_exists_all(data, &["name", "email"]);
318/// assert!(cond.to_sql().sql().contains("?&"));
319/// ```
320pub fn jsonb_exists_all<'a, E>(
321    expr: E,
322    keys: &[&'a str],
323) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
324where
325    E: Expr<'a, PostgresValue<'a>>,
326{
327    let arr: Vec<PostgresValue<'a>> = keys
328        .iter()
329        .map(|k| PostgresValue::Text((*k).into()))
330        .collect();
331    SQLExpr::new(
332        expr.to_sql()
333            .push(SQLChunk::Raw("?&".into()))
334            .append(SQL::param(PostgresValue::Array(arr))),
335    )
336}
337
338/// Extension trait providing method-based JSON operators for `PostgreSQL` expressions.
339pub trait JsonExprExt<'a>: Expr<'a, PostgresValue<'a>> + Sized {
340    /// Get JSON object field by key (`->` operator), returns JSON.
341    fn json_get(self, key: &'a str) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar> {
342        json_get(self, key)
343    }
344
345    /// Get JSON array element by index (`->` operator), returns JSON.
346    fn json_get_idx(self, index: i32) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar> {
347        json_get_idx(self, index)
348    }
349
350    /// Get JSON object field as text (`->>` operator).
351    fn json_get_text(self, key: &'a str) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar> {
352        json_get_text(self, key)
353    }
354
355    /// Get JSON array element as text (`->>` operator).
356    fn json_get_text_idx(self, index: i32) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar> {
357        json_get_text_idx(self, index)
358    }
359
360    /// Get JSON object at path (`#>` operator), returns JSON.
361    fn json_get_path(self, path: &'a str) -> SQLExpr<'a, PostgresValue<'a>, Json, Null, Scalar> {
362        json_get_path(self, path)
363    }
364
365    /// Get JSON object at path as text (`#>>` operator).
366    fn json_get_path_text(
367        self,
368        path: &'a str,
369    ) -> SQLExpr<'a, PostgresValue<'a>, Text, Null, Scalar> {
370        json_get_path_text(self, path)
371    }
372
373    /// JSONB contains (`@>` operator).
374    fn jsonb_contains<R>(self, other: R) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
375    where
376        R: ToSQL<'a, PostgresValue<'a>>,
377    {
378        jsonb_contains(self, other)
379    }
380
381    /// JSONB is contained by (`<@` operator).
382    fn jsonb_contained<R>(
383        self,
384        other: R,
385    ) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar>
386    where
387        R: ToSQL<'a, PostgresValue<'a>>,
388    {
389        jsonb_contained(self, other)
390    }
391
392    /// JSONB key exists (`?` operator).
393    fn jsonb_exists_key(
394        self,
395        key: &'a str,
396    ) -> SQLExpr<'a, PostgresValue<'a>, Boolean, NonNull, Scalar> {
397        jsonb_exists_key(self, key)
398    }
399}
400
401/// Blanket implementation for all `PostgreSQL` `Expr` types.
402impl<'a, E: Expr<'a, PostgresValue<'a>>> JsonExprExt<'a> for E {}
drizzle_postgres/expr/json_ops.rs

drizzle_postgres/expr/
json_ops.rs
