diesel/sqlite/expression/functions.rs
1//! SQLite specific functions
2#[cfg(doc)]
3use crate::expression::functions::aggregate_expressions::AggregateExpressionMethods;
4use crate::expression::functions::declare_sql_function;
5use crate::sql_types::*;
6use crate::sqlite::expression::expression_methods::BinaryOrNullableBinary;
7use crate::sqlite::expression::expression_methods::JsonOrNullableJson;
8use crate::sqlite::expression::expression_methods::JsonOrNullableJsonOrJsonbOrNullableJsonb;
9use crate::sqlite::expression::expression_methods::MaybeNullableValue;
10use crate::sqlite::expression::expression_methods::NotBlob;
11use crate::sqlite::expression::expression_methods::TextOrNullableText;
12use crate::sqlite::expression::expression_methods::TextOrNullableTextOrBinaryOrNullableBinary;
13use crate::sqlite::expression::functions::helper::CombinedNullableValue;
14
15#[cfg(feature = "sqlite")]
16#[declare_sql_function(generate_return_type_helpers = true)]
17#[backends(crate::sqlite::Sqlite)]
18extern "SQL" {
19 /// Verifies that its argument is a valid JSON string or JSONB blob and returns a minified
20 /// version of that JSON string with all unnecessary whitespace removed.
21 ///
22 /// # Example
23 ///
24 /// ```rust
25 /// # include!("../../doctest_setup.rs");
26 /// #
27 /// # fn main() {
28 /// # #[cfg(feature = "serde_json")]
29 /// # run_test().unwrap();
30 /// # }
31 /// #
32 /// # #[cfg(feature = "serde_json")]
33 /// # fn run_test() -> QueryResult<()> {
34 /// # use diesel::dsl::json;
35 /// # use serde_json::{json, Value};
36 /// # use diesel::sql_types::{Text, Nullable};
37 /// # let connection = &mut establish_connection();
38 ///
39 /// let result = diesel::select(json::<Text, _>(r#"{"a": "b", "c": 1}"#))
40 /// .get_result::<Value>(connection)?;
41 ///
42 /// assert_eq!(json!({"a":"b","c":1}), result);
43 ///
44 /// let result = diesel::select(json::<Text, _>(r#"{ "this" : "is", "a": [ "test" ] }"#))
45 /// .get_result::<Value>(connection)?;
46 ///
47 /// assert_eq!(json!({"a":["test"],"this":"is"}), result);
48 ///
49 /// let result = diesel::select(json::<Nullable<Text>, _>(None::<&str>))
50 /// .get_result::<Option<Value>>(connection)?;
51 ///
52 /// assert!(result.is_none());
53 ///
54 /// # Ok(())
55 /// # }
56 /// ```
57 fn json<E: TextOrNullableText + MaybeNullableValue<Json>>(e: E) -> E::Out;
58
59 /// The jsonb(X) function returns the binary JSONB representation of the JSON provided as argument X.
60 ///
61 /// This function requires at least SQLite 3.45 or newer
62 ///
63 /// # Example
64 ///
65 /// ```rust
66 /// # include!("../../doctest_setup.rs");
67 /// #
68 /// # fn main() {
69 /// # #[cfg(feature = "serde_json")]
70 /// # run_test().unwrap();
71 /// # }
72 /// #
73 /// # #[cfg(feature = "serde_json")]
74 /// # fn run_test() -> QueryResult<()> {
75 /// # use diesel::dsl::{sql, jsonb};
76 /// # use serde_json::{json, Value};
77 /// # use diesel::sql_types::{Text, Binary, Nullable};
78 /// # let connection = &mut establish_connection();
79 /// # assert_version!(connection, 3, 45, 0);
80 ///
81 /// let result = diesel::select(jsonb::<Binary, _>(br#"{"a": "b", "c": 1}"#))
82 /// .get_result::<Value>(connection)?;
83 ///
84 /// assert_eq!(json!({"a": "b", "c": 1}), result);
85 ///
86 /// let result = diesel::select(jsonb::<Binary, _>(br#"{"this":"is","a":["test"]}"#))
87 /// .get_result::<Value>(connection)?;
88 ///
89 /// assert_eq!(json!({"this":"is","a":["test"]}), result);
90 ///
91 /// let result = diesel::select(jsonb::<Nullable<Binary>, _>(None::<Vec<u8>>))
92 /// .get_result::<Option<Value>>(connection)?;
93 ///
94 /// assert!(result.is_none());
95 ///
96 /// # Ok(())
97 /// # }
98 /// ```
99 fn jsonb<E: BinaryOrNullableBinary + MaybeNullableValue<Jsonb>>(e: E) -> E::Out;
100
101 /// The json_array_length(X) function returns the number of elements in the JSON array X,
102 /// or 0 if X is some kind of JSON value other than an array.
103 /// Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
104 ///
105 /// This function requires at least SQLite 3.46 or newer
106 ///
107 /// # Example
108 ///
109 /// ```rust
110 /// # include!("../../doctest_setup.rs");
111 /// #
112 /// # fn main() {
113 /// # #[cfg(feature = "serde_json")]
114 /// # run_test().unwrap();
115 /// # }
116 /// #
117 /// # #[cfg(feature = "serde_json")]
118 /// # fn run_test() -> QueryResult<()> {
119 /// # use diesel::dsl::{sql, json_array_length};
120 /// # use serde_json::{json, Value};
121 /// # use diesel::sql_types::{Json, Jsonb, Text, Nullable};
122 /// # let connection = &mut establish_connection();
123 /// # assert_version!(connection, 3, 46, 0);
124 ///
125 /// let result = diesel::select(json_array_length::<Json, _>(json!([1,2,3,4])))
126 /// .get_result::<i32>(connection)?;
127 ///
128 /// assert_eq!(4, result);
129 ///
130 /// let result = diesel::select(json_array_length::<Json, _>(json!({"one":[1,2,3]})))
131 /// .get_result::<i32>(connection)?;
132 ///
133 /// assert_eq!(0, result);
134 ///
135 /// let result = diesel::select(json_array_length::<Nullable<Json>, _>(None::<Value>))
136 /// .get_result::<Option<i32>>(connection)?;
137 ///
138 /// assert_eq!(None, result);
139 ///
140 /// let result = diesel::select(json_array_length::<Jsonb, _>(json!([1,2,3,4])))
141 /// .get_result::<i32>(connection)?;
142 ///
143 /// assert_eq!(4, result);
144 ///
145 /// let result = diesel::select(json_array_length::<Jsonb, _>(json!({"one":[1,2,3]})))
146 /// .get_result::<i32>(connection)?;
147 ///
148 /// assert_eq!(0, result);
149 ///
150 /// let result = diesel::select(json_array_length::<Nullable<Jsonb>, _>(None::<Value>))
151 /// .get_result::<Option<i32>>(connection)?;
152 ///
153 /// assert_eq!(None, result);
154 ///
155 /// # Ok(())
156 /// # }
157 /// ```
158 #[cfg(feature = "sqlite")]
159 fn json_array_length<
160 J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Integer>,
161 >(
162 j: J,
163 ) -> J::Out;
164
165 /// The json_array_length(X) function returns the number of elements in the JSON array X,
166 /// or 0 if X is some kind of JSON value other than an array.
167 /// The json_array_length(X,P) locates the array at path P within X and returns the length of that array,
168 /// or 0 if path P locates an element in X that is not a JSON array,
169 /// and NULL if path P does not locate any element of X.
170 /// Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
171 ///
172 /// This function requires at least SQLite 3.46 or newer
173 ///
174 /// # Example
175 ///
176 /// ```rust
177 /// # include!("../../doctest_setup.rs");
178 /// #
179 /// # fn main() {
180 /// # #[cfg(feature = "serde_json")]
181 /// # run_test().unwrap();
182 /// # }
183 /// #
184 /// # #[cfg(feature = "serde_json")]
185 /// # fn run_test() -> QueryResult<()> {
186 /// # use diesel::dsl::{sql, json_array_length_with_path};
187 /// # use serde_json::{json, Value};
188 /// # use diesel::sql_types::{Json, Jsonb, Text, Nullable};
189 /// # let connection = &mut establish_connection();
190 /// # assert_version!(connection, 3, 46, 0);
191 ///
192 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!([1,2,3,4]), "$"))
193 /// .get_result::<Option<i32>>(connection)?;
194 ///
195 /// assert_eq!(Some(4), result);
196 ///
197 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!([1,2,3,4]), "$[2]"))
198 /// .get_result::<Option<i32>>(connection)?;
199 ///
200 /// assert_eq!(Some(0), result);
201 ///
202 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!({"one":[1,2,3]}), "$.one"))
203 /// .get_result::<Option<i32>>(connection)?;
204 ///
205 /// assert_eq!(Some(3), result);
206 ///
207 /// let result = diesel::select(json_array_length_with_path::<Nullable<Json>, _, _>(json!({"one":[1,2,3]}), "$.two"))
208 /// .get_result::<Option<i32>>(connection)?;
209 ///
210 /// assert_eq!(None, result);
211 ///
212 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!([1,2,3,4]), "$"))
213 /// .get_result::<Option<i32>>(connection)?;
214 ///
215 /// assert_eq!(Some(4), result);
216 ///
217 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!([1,2,3,4]), "$[2]"))
218 /// .get_result::<Option<i32>>(connection)?;
219 ///
220 /// assert_eq!(Some(0), result);
221 ///
222 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!({"one":[1,2,3]}), "$.one"))
223 /// .get_result::<Option<i32>>(connection)?;
224 ///
225 /// assert_eq!(Some(3), result);
226 ///
227 /// let result = diesel::select(json_array_length_with_path::<Nullable<Jsonb>, _, _>(json!({"one":[1,2,3]}), "$.two"))
228 /// .get_result::<Option<i32>>(connection)?;
229 ///
230 /// assert_eq!(None, result);
231 ///
232 /// # Ok(())
233 /// # }
234 /// ```
235 #[sql_name = "json_array_length"]
236 #[cfg(feature = "sqlite")]
237 fn json_array_length_with_path<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
238 j: J,
239 path: Text,
240 ) -> Nullable<Integer>;
241
242 /// The json_error_position(X) function returns 0 if the input X is a well-formed JSON or JSON5 string.
243 /// If the input X contains one or more syntax errors, then this function returns the character position of the first syntax error.
244 /// The left-most character is position 1.
245 ///
246 /// If the input X is a BLOB, then this routine returns 0 if X is a well-formed JSONB blob. If the return value is positive,
247 /// then it represents the approximate 1-based position in the BLOB of the first detected error.
248 ///
249 /// This function requires at least SQLite 3.46 or newer
250 ///
251 /// # Example
252 ///
253 /// ```rust
254 /// # include!("../../doctest_setup.rs");
255 /// #
256 /// # fn main() {
257 /// # #[cfg(feature = "serde_json")]
258 /// # run_test().unwrap();
259 /// # }
260 /// #
261 /// # #[cfg(feature = "serde_json")]
262 /// # fn run_test() -> QueryResult<()> {
263 /// # use diesel::dsl::{sql, json_error_position};
264 /// # use diesel::sql_types::{Binary, Text, Nullable};
265 /// # let connection = &mut establish_connection();
266 /// # assert_version!(connection, 3, 46, 0);
267 ///
268 /// let result = diesel::select(json_error_position::<Text, _>(r#"{"a": "b", "c": 1}"#))
269 /// .get_result::<i32>(connection)?;
270 ///
271 /// assert_eq!(0, result);
272 ///
273 /// let result = diesel::select(json_error_position::<Text, _>(r#"{"a": b", "c": 1}"#))
274 /// .get_result::<i32>(connection)?;
275 ///
276 /// assert_eq!(7, result);
277 ///
278 /// let json5 = r#"
279 /// {
280 /// // A traditional message.
281 /// message: 'hello world',
282 ///
283 /// // A number for some reason.
284 /// n: 42,
285 /// }
286 /// "#;
287 /// let result =
288 /// diesel::select(json_error_position::<Text, _>(json5)).get_result::<i32>(connection)?;
289 ///
290 /// assert_eq!(0, result);
291 ///
292 /// let json5_with_error = r#"
293 /// {
294 /// // A traditional message.
295 /// message: hello world',
296 ///
297 /// // A number for some reason.
298 /// n: 42,
299 /// }
300 /// "#;
301 /// let result = diesel::select(json_error_position::<Text, _>(json5_with_error))
302 /// .get_result::<i32>(connection)?;
303 ///
304 /// assert_eq!(59, result);
305 ///
306 /// let result = diesel::select(json_error_position::<Nullable<Text>, _>(None::<&str>))
307 /// .get_result::<Option<i32>>(connection)?;
308 ///
309 /// assert_eq!(None, result);
310 ///
311 /// let result = diesel::select(json_error_position::<Binary, _>(br#"{"a": "b", "c": 1}"#))
312 /// .get_result::<i32>(connection)?;
313 ///
314 /// assert_eq!(0, result);
315 ///
316 /// let result = diesel::select(json_error_position::<Binary, _>(br#"{"a": b", "c": 1}"#))
317 /// .get_result::<i32>(connection)?;
318 ///
319 /// assert_eq!(7, result);
320 ///
321 /// let result = diesel::select(json_error_position::<Nullable<Binary>, _>(None::<Vec<u8>>))
322 /// .get_result::<Option<i32>>(connection)?;
323 ///
324 /// assert_eq!(None, result);
325 ///
326 /// # Ok(())
327 /// # }
328 /// ```
329 #[cfg(feature = "sqlite")]
330 fn json_error_position<
331 X: TextOrNullableTextOrBinaryOrNullableBinary + MaybeNullableValue<Integer>,
332 >(
333 x: X,
334 ) -> X::Out;
335
336 /// Converts the given json value to pretty-printed, indented text
337 ///
338 /// This function requires at least SQLite 3.46 or newer
339 ///
340 /// # Example
341 ///
342 /// ```rust
343 /// # include!("../../doctest_setup.rs");
344 /// #
345 /// # fn main() {
346 /// # #[cfg(feature = "serde_json")]
347 /// # run_test().unwrap();
348 /// # }
349 /// #
350 /// # #[cfg(feature = "serde_json")]
351 /// # fn run_test() -> QueryResult<()> {
352 /// # use diesel::dsl::{sql, json_pretty};
353 /// # use serde_json::{json, Value};
354 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
355 /// # let connection = &mut establish_connection();
356 /// # assert_version!(connection, 3, 46, 0);
357 ///
358 /// let result = diesel::select(json_pretty::<Json, _>(json!([{"f1":1,"f2":null},2,null,3])))
359 /// .get_result::<String>(connection)?;
360 ///
361 /// assert_eq!(r#"[
362 /// {
363 /// "f1": 1,
364 /// "f2": null
365 /// },
366 /// 2,
367 /// null,
368 /// 3
369 /// ]"#, result);
370 ///
371 /// let result = diesel::select(json_pretty::<Json, _>(json!({"a": 1, "b": "cd"})))
372 /// .get_result::<String>(connection)?;
373 ///
374 /// assert_eq!(r#"{
375 /// "a": 1,
376 /// "b": "cd"
377 /// }"#, result);
378 ///
379 /// let result = diesel::select(json_pretty::<Json, _>(json!("abc")))
380 /// .get_result::<String>(connection)?;
381 ///
382 /// assert_eq!(r#""abc""#, result);
383 ///
384 /// let result = diesel::select(json_pretty::<Json, _>(json!(22)))
385 /// .get_result::<String>(connection)?;
386 ///
387 /// assert_eq!(r#"22"#, result);
388 ///
389 /// let result = diesel::select(json_pretty::<Json, _>(json!(false)))
390 /// .get_result::<String>(connection)?;
391 ///
392 /// assert_eq!(r#"false"#, result);
393 ///
394 /// let result = diesel::select(json_pretty::<Json, _>(json!(null)))
395 /// .get_result::<String>(connection)?;
396 ///
397 /// assert_eq!(r#"null"#, result);
398 ///
399 /// let result = diesel::select(json_pretty::<Json, _>(json!({})))
400 /// .get_result::<String>(connection)?;
401 ///
402 /// assert_eq!(r#"{}"#, result);
403 ///
404 /// let result = diesel::select(json_pretty::<Nullable<Json>, _>(None::<Value>))
405 /// .get_result::<Option<String>>(connection)?;
406 ///
407 /// assert!(result.is_none());
408 ///
409 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!([{"f1":1,"f2":null},2,null,3])))
410 /// .get_result::<String>(connection)?;
411 ///
412 /// assert_eq!(r#"[
413 /// {
414 /// "f1": 1,
415 /// "f2": null
416 /// },
417 /// 2,
418 /// null,
419 /// 3
420 /// ]"#, result);
421 ///
422 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!({"a": 1, "b": "cd"})))
423 /// .get_result::<String>(connection)?;
424 ///
425 /// assert_eq!(r#"{
426 /// "a": 1,
427 /// "b": "cd"
428 /// }"#, result);
429 ///
430 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!("abc")))
431 /// .get_result::<String>(connection)?;
432 ///
433 /// assert_eq!(r#""abc""#, result);
434 ///
435 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(22)))
436 /// .get_result::<String>(connection)?;
437 ///
438 /// assert_eq!(r#"22"#, result);
439 ///
440 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(false)))
441 /// .get_result::<String>(connection)?;
442 ///
443 /// assert_eq!(r#"false"#, result);
444 ///
445 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(null)))
446 /// .get_result::<String>(connection)?;
447 ///
448 /// assert_eq!(r#"null"#, result);
449 ///
450 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!({})))
451 /// .get_result::<String>(connection)?;
452 ///
453 /// assert_eq!(r#"{}"#, result);
454 ///
455 /// let result = diesel::select(json_pretty::<Nullable<Jsonb>, _>(None::<Value>))
456 /// .get_result::<Option<String>>(connection)?;
457 ///
458 /// assert!(result.is_none());
459 /// # Ok(())
460 /// # }
461 /// ```
462 fn json_pretty<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>>(
463 j: J,
464 ) -> J::Out;
465
466 /// Converts the given json value to pretty-printed, indented text
467 ///
468 /// This function requires at least SQLite 3.46 or newer
469 ///
470 /// # Example
471 ///
472 /// ```rust
473 /// # include!("../../doctest_setup.rs");
474 /// #
475 /// # fn main() {
476 /// # #[cfg(feature = "serde_json")]
477 /// # run_test().unwrap();
478 /// # }
479 /// #
480 /// # #[cfg(feature = "serde_json")]
481 /// # fn run_test() -> QueryResult<()> {
482 /// # use diesel::dsl::{sql, json_pretty_with_indentation};
483 /// # use serde_json::{json, Value};
484 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
485 /// # let connection = &mut establish_connection();
486 /// # assert_version!(connection, 3, 46, 0);
487 ///
488 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!([{"f1":1,"f2":null},2,null,3]), " "))
489 /// .get_result::<String>(connection)?;
490 ///
491 /// assert_eq!(r#"[
492 /// {
493 /// "f1": 1,
494 /// "f2": null
495 /// },
496 /// 2,
497 /// null,
498 /// 3
499 /// ]"#, result);
500 ///
501 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!([{"f1":1,"f2":null},2,null,3]), None::<&str>))
502 /// .get_result::<String>(connection)?;
503 ///
504 /// assert_eq!(r#"[
505 /// {
506 /// "f1": 1,
507 /// "f2": null
508 /// },
509 /// 2,
510 /// null,
511 /// 3
512 /// ]"#, result);
513 ///
514 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!({"a": 1, "b": "cd"}), " "))
515 /// .get_result::<String>(connection)?;
516 ///
517 /// assert_eq!(r#"{
518 /// "a": 1,
519 /// "b": "cd"
520 /// }"#, result);
521 ///
522 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!("abc"), " "))
523 /// .get_result::<String>(connection)?;
524 ///
525 /// assert_eq!(r#""abc""#, result);
526 ///
527 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(22), " "))
528 /// .get_result::<String>(connection)?;
529 ///
530 /// assert_eq!(r#"22"#, result);
531 ///
532 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(false), None::<&str>))
533 /// .get_result::<String>(connection)?;
534 ///
535 /// assert_eq!(r#"false"#, result);
536 ///
537 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(null), None::<&str>))
538 /// .get_result::<String>(connection)?;
539 ///
540 /// assert_eq!(r#"null"#, result);
541 ///
542 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!({}), " "))
543 /// .get_result::<String>(connection)?;
544 ///
545 /// assert_eq!(r#"{}"#, result);
546 ///
547 /// let result = diesel::select(json_pretty_with_indentation::<Nullable<Json>, _, _>(None::<Value>, None::<&str>))
548 /// .get_result::<Option<String>>(connection)?;
549 ///
550 /// assert!(result.is_none());
551 ///
552 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!([{"f1":1,"f2":null},2,null,3]), " "))
553 /// .get_result::<String>(connection)?;
554 ///
555 /// assert_eq!(r#"[
556 /// {
557 /// "f1": 1,
558 /// "f2": null
559 /// },
560 /// 2,
561 /// null,
562 /// 3
563 /// ]"#, result);
564 ///
565 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!([{"f1":1,"f2":null},2,null,3]), None::<&str>))
566 /// .get_result::<String>(connection)?;
567 ///
568 /// assert_eq!(r#"[
569 /// {
570 /// "f1": 1,
571 /// "f2": null
572 /// },
573 /// 2,
574 /// null,
575 /// 3
576 /// ]"#, result);
577 ///
578 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!({"a": 1, "b": "cd"}), " "))
579 /// .get_result::<String>(connection)?;
580 ///
581 /// assert_eq!(r#"{
582 /// "a": 1,
583 /// "b": "cd"
584 /// }"#, result);
585 ///
586 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!("abc"), " "))
587 /// .get_result::<String>(connection)?;
588 ///
589 /// assert_eq!(r#""abc""#, result);
590 ///
591 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(22), " "))
592 /// .get_result::<String>(connection)?;
593 ///
594 /// assert_eq!(r#"22"#, result);
595 ///
596 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(false), None::<&str>))
597 /// .get_result::<String>(connection)?;
598 ///
599 /// assert_eq!(r#"false"#, result);
600 ///
601 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(null), None::<&str>))
602 /// .get_result::<String>(connection)?;
603 ///
604 /// assert_eq!(r#"null"#, result);
605 ///
606 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!({}), " "))
607 /// .get_result::<String>(connection)?;
608 ///
609 /// assert_eq!(r#"{}"#, result);
610 ///
611 /// let result = diesel::select(json_pretty_with_indentation::<Nullable<Jsonb>, _, _>(None::<Value>, None::<&str>))
612 /// .get_result::<Option<String>>(connection)?;
613 ///
614 /// assert!(result.is_none());
615 ///
616 /// # Ok(())
617 /// # }
618 /// ```
619 #[sql_name = "json_pretty"]
620 fn json_pretty_with_indentation<
621 J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>,
622 >(
623 j: J,
624 indentation: Nullable<Text>,
625 ) -> J::Out;
626
627 /// Returns `true` if the argument is well-formed JSON, or returns `false` if is not well-formed.
628 ///
629 /// This function requires at least SQLite 3.46 or newer
630 ///
631 /// # Example
632 ///
633 /// ```rust
634 /// # include!("../../doctest_setup.rs");
635 /// #
636 /// # fn main() {
637 /// # #[cfg(feature = "serde_json")]
638 /// # run_test().unwrap();
639 /// # }
640 /// #
641 /// # #[cfg(feature = "serde_json")]
642 /// # fn run_test() -> QueryResult<()> {
643 /// # use diesel::dsl::{sql, json_valid};
644 /// # use serde_json::{json, Value};
645 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
646 /// # let connection = &mut establish_connection();
647 /// # assert_version!(connection, 3, 46, 0);
648 ///
649 /// let result = diesel::select(json_valid::<Json, _>(json!({"x":35})))
650 /// .get_result::<bool>(connection)?;
651 ///
652 /// assert_eq!(true, result);
653 ///
654 /// let result = diesel::select(json_valid::<Nullable<Json>, _>(None::<serde_json::Value>))
655 /// .get_result::<Option<bool>>(connection)?;
656 ///
657 /// assert_eq!(None, result);
658 ///
659 /// # Ok(())
660 /// # }
661 /// ```
662 #[sql_name = "json_valid"]
663 #[cfg(feature = "sqlite")]
664 fn json_valid<J: JsonOrNullableJson + MaybeNullableValue<Bool>>(j: J) -> J::Out;
665
666 /// The json_type(X) function returns the "type" of the outermost element of X.
667 /// The "type" returned by json_type() is one of the following SQL text values:
668 /// 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
669 ///
670 /// This function requires at least SQLite 3.38 or newer
671 ///
672 /// # Example
673 ///
674 /// ```rust
675 /// # include!("../../doctest_setup.rs");
676 /// #
677 /// # fn main() {
678 /// # #[cfg(feature = "serde_json")]
679 /// # run_test().unwrap();
680 /// # }
681 /// #
682 /// # #[cfg(feature = "serde_json")]
683 /// # fn run_test() -> QueryResult<()> {
684 /// # use diesel::dsl::{sql, json_type};
685 /// # use serde_json::{json, Value};
686 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
687 /// # let connection = &mut establish_connection();
688 /// # assert_version!(connection, 3, 38, 0);
689 ///
690 /// let result = diesel::select(json_type::<Json, _>(json!({"a": [2, 3.5, true, false, null, "x"]})))
691 /// .get_result::<String>(connection)?;
692 ///
693 /// assert_eq!("object", result);
694 ///
695 /// let result = diesel::select(json_type::<Jsonb, _>(json!({"a": [2, 3.5, true, false, null, "x"]})))
696 /// .get_result::<String>(connection)?;
697 ///
698 /// assert_eq!("object", result);
699 ///
700 /// let result = diesel::select(json_type::<Nullable<Json>, _>(None::<serde_json::Value>))
701 /// .get_result::<Option<String>>(connection)?;
702 ///
703 /// assert_eq!(None, result);
704 ///
705 /// # Ok(())
706 /// # }
707 /// ```
708 #[sql_name = "json_type"]
709 #[cfg(feature = "sqlite")]
710 fn json_type<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>>(
711 j: J,
712 ) -> J::Out;
713
714 /// The json_type(X,P) function returns the "type" of the element in X that is selected by path P.
715 /// If the path P in json_type(X,P) selects an element that does not exist in X, then this function returns NULL.
716 ///
717 /// This function requires at least SQLite 3.38 or newer
718 ///
719 /// # Example
720 ///
721 /// ```rust
722 /// # include!("../../doctest_setup.rs");
723 /// #
724 /// # fn main() {
725 /// # #[cfg(feature = "serde_json")]
726 /// # run_test().unwrap();
727 /// # }
728 /// #
729 /// # #[cfg(feature = "serde_json")]
730 /// # fn run_test() -> QueryResult<()> {
731 /// # use diesel::dsl::{sql, json_type_with_path};
732 /// # use serde_json::{json, Value};
733 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
734 /// # let connection = &mut establish_connection();
735 /// # assert_version!(connection, 3, 38, 0);
736 ///
737 /// let json_value = json!({"a": [2, 3.5, true, false, null, "x"]});
738 ///
739 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a"))
740 /// .get_result::<Option<String>>(connection)?;
741 ///
742 /// assert_eq!(Some("array".to_string()), result);
743 ///
744 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[0]"))
745 /// .get_result::<Option<String>>(connection)?;
746 ///
747 /// assert_eq!(Some("integer".to_string()), result);
748 ///
749 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[1]"))
750 /// .get_result::<Option<String>>(connection)?;
751 ///
752 /// assert_eq!(Some("real".to_string()), result);
753 ///
754 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[2]"))
755 /// .get_result::<Option<String>>(connection)?;
756 ///
757 /// assert_eq!(Some("true".to_string()), result);
758 ///
759 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[6]"))
760 /// .get_result::<Option<String>>(connection)?;
761 ///
762 /// assert_eq!(None, result);
763 ///
764 /// let result = diesel::select(json_type_with_path::<Jsonb, _, _>(json_value.clone(), "$.a"))
765 /// .get_result::<Option<String>>(connection)?;
766 ///
767 /// assert_eq!(Some("array".to_string()), result);
768 ///
769 /// let result = diesel::select(json_type_with_path::<Nullable<Json>, _, _>(None::<serde_json::Value>, "$.a"))
770 /// .get_result::<Option<String>>(connection)?;
771 ///
772 /// assert_eq!(None, result);
773 ///
774 /// # Ok(())
775 /// # }
776 /// ```
777 #[sql_name = "json_type"]
778 #[cfg(feature = "sqlite")]
779 fn json_type_with_path<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
780 j: J,
781 path: Text,
782 ) -> Nullable<Text>;
783
784 /// The json_quote(X) function converts the SQL value X (a number or a string) into its corresponding JSON
785 /// representation. If X is a JSON value returned by another JSON function, then this function is a no-op.
786 ///
787 /// This function requires at least SQLite 3.38 or newer
788 ///
789 /// # Example
790 ///
791 /// ```rust
792 /// # include!("../../doctest_setup.rs");
793 /// #
794 /// # fn main() {
795 /// # #[cfg(feature = "serde_json")]
796 /// # run_test().unwrap();
797 /// # }
798 /// #
799 /// # #[cfg(feature = "serde_json")]
800 /// # fn run_test() -> QueryResult<()> {
801 /// # use diesel::dsl::{sql, json_quote};
802 /// # use serde_json::{json, Value};
803 /// # use diesel::sql_types::{Text, Json, Integer, Float, Double, Nullable};
804 /// # let connection = &mut establish_connection();
805 /// # assert_version!(connection, 3, 38, 0);
806 ///
807 /// let result = diesel::select(json_quote::<Integer, _>(42))
808 /// .get_result::<Value>(connection)?;
809 /// assert_eq!(json!(42), result);
810 ///
811 /// let result = diesel::select(json_quote::<Text, _>("verdant"))
812 /// .get_result::<Value>(connection)?;
813 /// assert_eq!(json!("verdant"), result);
814 ///
815 /// let result = diesel::select(json_quote::<Text, _>("[1]"))
816 /// .get_result::<Value>(connection)?;
817 /// assert_eq!(json!("[1]"), result);
818 ///
819 /// let result = diesel::select(json_quote::<Nullable<Text>, _>(None::<&str>))
820 /// .get_result::<Value>(connection)?;
821 /// assert_eq!(json!(null), result);
822 ///
823 /// let result = diesel::select(json_quote::<Double, _>(3.14159))
824 /// .get_result::<Value>(connection)?;
825 /// assert_eq!(json!(3.14159), result);
826 ///
827 /// let result = diesel::select(json_quote::<Json, _>(json!([1])))
828 /// .get_result::<Value>(connection)?;
829 // assert_eq!(json!([1]), result);
830 ///
831 ///
832 /// # Ok(())
833 /// # }
834 /// ```
835 #[sql_name = "json_quote"]
836 #[cfg(feature = "sqlite")]
837 fn json_quote<J: SqlType + SingleValue>(j: J) -> Json;
838
839 /// The `json_group_array(X)` function is an aggregate SQL function that returns a JSON array comprised of
840 /// all X values in the aggregation.
841 ///
842 /// ## Aggregate Function Expression
843 ///
844 /// This function can be used as aggregate expression. See [`AggregateExpressionMethods`] for details.
845 ///
846 /// # Examples
847 ///
848 /// ## Normal function usage
849 ///
850 /// ```rust
851 /// # include!("../../doctest_setup.rs");
852 /// #
853 /// # fn main() {
854 /// # #[cfg(feature = "serde_json")]
855 /// # run_test().unwrap();
856 /// # }
857 /// #
858 /// # #[cfg(feature = "serde_json")]
859 /// # fn run_test() -> QueryResult<()> {
860 /// # use diesel::dsl::*;
861 /// # use schema::animals::dsl::*;
862 /// # use serde_json::json;
863 /// #
864 /// # let connection = &mut establish_connection();
865 /// #
866 /// let result = animals
867 /// .select(json_group_array(species))
868 /// .get_result::<serde_json::Value>(connection)?;
869 /// assert_eq!(result, json!(["dog", "spider"]));
870 ///
871 /// let result = animals
872 /// .select(json_group_array(legs))
873 /// .get_result::<serde_json::Value>(connection)?;
874 /// assert_eq!(result, json!([4, 8]));
875 ///
876 /// let result = animals
877 /// .select(json_group_array(name))
878 /// .get_result::<serde_json::Value>(connection)?;
879 /// assert_eq!(result, json!(["Jack", null]));
880 ///
881 /// # Ok(())
882 /// # }
883 /// ```
884 /// ## Aggregate function expression
885 ///
886 /// ```rust
887 /// # include!("../../doctest_setup.rs");
888 /// #
889 /// # fn main() {
890 /// # #[cfg(feature = "serde_json")]
891 /// # run_test().unwrap();
892 /// # }
893 /// #
894 /// # #[cfg(feature = "serde_json")]
895 /// # fn run_test() -> QueryResult<()> {
896 /// # use diesel::dsl::*;
897 /// # use schema::animals::dsl::*;
898 /// # use serde_json::json;
899 /// #
900 /// # let connection = &mut establish_connection();
901 /// #
902 /// let result = animals
903 /// .select(json_group_array(species).aggregate_filter(legs.lt(8)))
904 /// .get_result::<serde_json::Value>(connection)?;
905 /// assert_eq!(result, json!(["dog"]));
906 ///
907 /// # Ok(())
908 /// # }
909 /// ```
910 ///
911 /// # See also
912 /// - [`jsonb_group_array`](jsonb_group_array()) will return data in JSONB format instead of JSON.
913 /// - [`json_group_object`](json_group_object()) will return JSON object instead of array.
914 #[cfg(feature = "sqlite")]
915 #[aggregate]
916 fn json_group_array<E: SqlType + SingleValue>(elements: E) -> Json;
917
918 /// The `jsonb_group_array(X)` function is an aggregate SQL function that returns a JSONB array comprised of
919 /// all X values in the aggregation.
920 ///
921 /// ## Aggregate Function Expression
922 ///
923 /// This function can be used as aggregate expression. See [`AggregateExpressionMethods`] for details.
924 ///
925 /// # Examples
926 ///
927 /// ## Normal function usage
928 ///
929 /// ```rust
930 /// # include!("../../doctest_setup.rs");
931 /// #
932 /// # fn main() {
933 /// # #[cfg(feature = "serde_json")]
934 /// # run_test().unwrap();
935 /// # }
936 /// #
937 /// # #[cfg(feature = "serde_json")]
938 /// # fn run_test() -> QueryResult<()> {
939 /// # use diesel::dsl::*;
940 /// # use schema::animals::dsl::*;
941 /// # use serde_json::json;
942 /// #
943 /// # let connection = &mut establish_connection();
944 /// #
945 /// let result = animals
946 /// .select(json_group_array(species))
947 /// .get_result::<serde_json::Value>(connection)?;
948 /// assert_eq!(result, json!(["dog", "spider"]));
949 ///
950 /// let result = animals
951 /// .select(json_group_array(legs))
952 /// .get_result::<serde_json::Value>(connection)?;
953 /// assert_eq!(result, json!([4, 8]));
954 ///
955 /// let result = animals
956 /// .select(json_group_array(name))
957 /// .get_result::<serde_json::Value>(connection)?;
958 /// assert_eq!(result, json!(["Jack", null]));
959 ///
960 /// # Ok(())
961 /// # }
962 /// ```
963 ///
964 /// ## Aggregate function expression
965 ///
966 /// ```rust
967 /// # include!("../../doctest_setup.rs");
968 /// #
969 /// # fn main() {
970 /// # #[cfg(feature = "serde_json")]
971 /// # run_test().unwrap();
972 /// # }
973 /// #
974 /// # #[cfg(feature = "serde_json")]
975 /// # fn run_test() -> QueryResult<()> {
976 /// # use diesel::dsl::*;
977 /// # use schema::animals::dsl::*;
978 /// # use serde_json::json;
979 /// #
980 /// # let connection = &mut establish_connection();
981 /// #
982 /// let result = animals
983 /// .select(json_group_array(species).aggregate_filter(legs.lt(8)))
984 /// .get_result::<serde_json::Value>(connection)?;
985 /// assert_eq!(result, json!(["dog"]));
986 ///
987 /// # Ok(())
988 /// # }
989 /// ```
990 ///
991 /// # See also
992 /// - [`json_group_array`](json_group_array()) will return data in JSON format instead of JSONB.
993 /// - [`jsonb_group_object`](jsonb_group_object()) will return JSONB object instead of array.
994 #[cfg(feature = "sqlite")]
995 #[aggregate]
996 fn jsonb_group_array<E: SqlType + SingleValue>(elements: E) -> Jsonb;
997
998 /// The json_group_object(NAME,VALUE) function returns a JSON object comprised of all NAME/VALUE pairs in
999 /// the aggregation.
1000 ///
1001 /// A potential edge case in this function arises when `names` contains duplicate elements.
1002 /// In such case, the result will include all duplicates (e.g., `{"key": 1, "key": 2, "key": 3}`).
1003 /// Note that any duplicate entries in the resulting JSON will be removed during deserialization.
1004 ///
1005 /// This function requires at least SQLite 3.38 or newer
1006 ///
1007 /// ## Aggregate Function Expression
1008 ///
1009 /// This function can be used as aggregate expression. See [`AggregateExpressionMethods`] for details.
1010 ///
1011 /// # Examples
1012 ///
1013 /// ## Normal function usage
1014 ///
1015 /// ```rust
1016 /// # include!("../../doctest_setup.rs");
1017 /// #
1018 /// # fn main() {
1019 /// # #[cfg(feature = "serde_json")]
1020 /// # run_test().unwrap();
1021 /// # }
1022 /// #
1023 /// # #[cfg(feature = "serde_json")]
1024 /// # fn run_test() -> QueryResult<()> {
1025 /// # use diesel::dsl::*;
1026 /// # use diesel::sql_types::Text;
1027 /// # use serde_json::json;
1028 /// # use schema::animals::dsl::*;
1029 /// #
1030 /// # let connection = &mut establish_connection();
1031 /// # assert_version!(connection, 3, 38, 0);
1032 /// #
1033 /// let result = animals.select(json_group_object(species, name)).get_result::<serde_json::Value>(connection)?;
1034 /// assert_eq!(json!({"dog":"Jack","spider":null}), result);
1035 /// #
1036 /// # Ok(())
1037 /// # }
1038 /// ```
1039 ///
1040 /// ## Aggregate function expression
1041 ///
1042 /// ```rust
1043 /// # include!("../../doctest_setup.rs");
1044 /// #
1045 /// # fn main() {
1046 /// # #[cfg(feature = "serde_json")]
1047 /// # run_test().unwrap();
1048 /// # }
1049 /// #
1050 /// # #[cfg(feature = "serde_json")]
1051 /// # fn run_test() -> QueryResult<()> {
1052 /// # use diesel::dsl::*;
1053 /// # use diesel::sql_types::Text;
1054 /// # use serde_json::json;
1055 /// # use schema::animals::dsl::*;
1056 /// #
1057 /// # let connection = &mut establish_connection();
1058 /// #
1059 /// # let version = diesel::select(sql::<Text>("sqlite_version();"))
1060 /// # .get_result::<String>(connection)?;
1061 /// #
1062 /// # let version_components: Vec<&str> = version.split('.').collect();
1063 /// # let major: u32 = version_components[0].parse().unwrap();
1064 /// # let minor: u32 = version_components[1].parse().unwrap();
1065 /// #
1066 /// # if major < 3 || minor < 38 {
1067 /// # println!("SQLite version is too old, skipping the test.");
1068 /// # return Ok(());
1069 /// # }
1070 /// #
1071 /// let result = animals.select(json_group_object(species, name).aggregate_filter(legs.lt(8))).get_result::<serde_json::Value>(connection)?;
1072 /// assert_eq!(json!({"dog":"Jack"}), result);
1073 /// #
1074 /// # Ok(())
1075 /// # }
1076 /// ```
1077 ///
1078 /// # See also
1079 /// - [`jsonb_group_object`](jsonb_group_object()) will return data in JSONB format instead of JSON.
1080 /// - [`json_group_array`](json_group_array()) will return JSON array instead of object.
1081 #[cfg(feature = "sqlite")]
1082 #[aggregate]
1083 fn json_group_object<
1084 N: SqlType<IsNull = is_nullable::NotNull> + SingleValue,
1085 V: SqlType + SingleValue,
1086 >(
1087 names: N,
1088 values: V,
1089 ) -> Json;
1090
1091 /// The jsonb_group_object(NAME,VALUE) function returns a JSONB object comprised of all NAME/VALUE pairs in
1092 /// the aggregation.
1093 ///
1094 /// A potential edge case in this function arises when `names` contains duplicate elements.
1095 /// In such case, the result will include all duplicates (e.g., `{"key": 1, "key": 2, "key": 3}`).
1096 /// Note that any duplicate entries in the resulting JSONB will be removed during deserialization.
1097 ///
1098 /// This function requires at least SQLite 3.38 or newer
1099 ///
1100 /// ## Aggregate Function Expression
1101 ///
1102 /// This function can be used as aggregate expression. See [`AggregateExpressionMethods`] for details.
1103 ///
1104 /// # Examples
1105 ///
1106 /// ## Normal function usage
1107 ///
1108 /// ```rust
1109 /// # include!("../../doctest_setup.rs");
1110 /// #
1111 /// # fn main() {
1112 /// # #[cfg(feature = "serde_json")]
1113 /// # run_test().unwrap();
1114 /// # }
1115 /// #
1116 /// # #[cfg(feature = "serde_json")]
1117 /// # fn run_test() -> QueryResult<()> {
1118 /// # use diesel::dsl::*;
1119 /// # use diesel::sql_types::Text;
1120 /// # use serde_json::json;
1121 /// # use schema::animals::dsl::*;
1122 /// #
1123 /// # let connection = &mut establish_connection();
1124 /// # assert_version!(connection, 3, 38, 0);
1125 /// #
1126 /// let result = animals.select(jsonb_group_object(species, name)).get_result::<serde_json::Value>(connection)?;
1127 /// assert_eq!(json!({"dog":"Jack","spider":null}), result);
1128 /// #
1129 /// # Ok(())
1130 /// # }
1131 /// ```
1132 ///
1133 /// ## Aggregate function expression
1134 ///
1135 /// ```rust
1136 /// # include!("../../doctest_setup.rs");
1137 /// #
1138 /// # fn main() {
1139 /// # #[cfg(feature = "serde_json")]
1140 /// # run_test().unwrap();
1141 /// # }
1142 /// #
1143 /// # #[cfg(feature = "serde_json")]
1144 /// # fn run_test() -> QueryResult<()> {
1145 /// # use diesel::dsl::*;
1146 /// # use diesel::sql_types::Text;
1147 /// # use serde_json::json;
1148 /// # use schema::animals::dsl::*;
1149 /// #
1150 /// # let connection = &mut establish_connection();
1151 /// #
1152 /// # let version = diesel::select(sql::<Text>("sqlite_version();"))
1153 /// # .get_result::<String>(connection)?;
1154 /// #
1155 /// # let version_components: Vec<&str> = version.split('.').collect();
1156 /// # let major: u32 = version_components[0].parse().unwrap();
1157 /// # let minor: u32 = version_components[1].parse().unwrap();
1158 /// #
1159 /// # if major < 3 || minor < 38 {
1160 /// # println!("SQLite version is too old, skipping the test.");
1161 /// # return Ok(());
1162 /// # }
1163 /// #
1164 /// let result = animals.select(jsonb_group_object(species, name).aggregate_filter(legs.lt(8))).get_result::<serde_json::Value>(connection)?;
1165 /// assert_eq!(json!({"dog":"Jack"}), result);
1166 /// #
1167 /// # Ok(())
1168 /// # }
1169 /// ```
1170 ///
1171 /// # See also
1172 /// - [`json_group_object`](jsonb_group_array()) will return data in JSON format instead of JSONB.
1173 /// - [`jsonb_group_array`](jsonb_group_array()) will return JSONB array instead of object.
1174 #[cfg(feature = "sqlite")]
1175 #[aggregate]
1176 fn jsonb_group_object<
1177 N: SqlType<IsNull = is_nullable::NotNull> + SingleValue,
1178 V: SqlType + SingleValue,
1179 >(
1180 names: N,
1181 values: V,
1182 ) -> Jsonb;
1183
1184 /// The `json_array()` SQL function accepts zero or more arguments and returns a well-formed JSON array
1185 /// that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this
1186 /// function.
1187 ///
1188 /// An argument with SQL type TEXT is normally converted into a quoted JSON string. However, if the
1189 /// argument is the output from another json function, then it is stored as JSON. This allows calls to
1190 /// `json_array()` and `json_object()` to be nested. The [`json()`] function can also be used to force
1191 /// strings to be recognized as JSON.
1192 ///
1193 /// This function requires at least SQLite 3.38 or newer
1194 ///
1195 /// # Examples
1196 ///
1197 /// ```rust
1198 /// # include!("../../doctest_setup.rs");
1199 /// #
1200 /// # fn main() {
1201 /// # #[cfg(feature = "serde_json")]
1202 /// # run_test().unwrap();
1203 /// # }
1204 /// #
1205 /// # #[cfg(feature = "serde_json")]
1206 /// # fn run_test() -> QueryResult<()> {
1207 /// # use diesel::dsl::*;
1208 /// # use diesel::sql_types::{Text, Double};
1209 /// # use serde_json::json;
1210 /// #
1211 /// # let connection = &mut establish_connection();
1212 /// # assert_version!(connection, 3, 38, 0);
1213 /// #
1214 /// let result = diesel::select(json_array_0()).get_result::<serde_json::Value>(connection)?;
1215 /// assert_eq!(json!([]), result);
1216 ///
1217 /// let result = diesel::select(json_array_1::<Text, _>("abc"))
1218 /// .get_result::<serde_json::Value>(connection)?;
1219 /// assert_eq!(json!(["abc"]), result);
1220 ///
1221 /// let result = diesel::select(json_array_2::<Text, Double, _, _>("abc", 3.1415))
1222 /// .get_result::<serde_json::Value>(connection)?;
1223 /// assert_eq!(json!(["abc", 3.1415]), result);
1224 /// #
1225 /// # Ok(())
1226 /// # }
1227 /// ```
1228 #[cfg(feature = "sqlite")]
1229 #[variadic(1)]
1230 fn json_array<V: NotBlob>(value: V) -> Json;
1231
1232 /// The `jsonb_array()` SQL function accepts zero or more arguments and returns a well-formed JSON array
1233 /// that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this
1234 /// function.
1235 ///
1236 /// An argument with SQL type TEXT is normally converted into a quoted JSON string. However, if the
1237 /// argument is the output from another json function, then it is stored as JSON. This allows calls to
1238 /// `jsonb_array()` and `jsonb_object()` to be nested. The [`json()`] function can also be used to force
1239 /// strings to be recognized as JSON.
1240 ///
1241 /// This function works just like the [`json_array()`](json_array_1()) function except that it returns the
1242 /// constructed JSON array in the SQLite's private JSONB format rather than in the standard RFC 8259 text
1243 /// format.
1244 ///
1245 /// This function requires at least SQLite 3.38 or newer
1246 ///
1247 /// # Examples
1248 ///
1249 /// ```rust
1250 /// # include!("../../doctest_setup.rs");
1251 /// #
1252 /// # fn main() {
1253 /// # #[cfg(feature = "serde_json")]
1254 /// # run_test().unwrap();
1255 /// # }
1256 /// #
1257 /// # #[cfg(feature = "serde_json")]
1258 /// # fn run_test() -> QueryResult<()> {
1259 /// # use diesel::dsl::*;
1260 /// # use diesel::sql_types::{Text, Double};
1261 /// # use serde_json::json;
1262 /// #
1263 /// # let connection = &mut establish_connection();
1264 /// # assert_version!(connection, 3, 38, 0);
1265 /// #
1266 /// let result = diesel::select(jsonb_array_0()).get_result::<serde_json::Value>(connection)?;
1267 /// assert_eq!(json!([]), result);
1268 ///
1269 /// let result = diesel::select(jsonb_array_1::<Text, _>("abc"))
1270 /// .get_result::<serde_json::Value>(connection)?;
1271 /// assert_eq!(json!(["abc"]), result);
1272 ///
1273 /// let result = diesel::select(jsonb_array_2::<Text, Double, _, _>("abc", 3.1415))
1274 /// .get_result::<serde_json::Value>(connection)?;
1275 /// assert_eq!(json!(["abc", 3.1415]), result);
1276 /// #
1277 /// # Ok(())
1278 /// # }
1279 /// ```
1280 #[cfg(feature = "sqlite")]
1281 #[variadic(1)]
1282 fn jsonb_array<V: NotBlob>(value: V) -> Jsonb;
1283
1284 /// The `json_remove(X,P,...)` SQL function takes a single JSON value as its first argument followed by
1285 /// zero or more path arguments. The `json_remove(X,P,...)` function returns a copy of the X parameter
1286 /// with all the elements identified by path arguments removed. Paths that select elements not found in X
1287 /// are silently ignored.
1288 ///
1289 /// Removals occurs sequentially from left to right. Changes caused by prior removals can affect the path
1290 /// search for subsequent arguments.
1291 ///
1292 /// If the `json_remove(X)` function is called with no path arguments, then it returns the input X
1293 /// reformatted, with excess whitespace removed.
1294 ///
1295 /// The `json_remove()` function throws an error if any of the path arguments is not a well-formed path.
1296 ///
1297 /// This function requires at least SQLite 3.38 or newer
1298 ///
1299 /// # Examples
1300 ///
1301 /// ```rust
1302 /// # include!("../../doctest_setup.rs");
1303 /// #
1304 /// # fn main() {
1305 /// # #[cfg(feature = "serde_json")]
1306 /// # run_test().unwrap();
1307 /// # }
1308 /// #
1309 /// # #[cfg(feature = "serde_json")]
1310 /// # fn run_test() -> QueryResult<()> {
1311 /// # use diesel::dsl::*;
1312 /// # use diesel::sql_types::{Json, Text};
1313 /// # use serde_json::json;
1314 /// #
1315 /// # let connection = &mut establish_connection();
1316 /// # assert_version!(connection, 3, 38, 0);
1317 /// #
1318 /// let json = json!(['a', 'b', 'c', 'd']);
1319 /// let result = diesel::select(json_remove_0::<Json, _>(json))
1320 /// .get_result::<Option<serde_json::Value>>(connection)?;
1321 /// assert_eq!(Some(json!(['a', 'b', 'c', 'd'])), result);
1322 ///
1323 /// // Values are removed sequentially from left to right.
1324 /// let json = json!(['a', 'b', 'c', 'd']);
1325 /// let result = diesel::select(json_remove_2::<Json, _, _, _>(json, "$[0]", "$[2]"))
1326 /// .get_result::<Option<serde_json::Value>>(connection)?;
1327 /// assert_eq!(Some(json!(['b', 'c'])), result);
1328 ///
1329 /// let json = json!({"a": 10, "b": 20});
1330 /// let result = diesel::select(json_remove_1::<Json, _, _>(json, "$.a"))
1331 /// .get_result::<Option<serde_json::Value>>(connection)?;
1332 /// assert_eq!(Some(json!({"b": 20})), result);
1333 ///
1334 /// // Paths that select not existing elements are silently ignored.
1335 /// let json = json!({"a": 10, "b": 20});
1336 /// let result = diesel::select(json_remove_1::<Json, _, _>(json, "$.c"))
1337 /// .get_result::<Option<serde_json::Value>>(connection)?;
1338 /// assert_eq!(Some(json!({"a": 10, "b": 20})), result);
1339 ///
1340 /// let json = json!({"a": 10, "b": 20});
1341 /// let result = diesel::select(json_remove_1::<Json, _, _>(json, "$"))
1342 /// .get_result::<Option<serde_json::Value>>(connection)?;
1343 /// assert_eq!(None, result);
1344 ///
1345 /// #
1346 /// # Ok(())
1347 /// # }
1348 /// ```
1349 #[cfg(feature = "sqlite")]
1350 #[variadic(1)]
1351 fn json_remove<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
1352 json: J,
1353 path: Text,
1354 ) -> Nullable<Json>;
1355
1356 /// The `jsonb_remove(X,P,...)` SQL function takes a single JSON value as its first argument followed by
1357 /// zero or more path arguments. The `jsonb_remove(X,P,...)` function returns a copy of the X parameter
1358 /// with all the elements identified by path arguments removed. Paths that select elements not found in X
1359 /// are silently ignored.
1360 ///
1361 /// Removals occurs sequentially from left to right. Changes caused by prior removals can affect the path
1362 /// search for subsequent arguments.
1363 ///
1364 /// If the `jsonb_remove(X)` function is called with no path arguments, then it returns the input X
1365 /// reformatted, with excess whitespace removed.
1366 ///
1367 /// The `jsonb_remove()` function throws an error if any of the path arguments is not a well-formed path.
1368 ///
1369 /// This function returns value in a binary JSONB format.
1370 ///
1371 /// This function requires at least SQLite 3.38 or newer
1372 ///
1373 /// # Examples
1374 ///
1375 /// ```rust
1376 /// # include!("../../doctest_setup.rs");
1377 /// #
1378 /// # fn main() {
1379 /// # #[cfg(feature = "serde_json")]
1380 /// # run_test().unwrap();
1381 /// # }
1382 /// #
1383 /// # #[cfg(feature = "serde_json")]
1384 /// # fn run_test() -> QueryResult<()> {
1385 /// # use diesel::dsl::*;
1386 /// # use diesel::sql_types::{Jsonb, Text};
1387 /// # use serde_json::json;
1388 /// #
1389 /// # let connection = &mut establish_connection();
1390 /// # assert_version!(connection, 3, 38, 0);
1391 /// #
1392 /// let json = json!(['a', 'b', 'c', 'd']);
1393 /// let result = diesel::select(jsonb_remove_0::<Jsonb, _>(json))
1394 /// .get_result::<Option<serde_json::Value>>(connection)?;
1395 /// assert_eq!(Some(json!(['a', 'b', 'c', 'd'])), result);
1396 ///
1397 /// // Values are removed sequentially from left to right.
1398 /// let json = json!(['a', 'b', 'c', 'd']);
1399 /// let result = diesel::select(jsonb_remove_2::<Jsonb, _, _, _>(json, "$[0]", "$[2]"))
1400 /// .get_result::<Option<serde_json::Value>>(connection)?;
1401 /// assert_eq!(Some(json!(['b', 'c'])), result);
1402 ///
1403 /// let json = json!({"a": 10, "b": 20});
1404 /// let result = diesel::select(jsonb_remove_1::<Jsonb, _, _>(json, "$.a"))
1405 /// .get_result::<Option<serde_json::Value>>(connection)?;
1406 /// assert_eq!(Some(json!({"b": 20})), result);
1407 ///
1408 /// // Paths that select not existing elements are silently ignored.
1409 /// let json = json!({"a": 10, "b": 20});
1410 /// let result = diesel::select(jsonb_remove_1::<Jsonb, _, _>(json, "$.c"))
1411 /// .get_result::<Option<serde_json::Value>>(connection)?;
1412 /// assert_eq!(Some(json!({"a": 10, "b": 20})), result);
1413 ///
1414 /// let json = json!({"a": 10, "b": 20});
1415 /// let result = diesel::select(jsonb_remove_1::<Jsonb, _, _>(json, "$"))
1416 /// .get_result::<Option<serde_json::Value>>(connection)?;
1417 /// assert_eq!(None, result);
1418 ///
1419 /// #
1420 /// # Ok(())
1421 /// # }
1422 /// ```
1423 #[cfg(feature = "sqlite")]
1424 #[variadic(1)]
1425 fn jsonb_remove<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
1426 json: J,
1427 path: Text,
1428 ) -> Nullable<Jsonb>;
1429
1430 /// Applies an RFC 7396 MergePatch `patch` to the input JSON `target` and
1431 /// returns the patched JSON value.
1432 ///
1433 /// MergePatch can add, modify, or delete elements of a JSON object. Arrays are
1434 /// treated as atomic values: they can only be inserted, replaced, or deleted as a
1435 /// whole, not modified element-wise.
1436 ///
1437 /// # Examples
1438 ///
1439 /// ```rust
1440 /// # include!("../../doctest_setup.rs");
1441 /// #
1442 /// # fn main() {
1443 /// # #[cfg(feature = "serde_json")]
1444 /// # run_test().unwrap();
1445 /// # }
1446 /// #
1447 /// # #[cfg(feature = "serde_json")]
1448 /// # fn run_test() -> QueryResult<()> {
1449 /// # use diesel::dsl::json_patch;
1450 /// # use serde_json::{json, Value};
1451 /// # use diesel::sql_types::{Json, Nullable};
1452 /// # let connection = &mut establish_connection();
1453 ///
1454 /// let result = diesel::select(json_patch::<Json, Json, _, _>(
1455 /// json!( {"a":1,"b":2} ),
1456 /// json!( {"c":3,"d":4} ),
1457 /// ))
1458 /// .get_result::<Value>(connection)?;
1459 /// assert_eq!(json!({"a":1,"b":2,"c":3,"d":4}), result);
1460 ///
1461 /// let result = diesel::select(json_patch::<Json, Json, _, _>(
1462 /// json!( {"a":[1,2],"b":2} ),
1463 /// json!( {"a":9} ),
1464 /// ))
1465 /// .get_result::<Value>(connection)?;
1466 /// assert_eq!(json!({"a":9,"b":2}), result);
1467 ///
1468 /// let result = diesel::select(json_patch::<Json, Json, _, _>(
1469 /// json!( {"a":[1,2],"b":2} ),
1470 /// json!( {"a":null} ),
1471 /// ))
1472 /// .get_result::<Value>(connection)?;
1473 /// assert_eq!(json!({"b":2}), result);
1474 ///
1475 /// let result = diesel::select(json_patch::<Json, Json, _, _>(
1476 /// json!( {"a":1,"b":2} ),
1477 /// json!( {"a":9,"b":null,"c":8} ),
1478 /// ))
1479 /// .get_result::<Value>(connection)?;
1480 /// assert_eq!(json!({"a":9,"c":8}), result);
1481 ///
1482 /// let result = diesel::select(json_patch::<Json, Json, _, _>(
1483 /// json!( {"a":{"x":1,"y":2},"b":3} ),
1484 /// json!( {"a":{"y":9},"c":8} ),
1485 /// ))
1486 /// .get_result::<Value>(connection)?;
1487 /// assert_eq!(
1488 /// json!({"a":{"x":1,"y":9},"b":3,"c":8}),
1489 /// result
1490 /// );
1491 ///
1492 /// // Nullable input yields nullable output
1493 /// let result = diesel::select(json_patch::<Nullable<Json>, Json, _, _>(
1494 /// None::<Value>,
1495 /// json!({}),
1496 /// ))
1497 /// .get_result::<Option<Value>>(connection)?;
1498 /// assert!(result.is_none());
1499 ///
1500 /// # Ok(())
1501 /// # }
1502 /// ```
1503 #[cfg(feature = "sqlite")]
1504 fn json_patch<
1505 T: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue,
1506 P: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue + CombinedNullableValue<T, Json>,
1507 >(
1508 target: T,
1509 patch: P,
1510 ) -> P::Out;
1511
1512 /// Applies an RFC 7396 MergePatch `patch` to the input JSON `target` and
1513 /// returns the patched JSON value in SQLite's binary JSONB format.
1514 ///
1515 /// See [`json_patch`](json_patch()) for details about the MergePatch semantics.
1516 ///
1517 /// # Examples
1518 ///
1519 /// ```rust
1520 /// # include!("../../doctest_setup.rs");
1521 /// #
1522 /// # fn main() {
1523 /// # #[cfg(feature = "serde_json")]
1524 /// # run_test().unwrap();
1525 /// # }
1526 /// #
1527 /// # #[cfg(feature = "serde_json")]
1528 /// # fn run_test() -> QueryResult<()> {
1529 /// # use diesel::dsl::jsonb_patch;
1530 /// # use serde_json::{json, Value};
1531 /// # use diesel::sql_types::{Jsonb, Nullable};
1532 /// # let connection = &mut establish_connection();
1533 /// # assert_version!(connection, 3, 45, 0);
1534 ///
1535 /// let result = diesel::select(jsonb_patch::<Jsonb, Jsonb, _, _>(
1536 /// json!( {"a":1,"b":2} ),
1537 /// json!( {"c":3,"d":4} ),
1538 /// ))
1539 /// .get_result::<Value>(connection)?;
1540 /// assert_eq!(json!({"a":1,"b":2,"c":3,"d":4}), result);
1541 ///
1542 /// // Nullable input yields nullable output
1543 /// let result = diesel::select(jsonb_patch::<Nullable<Jsonb>, Jsonb, _, _>(
1544 /// None::<Value>,
1545 /// json!({}),
1546 /// ))
1547 /// .get_result::<Option<Value>>(connection)?;
1548 /// assert!(result.is_none());
1549 ///
1550 /// # Ok(())
1551 /// # }
1552 /// ```
1553 #[cfg(feature = "sqlite")]
1554 fn jsonb_patch<
1555 T: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue,
1556 P: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue + CombinedNullableValue<T, Jsonb>,
1557 >(
1558 target: T,
1559 patch: P,
1560 ) -> P::Out;
1561}
1562
1563pub(super) mod return_type_helpers_reexported {
1564 #[allow(unused_imports)]
1565 #[doc(inline)]
1566 pub use super::return_type_helpers::*;
1567}