diesel/sqlite/expression/functions.rs
1//! SQLite specific functions
2use crate::expression::functions::declare_sql_function;
3use crate::sql_types::*;
4use crate::sqlite::expression::expression_methods::BinaryOrNullableBinary;
5use crate::sqlite::expression::expression_methods::JsonOrNullableJson;
6use crate::sqlite::expression::expression_methods::JsonOrNullableJsonOrJsonbOrNullableJsonb;
7use crate::sqlite::expression::expression_methods::MaybeNullableValue;
8use crate::sqlite::expression::expression_methods::TextOrNullableText;
9use crate::sqlite::expression::expression_methods::TextOrNullableTextOrBinaryOrNullableBinary;
10
11#[cfg(feature = "sqlite")]
12#[declare_sql_function]
13extern "SQL" {
14 /// Verifies that its argument is a valid JSON string or JSONB blob and returns a minified
15 /// version of that JSON string with all unnecessary whitespace removed.
16 ///
17 /// # Example
18 ///
19 /// ```rust
20 /// # include!("../../doctest_setup.rs");
21 /// #
22 /// # fn main() {
23 /// # #[cfg(feature = "serde_json")]
24 /// # run_test().unwrap();
25 /// # }
26 /// #
27 /// # #[cfg(feature = "serde_json")]
28 /// # fn run_test() -> QueryResult<()> {
29 /// # use diesel::dsl::json;
30 /// # use serde_json::{json, Value};
31 /// # use diesel::sql_types::{Text, Nullable};
32 /// # let connection = &mut establish_connection();
33 ///
34 /// let result = diesel::select(json::<Text, _>(r#"{"a": "b", "c": 1}"#))
35 /// .get_result::<Value>(connection)?;
36 ///
37 /// assert_eq!(json!({"a":"b","c":1}), result);
38 ///
39 /// let result = diesel::select(json::<Text, _>(r#"{ "this" : "is", "a": [ "test" ] }"#))
40 /// .get_result::<Value>(connection)?;
41 ///
42 /// assert_eq!(json!({"a":["test"],"this":"is"}), result);
43 ///
44 /// let result = diesel::select(json::<Nullable<Text>, _>(None::<&str>))
45 /// .get_result::<Option<Value>>(connection)?;
46 ///
47 /// assert!(result.is_none());
48 ///
49 /// # Ok(())
50 /// # }
51 /// ```
52 fn json<E: TextOrNullableText + MaybeNullableValue<Json>>(e: E) -> E::Out;
53
54 /// The jsonb(X) function returns the binary JSONB representation of the JSON provided as argument X.
55 ///
56 /// # Example
57 ///
58 /// ```rust
59 /// # include!("../../doctest_setup.rs");
60 /// #
61 /// # fn main() {
62 /// # #[cfg(feature = "serde_json")]
63 /// # run_test().unwrap();
64 /// # }
65 /// #
66 /// # #[cfg(feature = "serde_json")]
67 /// # fn run_test() -> QueryResult<()> {
68 /// # use diesel::dsl::{sql, jsonb};
69 /// # use serde_json::{json, Value};
70 /// # use diesel::sql_types::{Text, Binary, Nullable};
71 /// # let connection = &mut establish_connection();
72 ///
73 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
74 /// .get_result::<String>(connection)?;
75 ///
76 /// // Querying SQLite version should not fail.
77 /// let version_components: Vec<&str> = version.split('.').collect();
78 /// let major: u32 = version_components[0].parse().unwrap();
79 /// let minor: u32 = version_components[1].parse().unwrap();
80 /// let patch: u32 = version_components[2].parse().unwrap();
81 ///
82 /// if major > 3 || (major == 3 && minor >= 45) {
83 /// /* Valid sqlite version, do nothing */
84 /// } else {
85 /// println!("SQLite version is too old, skipping the test.");
86 /// return Ok(());
87 /// }
88 ///
89 /// let result = diesel::select(jsonb::<Binary, _>(br#"{"a": "b", "c": 1}"#))
90 /// .get_result::<Value>(connection)?;
91 ///
92 /// assert_eq!(json!({"a": "b", "c": 1}), result);
93 ///
94 /// let result = diesel::select(jsonb::<Binary, _>(br#"{"this":"is","a":["test"]}"#))
95 /// .get_result::<Value>(connection)?;
96 ///
97 /// assert_eq!(json!({"this":"is","a":["test"]}), result);
98 ///
99 /// let result = diesel::select(jsonb::<Nullable<Binary>, _>(None::<Vec<u8>>))
100 /// .get_result::<Option<Value>>(connection)?;
101 ///
102 /// assert!(result.is_none());
103 ///
104 /// # Ok(())
105 /// # }
106 /// ```
107 fn jsonb<E: BinaryOrNullableBinary + MaybeNullableValue<Jsonb>>(e: E) -> E::Out;
108
109 /// The json_array_length(X) function returns the number of elements in the JSON array X,
110 /// or 0 if X is some kind of JSON value other than an array.
111 /// Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
112 ///
113 /// # Example
114 ///
115 /// ```rust
116 /// # include!("../../doctest_setup.rs");
117 /// #
118 /// # fn main() {
119 /// # #[cfg(feature = "serde_json")]
120 /// # run_test().unwrap();
121 /// # }
122 /// #
123 /// # #[cfg(feature = "serde_json")]
124 /// # fn run_test() -> QueryResult<()> {
125 /// # use diesel::dsl::{sql, json_array_length};
126 /// # use serde_json::{json, Value};
127 /// # use diesel::sql_types::{Json, Jsonb, Text, Nullable};
128 /// # let connection = &mut establish_connection();
129 ///
130 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
131 /// .get_result::<String>(connection)?;
132 ///
133 /// // Querying SQLite version should not fail.
134 /// let version_components: Vec<&str> = version.split('.').collect();
135 /// let major: u32 = version_components[0].parse().unwrap();
136 /// let minor: u32 = version_components[1].parse().unwrap();
137 /// let patch: u32 = version_components[2].parse().unwrap();
138 ///
139 /// if major > 3 || (major == 3 && minor >= 46) {
140 /// /* Valid sqlite version, do nothing */
141 /// } else {
142 /// println!("SQLite version is too old, skipping the test.");
143 /// return Ok(());
144 /// }
145 ///
146 /// let result = diesel::select(json_array_length::<Json, _>(json!([1,2,3,4])))
147 /// .get_result::<i32>(connection)?;
148 ///
149 /// assert_eq!(4, result);
150 ///
151 /// let result = diesel::select(json_array_length::<Json, _>(json!({"one":[1,2,3]})))
152 /// .get_result::<i32>(connection)?;
153 ///
154 /// assert_eq!(0, result);
155 ///
156 /// let result = diesel::select(json_array_length::<Nullable<Json>, _>(None::<Value>))
157 /// .get_result::<Option<i32>>(connection)?;
158 ///
159 /// assert_eq!(None, result);
160 ///
161 /// let result = diesel::select(json_array_length::<Jsonb, _>(json!([1,2,3,4])))
162 /// .get_result::<i32>(connection)?;
163 ///
164 /// assert_eq!(4, result);
165 ///
166 /// let result = diesel::select(json_array_length::<Jsonb, _>(json!({"one":[1,2,3]})))
167 /// .get_result::<i32>(connection)?;
168 ///
169 /// assert_eq!(0, result);
170 ///
171 /// let result = diesel::select(json_array_length::<Nullable<Jsonb>, _>(None::<Value>))
172 /// .get_result::<Option<i32>>(connection)?;
173 ///
174 /// assert_eq!(None, result);
175 ///
176 /// # Ok(())
177 /// # }
178 /// ```
179 #[cfg(feature = "sqlite")]
180 fn json_array_length<
181 J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Integer>,
182 >(
183 j: J,
184 ) -> J::Out;
185
186 /// The json_array_length(X) function returns the number of elements in the JSON array X,
187 /// or 0 if X is some kind of JSON value other than an array.
188 /// The json_array_length(X,P) locates the array at path P within X and returns the length of that array,
189 /// or 0 if path P locates an element in X that is not a JSON array,
190 /// and NULL if path P does not locate any element of X.
191 /// Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
192 ///
193 /// # Example
194 ///
195 /// ```rust
196 /// # include!("../../doctest_setup.rs");
197 /// #
198 /// # fn main() {
199 /// # #[cfg(feature = "serde_json")]
200 /// # run_test().unwrap();
201 /// # }
202 /// #
203 /// # #[cfg(feature = "serde_json")]
204 /// # fn run_test() -> QueryResult<()> {
205 /// # use diesel::dsl::{sql, json_array_length_with_path};
206 /// # use serde_json::{json, Value};
207 /// # use diesel::sql_types::{Json, Jsonb, Text, Nullable};
208 /// # let connection = &mut establish_connection();
209 ///
210 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
211 /// .get_result::<String>(connection)?;
212 ///
213 /// // Querying SQLite version should not fail.
214 /// let version_components: Vec<&str> = version.split('.').collect();
215 /// let major: u32 = version_components[0].parse().unwrap();
216 /// let minor: u32 = version_components[1].parse().unwrap();
217 /// let patch: u32 = version_components[2].parse().unwrap();
218 ///
219 /// if major > 3 || (major == 3 && minor >= 46) {
220 /// /* Valid sqlite version, do nothing */
221 /// } else {
222 /// println!("SQLite version is too old, skipping the test.");
223 /// return Ok(());
224 /// }
225 ///
226 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!([1,2,3,4]), "$"))
227 /// .get_result::<Option<i32>>(connection)?;
228 ///
229 /// assert_eq!(Some(4), result);
230 ///
231 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!([1,2,3,4]), "$[2]"))
232 /// .get_result::<Option<i32>>(connection)?;
233 ///
234 /// assert_eq!(Some(0), result);
235 ///
236 /// let result = diesel::select(json_array_length_with_path::<Json, _, _>(json!({"one":[1,2,3]}), "$.one"))
237 /// .get_result::<Option<i32>>(connection)?;
238 ///
239 /// assert_eq!(Some(3), result);
240 ///
241 /// let result = diesel::select(json_array_length_with_path::<Nullable<Json>, _, _>(json!({"one":[1,2,3]}), "$.two"))
242 /// .get_result::<Option<i32>>(connection)?;
243 ///
244 /// assert_eq!(None, result);
245 ///
246 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!([1,2,3,4]), "$"))
247 /// .get_result::<Option<i32>>(connection)?;
248 ///
249 /// assert_eq!(Some(4), result);
250 ///
251 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!([1,2,3,4]), "$[2]"))
252 /// .get_result::<Option<i32>>(connection)?;
253 ///
254 /// assert_eq!(Some(0), result);
255 ///
256 /// let result = diesel::select(json_array_length_with_path::<Jsonb, _, _>(json!({"one":[1,2,3]}), "$.one"))
257 /// .get_result::<Option<i32>>(connection)?;
258 ///
259 /// assert_eq!(Some(3), result);
260 ///
261 /// let result = diesel::select(json_array_length_with_path::<Nullable<Jsonb>, _, _>(json!({"one":[1,2,3]}), "$.two"))
262 /// .get_result::<Option<i32>>(connection)?;
263 ///
264 /// assert_eq!(None, result);
265 ///
266 /// # Ok(())
267 /// # }
268 /// ```
269 #[sql_name = "json_array_length"]
270 #[cfg(feature = "sqlite")]
271 fn json_array_length_with_path<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
272 j: J,
273 path: Text,
274 ) -> Nullable<Integer>;
275
276 /// The json_error_position(X) function returns 0 if the input X is a well-formed JSON or JSON5 string.
277 /// If the input X contains one or more syntax errors, then this function returns the character position of the first syntax error.
278 /// The left-most character is position 1.
279 ///
280 /// 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,
281 /// then it represents the approximate 1-based position in the BLOB of the first detected error.
282 ///
283 /// # Example
284 ///
285 /// ```rust
286 /// # include!("../../doctest_setup.rs");
287 /// #
288 /// # fn main() {
289 /// # #[cfg(feature = "serde_json")]
290 /// # run_test().unwrap();
291 /// # }
292 /// #
293 /// # #[cfg(feature = "serde_json")]
294 /// # fn run_test() -> QueryResult<()> {
295 /// # use diesel::dsl::{sql, json_error_position};
296 /// # use diesel::sql_types::{Binary, Text, Nullable};
297 /// # let connection = &mut establish_connection();
298 ///
299 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
300 /// .get_result::<String>(connection)?;
301 ///
302 /// // Querying SQLite version should not fail.
303 /// let version_components: Vec<&str> = version.split('.').collect();
304 /// let major: u32 = version_components[0].parse().unwrap();
305 /// let minor: u32 = version_components[1].parse().unwrap();
306 /// let patch: u32 = version_components[2].parse().unwrap();
307 ///
308 /// if major > 3 || (major == 3 && minor >= 46) {
309 /// /* Valid sqlite version, do nothing */
310 /// } else {
311 /// println!("SQLite version is too old, skipping the test.");
312 /// return Ok(());
313 /// }
314 ///
315 /// let result = diesel::select(json_error_position::<Text, _>(r#"{"a": "b", "c": 1}"#))
316 /// .get_result::<i32>(connection)?;
317 ///
318 /// assert_eq!(0, result);
319 ///
320 /// let result = diesel::select(json_error_position::<Text, _>(r#"{"a": b", "c": 1}"#))
321 /// .get_result::<i32>(connection)?;
322 ///
323 /// assert_eq!(7, result);
324 ///
325 /// let json5 = r#"
326 /// {
327 /// // A traditional message.
328 /// message: 'hello world',
329 ///
330 /// // A number for some reason.
331 /// n: 42,
332 /// }
333 /// "#;
334 /// let result = diesel::select(json_error_position::<Text, _>(json5))
335 /// .get_result::<i32>(connection)?;
336 ///
337 /// assert_eq!(0, result);
338 ///
339 /// let json5_with_error = r#"
340 /// {
341 /// // A traditional message.
342 /// message: hello world',
343 ///
344 /// // A number for some reason.
345 /// n: 42,
346 /// }
347 /// "#;
348 /// let result = diesel::select(json_error_position::<Text, _>(json5_with_error))
349 /// .get_result::<i32>(connection)?;
350 ///
351 /// assert_eq!(59, result);
352 ///
353 /// let result = diesel::select(json_error_position::<Nullable<Text>, _>(None::<&str>))
354 /// .get_result::<Option<i32>>(connection)?;
355 ///
356 /// assert_eq!(None, result);
357 ///
358 /// let result = diesel::select(json_error_position::<Binary, _>(br#"{"a": "b", "c": 1}"#))
359 /// .get_result::<i32>(connection)?;
360 ///
361 /// assert_eq!(0, result);
362 ///
363 /// let result = diesel::select(json_error_position::<Binary, _>(br#"{"a": b", "c": 1}"#))
364 /// .get_result::<i32>(connection)?;
365 ///
366 /// assert_eq!(7, result);
367 ///
368 /// let result = diesel::select(json_error_position::<Nullable<Binary>, _>(None::<Vec<u8>>))
369 /// .get_result::<Option<i32>>(connection)?;
370 ///
371 /// assert_eq!(None, result);
372 ///
373 /// # Ok(())
374 /// # }
375 /// ```
376 #[cfg(feature = "sqlite")]
377 fn json_error_position<
378 X: TextOrNullableTextOrBinaryOrNullableBinary + MaybeNullableValue<Integer>,
379 >(
380 x: X,
381 ) -> X::Out;
382
383 /// Converts the given json value to pretty-printed, indented text
384 ///
385 /// # Example
386 ///
387 /// ```rust
388 /// # include!("../../doctest_setup.rs");
389 /// #
390 /// # fn main() {
391 /// # #[cfg(feature = "serde_json")]
392 /// # run_test().unwrap();
393 /// # }
394 /// #
395 /// # #[cfg(feature = "serde_json")]
396 /// # fn run_test() -> QueryResult<()> {
397 /// # use diesel::dsl::{sql, json_pretty};
398 /// # use serde_json::{json, Value};
399 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
400 /// # let connection = &mut establish_connection();
401 ///
402 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
403 /// .get_result::<String>(connection)?;
404 ///
405 /// // Querying SQLite version should not fail.
406 /// let version_components: Vec<&str> = version.split('.').collect();
407 /// let major: u32 = version_components[0].parse().unwrap();
408 /// let minor: u32 = version_components[1].parse().unwrap();
409 /// let patch: u32 = version_components[2].parse().unwrap();
410 ///
411 /// if major > 3 || (major == 3 && minor >= 46) {
412 /// /* Valid sqlite version, do nothing */
413 /// } else {
414 /// println!("SQLite version is too old, skipping the test.");
415 /// return Ok(());
416 /// }
417 ///
418 /// let result = diesel::select(json_pretty::<Json, _>(json!([{"f1":1,"f2":null},2,null,3])))
419 /// .get_result::<String>(connection)?;
420 ///
421 /// assert_eq!(r#"[
422 /// {
423 /// "f1": 1,
424 /// "f2": null
425 /// },
426 /// 2,
427 /// null,
428 /// 3
429 /// ]"#, result);
430 ///
431 /// let result = diesel::select(json_pretty::<Json, _>(json!({"a": 1, "b": "cd"})))
432 /// .get_result::<String>(connection)?;
433 ///
434 /// assert_eq!(r#"{
435 /// "a": 1,
436 /// "b": "cd"
437 /// }"#, result);
438 ///
439 /// let result = diesel::select(json_pretty::<Json, _>(json!("abc")))
440 /// .get_result::<String>(connection)?;
441 ///
442 /// assert_eq!(r#""abc""#, result);
443 ///
444 /// let result = diesel::select(json_pretty::<Json, _>(json!(22)))
445 /// .get_result::<String>(connection)?;
446 ///
447 /// assert_eq!(r#"22"#, result);
448 ///
449 /// let result = diesel::select(json_pretty::<Json, _>(json!(false)))
450 /// .get_result::<String>(connection)?;
451 ///
452 /// assert_eq!(r#"false"#, result);
453 ///
454 /// let result = diesel::select(json_pretty::<Json, _>(json!(null)))
455 /// .get_result::<String>(connection)?;
456 ///
457 /// assert_eq!(r#"null"#, result);
458 ///
459 /// let result = diesel::select(json_pretty::<Json, _>(json!({})))
460 /// .get_result::<String>(connection)?;
461 ///
462 /// assert_eq!(r#"{}"#, result);
463 ///
464 /// let result = diesel::select(json_pretty::<Nullable<Json>, _>(None::<Value>))
465 /// .get_result::<Option<String>>(connection)?;
466 ///
467 /// assert!(result.is_none());
468 ///
469 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!([{"f1":1,"f2":null},2,null,3])))
470 /// .get_result::<String>(connection)?;
471 ///
472 /// assert_eq!(r#"[
473 /// {
474 /// "f1": 1,
475 /// "f2": null
476 /// },
477 /// 2,
478 /// null,
479 /// 3
480 /// ]"#, result);
481 ///
482 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!({"a": 1, "b": "cd"})))
483 /// .get_result::<String>(connection)?;
484 ///
485 /// assert_eq!(r#"{
486 /// "a": 1,
487 /// "b": "cd"
488 /// }"#, result);
489 ///
490 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!("abc")))
491 /// .get_result::<String>(connection)?;
492 ///
493 /// assert_eq!(r#""abc""#, result);
494 ///
495 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(22)))
496 /// .get_result::<String>(connection)?;
497 ///
498 /// assert_eq!(r#"22"#, result);
499 ///
500 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(false)))
501 /// .get_result::<String>(connection)?;
502 ///
503 /// assert_eq!(r#"false"#, result);
504 ///
505 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!(null)))
506 /// .get_result::<String>(connection)?;
507 ///
508 /// assert_eq!(r#"null"#, result);
509 ///
510 /// let result = diesel::select(json_pretty::<Jsonb, _>(json!({})))
511 /// .get_result::<String>(connection)?;
512 ///
513 /// assert_eq!(r#"{}"#, result);
514 ///
515 /// let result = diesel::select(json_pretty::<Nullable<Jsonb>, _>(None::<Value>))
516 /// .get_result::<Option<String>>(connection)?;
517 ///
518 /// assert!(result.is_none());
519 /// # Ok(())
520 /// # }
521 /// ```
522 fn json_pretty<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>>(
523 j: J,
524 ) -> J::Out;
525
526 /// Converts the given json value to pretty-printed, indented text
527 ///
528 /// # Example
529 ///
530 /// ```rust
531 /// # include!("../../doctest_setup.rs");
532 /// #
533 /// # fn main() {
534 /// # #[cfg(feature = "serde_json")]
535 /// # run_test().unwrap();
536 /// # }
537 /// #
538 /// # #[cfg(feature = "serde_json")]
539 /// # fn run_test() -> QueryResult<()> {
540 /// # use diesel::dsl::{sql, json_pretty_with_indentation};
541 /// # use serde_json::{json, Value};
542 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
543 /// # let connection = &mut establish_connection();
544 ///
545 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
546 /// .get_result::<String>(connection)?;
547 ///
548 /// // Querying SQLite version should not fail.
549 /// let version_components: Vec<&str> = version.split('.').collect();
550 /// let major: u32 = version_components[0].parse().unwrap();
551 /// let minor: u32 = version_components[1].parse().unwrap();
552 /// let patch: u32 = version_components[2].parse().unwrap();
553 ///
554 /// if major > 3 || (major == 3 && minor >= 46) {
555 /// /* Valid sqlite version, do nothing */
556 /// } else {
557 /// println!("SQLite version is too old, skipping the test.");
558 /// return Ok(());
559 /// }
560 ///
561 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!([{"f1":1,"f2":null},2,null,3]), " "))
562 /// .get_result::<String>(connection)?;
563 ///
564 /// assert_eq!(r#"[
565 /// {
566 /// "f1": 1,
567 /// "f2": null
568 /// },
569 /// 2,
570 /// null,
571 /// 3
572 /// ]"#, result);
573 ///
574 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!([{"f1":1,"f2":null},2,null,3]), None::<&str>))
575 /// .get_result::<String>(connection)?;
576 ///
577 /// assert_eq!(r#"[
578 /// {
579 /// "f1": 1,
580 /// "f2": null
581 /// },
582 /// 2,
583 /// null,
584 /// 3
585 /// ]"#, result);
586 ///
587 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!({"a": 1, "b": "cd"}), " "))
588 /// .get_result::<String>(connection)?;
589 ///
590 /// assert_eq!(r#"{
591 /// "a": 1,
592 /// "b": "cd"
593 /// }"#, result);
594 ///
595 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!("abc"), " "))
596 /// .get_result::<String>(connection)?;
597 ///
598 /// assert_eq!(r#""abc""#, result);
599 ///
600 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(22), " "))
601 /// .get_result::<String>(connection)?;
602 ///
603 /// assert_eq!(r#"22"#, result);
604 ///
605 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(false), None::<&str>))
606 /// .get_result::<String>(connection)?;
607 ///
608 /// assert_eq!(r#"false"#, result);
609 ///
610 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!(null), None::<&str>))
611 /// .get_result::<String>(connection)?;
612 ///
613 /// assert_eq!(r#"null"#, result);
614 ///
615 /// let result = diesel::select(json_pretty_with_indentation::<Json, _, _>(json!({}), " "))
616 /// .get_result::<String>(connection)?;
617 ///
618 /// assert_eq!(r#"{}"#, result);
619 ///
620 /// let result = diesel::select(json_pretty_with_indentation::<Nullable<Json>, _, _>(None::<Value>, None::<&str>))
621 /// .get_result::<Option<String>>(connection)?;
622 ///
623 /// assert!(result.is_none());
624 ///
625 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!([{"f1":1,"f2":null},2,null,3]), " "))
626 /// .get_result::<String>(connection)?;
627 ///
628 /// assert_eq!(r#"[
629 /// {
630 /// "f1": 1,
631 /// "f2": null
632 /// },
633 /// 2,
634 /// null,
635 /// 3
636 /// ]"#, result);
637 ///
638 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!([{"f1":1,"f2":null},2,null,3]), None::<&str>))
639 /// .get_result::<String>(connection)?;
640 ///
641 /// assert_eq!(r#"[
642 /// {
643 /// "f1": 1,
644 /// "f2": null
645 /// },
646 /// 2,
647 /// null,
648 /// 3
649 /// ]"#, result);
650 ///
651 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!({"a": 1, "b": "cd"}), " "))
652 /// .get_result::<String>(connection)?;
653 ///
654 /// assert_eq!(r#"{
655 /// "a": 1,
656 /// "b": "cd"
657 /// }"#, result);
658 ///
659 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!("abc"), " "))
660 /// .get_result::<String>(connection)?;
661 ///
662 /// assert_eq!(r#""abc""#, result);
663 ///
664 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(22), " "))
665 /// .get_result::<String>(connection)?;
666 ///
667 /// assert_eq!(r#"22"#, result);
668 ///
669 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(false), None::<&str>))
670 /// .get_result::<String>(connection)?;
671 ///
672 /// assert_eq!(r#"false"#, result);
673 ///
674 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!(null), None::<&str>))
675 /// .get_result::<String>(connection)?;
676 ///
677 /// assert_eq!(r#"null"#, result);
678 ///
679 /// let result = diesel::select(json_pretty_with_indentation::<Jsonb, _, _>(json!({}), " "))
680 /// .get_result::<String>(connection)?;
681 ///
682 /// assert_eq!(r#"{}"#, result);
683 ///
684 /// let result = diesel::select(json_pretty_with_indentation::<Nullable<Jsonb>, _, _>(None::<Value>, None::<&str>))
685 /// .get_result::<Option<String>>(connection)?;
686 ///
687 /// assert!(result.is_none());
688 ///
689 /// # Ok(())
690 /// # }
691 /// ```
692 #[sql_name = "json_pretty"]
693 fn json_pretty_with_indentation<
694 J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>,
695 >(
696 j: J,
697 indentation: Nullable<Text>,
698 ) -> J::Out;
699
700 /// Returns `true` if the argument is well-formed JSON, or returns `false` if is not well-formed.
701 ///
702 /// # Example
703 ///
704 /// ```rust
705 /// # include!("../../doctest_setup.rs");
706 /// #
707 /// # fn main() {
708 /// # #[cfg(feature = "serde_json")]
709 /// # run_test().unwrap();
710 /// # }
711 /// #
712 /// # #[cfg(feature = "serde_json")]
713 /// # fn run_test() -> QueryResult<()> {
714 /// # use diesel::dsl::{sql, json_valid};
715 /// # use serde_json::{json, Value};
716 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
717 /// # let connection = &mut establish_connection();
718 ///
719 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
720 /// .get_result::<String>(connection)?;
721 ///
722 /// // Querying SQLite version should not fail.
723 /// let version_components: Vec<&str> = version.split('.').collect();
724 /// let major: u32 = version_components[0].parse().unwrap();
725 /// let minor: u32 = version_components[1].parse().unwrap();
726 /// let patch: u32 = version_components[2].parse().unwrap();
727 ///
728 /// if major > 3 || (major == 3 && minor >= 46) {
729 /// /* Valid sqlite version, do nothing */
730 /// } else {
731 /// println!("SQLite version is too old, skipping the test.");
732 /// return Ok(());
733 /// }
734 ///
735 /// let result = diesel::select(json_valid::<Json, _>(json!({"x":35})))
736 /// .get_result::<bool>(connection)?;
737 ///
738 /// assert_eq!(true, result);
739 ///
740 /// let result = diesel::select(json_valid::<Nullable<Json>, _>(None::<serde_json::Value>))
741 /// .get_result::<Option<bool>>(connection)?;
742 ///
743 /// assert_eq!(None, result);
744 ///
745 /// # Ok(())
746 /// # }
747 /// ```
748 #[sql_name = "json_valid"]
749 #[cfg(feature = "sqlite")]
750 fn json_valid<J: JsonOrNullableJson + MaybeNullableValue<Bool>>(j: J) -> J::Out;
751
752 /// The json_type(X) function returns the "type" of the outermost element of X.
753 /// The "type" returned by json_type() is one of the following SQL text values:
754 /// 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
755 ///
756 /// # Example
757 ///
758 /// ```rust
759 /// # include!("../../doctest_setup.rs");
760 /// #
761 /// # fn main() {
762 /// # #[cfg(feature = "serde_json")]
763 /// # run_test().unwrap();
764 /// # }
765 /// #
766 /// # #[cfg(feature = "serde_json")]
767 /// # fn run_test() -> QueryResult<()> {
768 /// # use diesel::dsl::{sql, json_type};
769 /// # use serde_json::{json, Value};
770 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
771 /// # let connection = &mut establish_connection();
772 ///
773 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
774 /// .get_result::<String>(connection)?;
775 ///
776 /// // Querying SQLite version should not fail.
777 /// let version_components: Vec<&str> = version.split('.').collect();
778 /// let major: u32 = version_components[0].parse().unwrap();
779 /// let minor: u32 = version_components[1].parse().unwrap();
780 /// let patch: u32 = version_components[2].parse().unwrap();
781 ///
782 /// if major > 3 || (major == 3 && minor >= 38) {
783 /// /* Valid sqlite version, do nothing */
784 /// } else {
785 /// println!("SQLite version is too old, skipping the test.");
786 /// return Ok(());
787 /// }
788 ///
789 /// let result = diesel::select(json_type::<Json, _>(json!({"a": [2, 3.5, true, false, null, "x"]})))
790 /// .get_result::<String>(connection)?;
791 ///
792 /// assert_eq!("object", result);
793 ///
794 /// let result = diesel::select(json_type::<Jsonb, _>(json!({"a": [2, 3.5, true, false, null, "x"]})))
795 /// .get_result::<String>(connection)?;
796 ///
797 /// assert_eq!("object", result);
798 ///
799 /// let result = diesel::select(json_type::<Nullable<Json>, _>(None::<serde_json::Value>))
800 /// .get_result::<Option<String>>(connection)?;
801 ///
802 /// assert_eq!(None, result);
803 ///
804 /// # Ok(())
805 /// # }
806 /// ```
807 #[sql_name = "json_type"]
808 #[cfg(feature = "sqlite")]
809 fn json_type<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + MaybeNullableValue<Text>>(
810 j: J,
811 ) -> J::Out;
812
813 /// The json_type(X,P) function returns the "type" of the element in X that is selected by path P.
814 /// If the path P in json_type(X,P) selects an element that does not exist in X, then this function returns NULL.
815 ///
816 /// # Example
817 ///
818 /// ```rust
819 /// # include!("../../doctest_setup.rs");
820 /// #
821 /// # fn main() {
822 /// # #[cfg(feature = "serde_json")]
823 /// # run_test().unwrap();
824 /// # }
825 /// #
826 /// # #[cfg(feature = "serde_json")]
827 /// # fn run_test() -> QueryResult<()> {
828 /// # use diesel::dsl::{sql, json_type_with_path};
829 /// # use serde_json::{json, Value};
830 /// # use diesel::sql_types::{Text, Json, Jsonb, Nullable};
831 /// # let connection = &mut establish_connection();
832 ///
833 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
834 /// .get_result::<String>(connection)?;
835 ///
836 /// // Querying SQLite version should not fail.
837 /// let version_components: Vec<&str> = version.split('.').collect();
838 /// let major: u32 = version_components[0].parse().unwrap();
839 /// let minor: u32 = version_components[1].parse().unwrap();
840 /// let patch: u32 = version_components[2].parse().unwrap();
841 ///
842 /// if major > 3 || (major == 3 && minor >= 38) {
843 /// /* Valid sqlite version, do nothing */
844 /// } else {
845 /// println!("SQLite version is too old, skipping the test.");
846 /// return Ok(());
847 /// }
848 ///
849 /// let json_value = json!({"a": [2, 3.5, true, false, null, "x"]});
850 ///
851 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a"))
852 /// .get_result::<Option<String>>(connection)?;
853 ///
854 /// assert_eq!(Some("array".to_string()), result);
855 ///
856 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[0]"))
857 /// .get_result::<Option<String>>(connection)?;
858 ///
859 /// assert_eq!(Some("integer".to_string()), result);
860 ///
861 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[1]"))
862 /// .get_result::<Option<String>>(connection)?;
863 ///
864 /// assert_eq!(Some("real".to_string()), result);
865 ///
866 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[2]"))
867 /// .get_result::<Option<String>>(connection)?;
868 ///
869 /// assert_eq!(Some("true".to_string()), result);
870 ///
871 /// let result = diesel::select(json_type_with_path::<Json, _, _>(json_value.clone(), "$.a[6]"))
872 /// .get_result::<Option<String>>(connection)?;
873 ///
874 /// assert_eq!(None, result);
875 ///
876 /// let result = diesel::select(json_type_with_path::<Jsonb, _, _>(json_value.clone(), "$.a"))
877 /// .get_result::<Option<String>>(connection)?;
878 ///
879 /// assert_eq!(Some("array".to_string()), result);
880 ///
881 /// let result = diesel::select(json_type_with_path::<Nullable<Json>, _, _>(None::<serde_json::Value>, "$.a"))
882 /// .get_result::<Option<String>>(connection)?;
883 ///
884 /// assert_eq!(None, result);
885 ///
886 /// # Ok(())
887 /// # }
888 /// ```
889 #[sql_name = "json_type"]
890 #[cfg(feature = "sqlite")]
891 fn json_type_with_path<J: JsonOrNullableJsonOrJsonbOrNullableJsonb + SingleValue>(
892 j: J,
893 path: Text,
894 ) -> Nullable<Text>;
895
896 /// The json_quote(X) function converts the SQL value X (a number or a string) into its corresponding JSON
897 /// representation. If X is a JSON value returned by another JSON function, then this function is a no-op.
898 ///
899 /// # Example
900 ///
901 /// ```rust
902 /// # include!("../../doctest_setup.rs");
903 /// #
904 /// # fn main() {
905 /// # #[cfg(feature = "serde_json")]
906 /// # run_test().unwrap();
907 /// # }
908 /// #
909 /// # #[cfg(feature = "serde_json")]
910 /// # fn run_test() -> QueryResult<()> {
911 /// # use diesel::dsl::{sql, json_quote};
912 /// # use serde_json::{json, Value};
913 /// # use diesel::sql_types::{Text, Json, Integer, Float, Double, Nullable};
914 /// # let connection = &mut establish_connection();
915 ///
916 /// let version = diesel::select(sql::<Text>("sqlite_version();"))
917 /// .get_result::<String>(connection)?;
918 ///
919 /// // Querying SQLite version should not fail.
920 /// let version_components: Vec<&str> = version.split('.').collect();
921 /// let major: u32 = version_components[0].parse().unwrap();
922 /// let minor: u32 = version_components[1].parse().unwrap();
923 /// let patch: u32 = version_components[2].parse().unwrap();
924 ///
925 /// if major > 3 || (major == 3 && minor >= 38) {
926 /// /* Valid sqlite version, do nothing */
927 /// } else {
928 /// println!("SQLite version is too old, skipping the test.");
929 /// return Ok(());
930 /// }
931 /// let result = diesel::select(json_quote::<Integer, _>(42))
932 /// .get_result::<Value>(connection)?;
933 /// assert_eq!(json!(42), result);
934 ///
935 /// let result = diesel::select(json_quote::<Text, _>("verdant"))
936 /// .get_result::<Value>(connection)?;
937 /// assert_eq!(json!("verdant"), result);
938 ///
939 /// let result = diesel::select(json_quote::<Text, _>("[1]"))
940 /// .get_result::<Value>(connection)?;
941 /// assert_eq!(json!("[1]"), result);
942 ///
943 /// let result = diesel::select(json_quote::<Nullable<Text>, _>(None::<&str>))
944 /// .get_result::<Value>(connection)?;
945 /// assert_eq!(json!(null), result);
946 ///
947 /// let result = diesel::select(json_quote::<Double, _>(3.14159))
948 /// .get_result::<Value>(connection)?;
949 /// assert_eq!(json!(3.14159), result);
950 ///
951 /// let result = diesel::select(json_quote::<Json, _>(json!([1])))
952 /// .get_result::<Value>(connection)?;
953 // assert_eq!(json!([1]), result);
954 ///
955 ///
956 /// # Ok(())
957 /// # }
958 /// ```
959 #[sql_name = "json_quote"]
960 #[cfg(feature = "sqlite")]
961 fn json_quote<J: SqlType + SingleValue>(j: J) -> Json;
962
963 /// The `json_group_array(X)` function is an aggregate SQL function that returns a JSON array comprised of
964 /// all X values in the aggregation.
965 ///
966 /// # Examples
967 ///
968 /// ```rust
969 /// # include!("../../doctest_setup.rs");
970 /// #
971 /// # fn main() {
972 /// # #[cfg(feature = "serde_json")]
973 /// # run_test().unwrap();
974 /// # }
975 /// #
976 /// # #[cfg(feature = "serde_json")]
977 /// # fn run_test() -> QueryResult<()> {
978 /// # use diesel::dsl::*;
979 /// # use schema::animals::dsl::*;
980 /// # use serde_json::json;
981 /// #
982 /// # let connection = &mut establish_connection();
983 /// #
984 /// let result = animals.select(json_group_array(species)).get_result::<serde_json::Value>(connection)?;
985 /// assert_eq!(result, json!(["dog", "spider"]));
986 ///
987 /// let result = animals.select(json_group_array(legs)).get_result::<serde_json::Value>(connection)?;
988 /// assert_eq!(result, json!([4, 8]));
989 ///
990 /// let result = animals.select(json_group_array(name)).get_result::<serde_json::Value>(connection)?;
991 /// assert_eq!(result, json!(["Jack", null]));
992 ///
993 /// # Ok(())
994 /// # }
995 /// ```
996 ///
997 /// # See also
998 /// - [`jsonb_group_array`] will return data in JSONB format instead of JSON.
999 /// - [`json_group_object`] will return JSON object instead of array.
1000 #[cfg(feature = "sqlite")]
1001 #[aggregate]
1002 fn json_group_array<E: SqlType + SingleValue>(elements: E) -> Json;
1003
1004 /// The `jsonb_group_array(X)` function is an aggregate SQL function that returns a JSONB array comprised of
1005 /// all X values in the aggregation.
1006 ///
1007 /// # Examples
1008 ///
1009 /// ```rust
1010 /// # include!("../../doctest_setup.rs");
1011 /// #
1012 /// # fn main() {
1013 /// # #[cfg(feature = "serde_json")]
1014 /// # run_test().unwrap();
1015 /// # }
1016 /// #
1017 /// # #[cfg(feature = "serde_json")]
1018 /// # fn run_test() -> QueryResult<()> {
1019 /// # use diesel::dsl::*;
1020 /// # use schema::animals::dsl::*;
1021 /// # use serde_json::json;
1022 /// #
1023 /// # let connection = &mut establish_connection();
1024 /// #
1025 /// let result = animals.select(json_group_array(species)).get_result::<serde_json::Value>(connection)?;
1026 /// assert_eq!(result, json!(["dog", "spider"]));
1027 ///
1028 /// let result = animals.select(json_group_array(legs)).get_result::<serde_json::Value>(connection)?;
1029 /// assert_eq!(result, json!([4, 8]));
1030 ///
1031 /// let result = animals.select(json_group_array(name)).get_result::<serde_json::Value>(connection)?;
1032 /// assert_eq!(result, json!(["Jack", null]));
1033 ///
1034 /// # Ok(())
1035 /// # }
1036 /// ```
1037 ///
1038 /// # See also
1039 /// - [`json_group_array`] will return data in JSON format instead of JSONB.
1040 /// - [`jsonb_group_object`] will return JSONB object instead of array.
1041 #[cfg(feature = "sqlite")]
1042 #[aggregate]
1043 fn jsonb_group_array<E: SqlType + SingleValue>(elements: E) -> Jsonb;
1044
1045 /// The json_group_object(NAME,VALUE) function returns a JSON object comprised of all NAME/VALUE pairs in
1046 /// the aggregation.
1047 ///
1048 /// A potential edge case in this function arises when `names` contains duplicate elements.
1049 /// In such case, the result will include all duplicates (e.g., `{"key": 1, "key": 2, "key": 3}`).
1050 /// Note that any duplicate entries in the resulting JSON will be removed during deserialization.
1051 ///
1052 /// # Examples
1053 ///
1054 /// ```rust
1055 /// # include!("../../doctest_setup.rs");
1056 /// #
1057 /// # fn main() {
1058 /// # #[cfg(feature = "serde_json")]
1059 /// # run_test().unwrap();
1060 /// # }
1061 /// #
1062 /// # #[cfg(feature = "serde_json")]
1063 /// # fn run_test() -> QueryResult<()> {
1064 /// # use diesel::dsl::*;
1065 /// # use diesel::sql_types::Text;
1066 /// # use serde_json::json;
1067 /// # use schema::animals::dsl::*;
1068 /// #
1069 /// # let connection = &mut establish_connection();
1070 /// #
1071 /// # let version = diesel::select(sql::<Text>("sqlite_version();"))
1072 /// # .get_result::<String>(connection)?;
1073 /// #
1074 /// # let version_components: Vec<&str> = version.split('.').collect();
1075 /// # let major: u32 = version_components[0].parse().unwrap();
1076 /// # let minor: u32 = version_components[1].parse().unwrap();
1077 /// #
1078 /// # if major < 3 || minor < 38 {
1079 /// # println!("SQLite version is too old, skipping the test.");
1080 /// # return Ok(());
1081 /// # }
1082 /// #
1083 /// let result = animals.select(json_group_object(species, name)).get_result::<serde_json::Value>(connection)?;
1084 /// assert_eq!(json!({"dog":"Jack","spider":null}), result);
1085 /// #
1086 /// # Ok(())
1087 /// # }
1088 /// ```
1089 ///
1090 /// # See also
1091 /// - [`jsonb_group_object`] will return data in JSONB format instead of JSON.
1092 /// - [`json_group_array`] will return JSON array instead of object.
1093 #[cfg(feature = "sqlite")]
1094 #[aggregate]
1095 fn json_group_object<
1096 N: SqlType<IsNull = is_nullable::NotNull> + SingleValue,
1097 V: SqlType + SingleValue,
1098 >(
1099 names: N,
1100 values: V,
1101 ) -> Json;
1102
1103 /// The jsonb_group_object(NAME,VALUE) function returns a JSONB object comprised of all NAME/VALUE pairs in
1104 /// the aggregation.
1105 ///
1106 /// A potential edge case in this function arises when `names` contains duplicate elements.
1107 /// In such case, the result will include all duplicates (e.g., `{"key": 1, "key": 2, "key": 3}`).
1108 /// Note that any duplicate entries in the resulting JSONB will be removed during deserialization.
1109 ///
1110 /// # Examples
1111 ///
1112 /// ```rust
1113 /// # include!("../../doctest_setup.rs");
1114 /// #
1115 /// # fn main() {
1116 /// # #[cfg(feature = "serde_json")]
1117 /// # run_test().unwrap();
1118 /// # }
1119 /// #
1120 /// # #[cfg(feature = "serde_json")]
1121 /// # fn run_test() -> QueryResult<()> {
1122 /// # use diesel::dsl::*;
1123 /// # use diesel::sql_types::Text;
1124 /// # use serde_json::json;
1125 /// # use schema::animals::dsl::*;
1126 /// #
1127 /// # let connection = &mut establish_connection();
1128 /// #
1129 /// # let version = diesel::select(sql::<Text>("sqlite_version();"))
1130 /// # .get_result::<String>(connection)?;
1131 /// #
1132 /// # let version_components: Vec<&str> = version.split('.').collect();
1133 /// # let major: u32 = version_components[0].parse().unwrap();
1134 /// # let minor: u32 = version_components[1].parse().unwrap();
1135 /// #
1136 /// # if major < 3 || minor < 38 {
1137 /// # println!("SQLite version is too old, skipping the test.");
1138 /// # return Ok(());
1139 /// # }
1140 /// #
1141 /// let result = animals.select(jsonb_group_object(species, name)).get_result::<serde_json::Value>(connection)?;
1142 /// assert_eq!(json!({"dog":"Jack","spider":null}), result);
1143 /// #
1144 /// # Ok(())
1145 /// # }
1146 /// ```
1147 ///
1148 /// # See also
1149 /// - [`json_group_object`] will return data in JSON format instead of JSONB.
1150 /// - [`jsonb_group_array`] will return JSONB array instead of object.
1151 #[cfg(feature = "sqlite")]
1152 #[aggregate]
1153 fn jsonb_group_object<
1154 N: SqlType<IsNull = is_nullable::NotNull> + SingleValue,
1155 V: SqlType + SingleValue,
1156 >(
1157 names: N,
1158 values: V,
1159 ) -> Jsonb;
1160}