diesel/sqlite/types/
json.rs

Help
1//! Support for JSON and JSONB values under SQLite.
2
3use crate::deserialize::{self, FromSql};
4use crate::serialize::{self, IsNull, Output, ToSql};
5use crate::sql_types;
6use crate::sqlite::{Sqlite, SqliteValue};
7
8#[cfg(all(feature = "sqlite", feature = "serde_json"))]
9impl FromSql<sql_types::Json, Sqlite> for serde_json::Value {
10    fn from_sql(mut value: SqliteValue<'_, '_, '_>) -> deserialize::Result<Self> {
11        serde_json::from_str(value.read_text()).map_err(|_| "Invalid Json".into())
12    }
13}
14
15#[cfg(all(feature = "sqlite", feature = "serde_json"))]
16impl ToSql<sql_types::Json, Sqlite> for serde_json::Value {
17    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
18        out.set_value(serde_json::to_string(self)?);
19        Ok(IsNull::No)
20    }
21}
22
23#[cfg(all(feature = "sqlite", feature = "serde_json"))]
24impl FromSql<sql_types::Jsonb, Sqlite> for serde_json::Value {
25    fn from_sql(mut value: SqliteValue<'_, '_, '_>) -> deserialize::Result<Self> {
26        use self::jsonb::*;
27
28        let bytes = value.read_blob();
29
30        if bytes.is_empty() {
31            return Err("Empty blob cannot be decoded as JSONB".into());
32        }
33
34        // Read the JSONB value from the byte stream
35        let (jsonb, _size) = read_jsonb_value(bytes)?;
36
37        Ok(jsonb)
38    }
39}
40
41#[cfg(all(feature = "sqlite", feature = "serde_json"))]
42impl ToSql<sql_types::Jsonb, Sqlite> for serde_json::Value {
43    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
44        use self::jsonb::*;
45
46        // Create a buffer to hold the binary JSONB encoding
47        let mut buffer = Vec::new();
48
49        // Write the JSON value into the buffer in JSONB format
50        write_jsonb_value(self, &mut buffer)?;
51
52        // Set the serialized binary data to the output
53        out.set_value(buffer);
54
55        Ok(IsNull::No)
56    }
57}
58
59#[cfg(all(feature = "sqlite", feature = "serde_json"))]
60mod jsonb {
61    extern crate serde_json;
62
63    use super::*;
64
65    pub(super) const JSONB_NULL: u8 = 0x00;
66    pub(super) const JSONB_TRUE: u8 = 0x01;
67    pub(super) const JSONB_FALSE: u8 = 0x02;
68    pub(super) const JSONB_INT: u8 = 0x03;
69    pub(super) const JSONB_INT5: u8 = 0x04;
70    pub(super) const JSONB_FLOAT: u8 = 0x05;
71    pub(super) const JSONB_FLOAT5: u8 = 0x06;
72    pub(super) const JSONB_TEXT: u8 = 0x07;
73    pub(super) const JSONB_TEXTJ: u8 = 0x08;
74    pub(super) const JSONB_TEXT5: u8 = 0x09;
75    pub(super) const JSONB_TEXTRAW: u8 = 0x0A;
76    pub(super) const JSONB_ARRAY: u8 = 0x0B;
77    pub(super) const JSONB_OBJECT: u8 = 0x0C;
78
79    // Helper function to read a JSONB value from the byte stream
80    pub(super) fn read_jsonb_value(
81        bytes: &[u8],
82    ) -> deserialize::Result<(serde_json::Value, usize)> {
83        if bytes.is_empty() {
84            return Err("Empty JSONB data".into());
85        }
86
87        // The first byte contains both the element type and potentially the payload size
88        let first_byte = bytes[0];
89        let element_type = first_byte & 0x0F;
90        let size_hint = (first_byte & 0xF0) >> 4;
91
92        let (payload_size, header_size) = match size_hint {
93            0x00..=0x0B => (size_hint as usize, 1), // Payload size is directly in the upper nibble
94            0x0C => {
95                if bytes.len() < 2 {
96                    return Err("Invalid JSONB data: insufficient bytes for payload size".into());
97                }
98                (bytes[1] as usize, 2) // 1 additional byte for payload size
99            }
100            0x0D => {
101                if bytes.len() < 3 {
102                    return Err("Invalid JSONB data: insufficient bytes for payload size".into());
103                }
104                (u16::from_be_bytes([bytes[1], bytes[2]]) as usize, 3) // 2 additional bytes
105            }
106            0x0E => {
107                if bytes.len() < 5 {
108                    return Err("Invalid JSONB data: insufficient bytes for payload size".into());
109                }
110                (
111                    u32::from_be_bytes([bytes[1], bytes[2], bytes[3], bytes[4]]) as usize,
112                    5,
113                ) // 4 additional bytes
114            }
115            0x0F => {
116                if bytes.len() < 9 {
117                    return Err("Invalid JSONB data: insufficient bytes for payload size".into());
118                }
119                (
120                    usize::try_from(u64::from_be_bytes([
121                        bytes[1], bytes[2], bytes[3], bytes[4], bytes[5], bytes[6], bytes[7],
122                        bytes[8],
123                    ]))
124                    .map_err(Box::new)?,
125                    9,
126                ) // 8 additional bytes
127            }
128            _ => return Err("Invalid payload size hint".into()),
129        };
130
131        let total_size = header_size + payload_size;
132        if bytes.len() < total_size {
133            return Err(format!(
134                "Invalid JSONB data: insufficient bytes for value of type {}, expected {} bytes, got {}",
135                element_type,
136                total_size,
137                bytes.len()
138            )
139            .into());
140        }
141
142        let payload_bytes = &bytes[header_size..total_size];
143
144        let value = match element_type {
145            JSONB_NULL => Ok(serde_json::Value::Null),
146            JSONB_TRUE => Ok(serde_json::Value::Bool(true)),
147            JSONB_FALSE => Ok(serde_json::Value::Bool(false)),
148            JSONB_INT => read_jsonb_int(payload_bytes, payload_size),
149            JSONB_INT5 => Err("INT5 is not supported".into()),
150            JSONB_FLOAT => read_jsonb_float(payload_bytes, payload_size),
151            JSONB_FLOAT5 => Err("FLOAT5 is not supported".into()),
152            JSONB_TEXT => read_jsonb_text(payload_bytes, payload_size),
153            JSONB_TEXTJ => read_jsonb_textj(payload_bytes, payload_size),
154            JSONB_TEXTRAW => Err("TEXTRAW is not supported".into()),
155            JSONB_TEXT5 => Err("TEXT5 is not supported".into()),
156            JSONB_ARRAY => read_jsonb_array(payload_bytes, payload_size),
157            JSONB_OBJECT => read_jsonb_object(payload_bytes, payload_size),
158            _ => Err(format!("Unsupported or reserved JSONB type: {}", element_type).into()),
159        }?;
160
161        Ok((value, total_size))
162    }
163
164    // Read a JSON integer in canonical format (INT)
165    pub(super) fn read_jsonb_int(
166        bytes: &[u8],
167        payload_size: usize,
168    ) -> deserialize::Result<serde_json::Value> {
169        // Ensure the bytes are at least as large as the payload size
170        if bytes.len() < payload_size {
171            return Err(format!(
172                "Expected payload of size {}, but got {}",
173                payload_size,
174                bytes.len()
175            )
176            .into());
177        }
178
179        // Read only the number of bytes specified by the payload size
180        let int_str = std::str::from_utf8(bytes).map_err(|_| "Invalid ASCII in JSONB integer")?;
181        let int_value = serde_json::from_str(int_str)
182            .map_err(|_| "Failed to parse JSONB")
183            .and_then(|v: serde_json::Value| {
184                v.is_i64()
185                    .then_some(v)
186                    .ok_or("Failed to parse JSONB integer")
187            })?;
188
189        Ok(int_value)
190    }
191
192    // Read a JSON float in canonical format (FLOAT)
193    pub(super) fn read_jsonb_float(
194        bytes: &[u8],
195        payload_size: usize,
196    ) -> deserialize::Result<serde_json::Value> {
197        if bytes.len() < payload_size {
198            return Err(format!(
199                "Expected payload of size {}, but got {}",
200                payload_size,
201                bytes.len()
202            )
203            .into());
204        }
205
206        let float_str = std::str::from_utf8(bytes).map_err(|_| "Invalid UTF-8 in JSONB float")?;
207        let float_value = serde_json::from_str(float_str)
208            .map_err(|_| "Failed to parse JSONB")
209            .and_then(|v: serde_json::Value| {
210                v.is_f64()
211                    .then_some(v)
212                    .ok_or("Failed to parse JSONB number")
213            })?;
214
215        Ok(float_value)
216    }
217
218    // Read a JSON string
219    pub(super) fn read_jsonb_text(
220        bytes: &[u8],
221        payload_size: usize,
222    ) -> deserialize::Result<serde_json::Value> {
223        if bytes.len() < payload_size {
224            return Err(format!(
225                "Expected payload of size {}, but got {}",
226                payload_size,
227                bytes.len()
228            )
229            .into());
230        }
231
232        let text = std::str::from_utf8(bytes).map_err(|_| "Invalid UTF-8 in JSONB string")?;
233        Ok(serde_json::Value::String(text.to_string()))
234    }
235
236    pub(super) fn read_jsonb_textj(
237        bytes: &[u8],
238        payload_size: usize,
239    ) -> deserialize::Result<serde_json::Value> {
240        if bytes.len() < payload_size {
241            return Err(format!(
242                "Expected payload of size {}, but got {}",
243                payload_size,
244                bytes.len()
245            )
246            .into());
247        }
248
249        let text = std::str::from_utf8(bytes).map_err(|_| "Invalid UTF-8 in JSONB string")?;
250
251        // Unescape JSON escape sequences (e.g., "\n", "\u0020")
252        let unescaped_text = serde_json::from_str(&format!("\"{}\"", text))
253            .map_err(|_| "Failed to parse JSON-escaped text in TEXTJ")?;
254
255        Ok(unescaped_text)
256    }
257
258    // Read a JSON array
259    pub(super) fn read_jsonb_array(
260        bytes: &[u8],
261        payload_size: usize,
262    ) -> deserialize::Result<serde_json::Value> {
263        let mut elements = Vec::new();
264        let mut total_read = 0;
265
266        while total_read < payload_size {
267            let (element, consumed) = read_jsonb_value(&bytes[total_read..payload_size])?;
268
269            elements.push(element);
270            total_read += consumed;
271        }
272
273        if total_read != payload_size {
274            return Err("Array payload size mismatch".into());
275        }
276
277        Ok(serde_json::Value::Array(elements))
278    }
279
280    pub(super) fn read_jsonb_object(
281        bytes: &[u8],
282        payload_size: usize,
283    ) -> deserialize::Result<serde_json::Value> {
284        let mut object = serde_json::Map::new();
285        let mut total_read = 0;
286
287        while total_read < payload_size {
288            // Read the key (must be a valid JSONB text type)
289            let (key_value, key_consumed) = read_jsonb_value(&bytes[total_read..])?;
290            let key_str = key_value
291                .as_str()
292                .ok_or("Invalid object key in JSONB, must be a string")?
293                .to_string();
294            total_read += key_consumed;
295
296            // Read the value associated with the key
297            let (value, value_consumed) = read_jsonb_value(&bytes[total_read..])?;
298            object.insert(key_str, value);
299            total_read += value_consumed;
300        }
301
302        // Ensure the total bytes read match the payload size
303        if total_read != payload_size {
304            return Err("Object payload size mismatch".into());
305        }
306
307        Ok(serde_json::Value::Object(object))
308    }
309
310    // Helper function to create the correct JsonbHeader based on the payload size
311    pub(super) fn create_jsonb_header(
312        element_type: u8,
313        payload_size: usize,
314    ) -> Result<Vec<u8>, String> {
315        // Check if payload size exceeds the maximum allowed size
316        if payload_size > 2_147_483_647 {
317            return Err("Payload size exceeds the maximum allowed size of 2GB".into());
318        }
319
320        let header = if payload_size <= 0x0B {
321            // Small payloads, 0 additional byte for size
322            vec![((u8::try_from(payload_size).map_err(|e| e.to_string())?) << 4) | element_type]
323        } else if payload_size <= 0xFF {
324            // Medium payloads, 1 additional byte for size
325            vec![
326                (0x0C << 4) | element_type,
327                u8::try_from(payload_size).map_err(|e| e.to_string())?,
328            ]
329        } else if payload_size <= 0xFFFF {
330            let mut header = Vec::with_capacity(3);
331
332            // Larger payloads, 2 additional bytes for size
333            header.push((0x0D << 4) | element_type);
334            header.extend_from_slice(
335                &(u16::try_from(payload_size).map_err(|e| e.to_string())?).to_be_bytes(),
336            );
337
338            header
339        } else {
340            let mut header = Vec::with_capacity(5);
341
342            // Very large payloads, 4 additional bytes for size (up to 2 GiB)
343            header.push((0x0E << 4) | element_type);
344            header.extend_from_slice(
345                &(u32::try_from(payload_size).map_err(|e| e.to_string())?).to_be_bytes(),
346            );
347
348            header
349        };
350
351        Ok(header)
352    }
353
354    pub(super) fn write_jsonb_header(
355        buffer: &mut Vec<u8>,
356        element_type: u8,
357        payload_size: usize,
358    ) -> serialize::Result {
359        // Create the header and append it to the buffer
360        let header = create_jsonb_header(element_type, payload_size)?;
361        buffer.extend(header);
362        Ok(IsNull::No)
363    }
364
365    // Helper function to write a JSON value into a JSONB binary format
366    pub(super) fn write_jsonb_value(
367        value: &serde_json::Value,
368        buffer: &mut Vec<u8>,
369    ) -> serialize::Result {
370        if value.is_null() {
371            write_jsonb_null(buffer)
372        } else if value.is_boolean() {
373            write_jsonb_bool(value.as_bool().ok_or("Failed to read JSONB value")?, buffer)
374        } else if value.is_number() {
375            write_jsonb_number(value, buffer)
376        } else if value.is_string() {
377            write_jsonb_string(value.as_str().ok_or("Failed to read JSONB value")?, buffer)
378        } else if value.is_array() {
379            write_jsonb_array(
380                value.as_array().ok_or("Failed to read JSONB value")?,
381                buffer,
382            )
383        } else if value.is_object() {
384            write_jsonb_object(
385                value.as_object().ok_or("Failed to read JSONB value")?,
386                buffer,
387            )
388        } else {
389            Err("Unsupported JSONB value type".into())
390        }
391    }
392
393    // Write a JSON null
394    pub(super) fn write_jsonb_null(buffer: &mut Vec<u8>) -> serialize::Result {
395        write_jsonb_header(buffer, JSONB_NULL, 0x0)?;
396        Ok(IsNull::No)
397    }
398
399    // Write a JSON boolean
400    pub(super) fn write_jsonb_bool(b: bool, buffer: &mut Vec<u8>) -> serialize::Result {
401        // Use the constants for true and false
402        write_jsonb_header(buffer, if b { JSONB_TRUE } else { JSONB_FALSE }, 0x0)?;
403        Ok(IsNull::No)
404    }
405
406    // Write a JSON number (integers and floats)
407    pub(super) fn write_jsonb_number(
408        n: &serde_json::Value,
409        buffer: &mut Vec<u8>,
410    ) -> serialize::Result {
411        if let Some(i) = n.as_i64() {
412            // Write an integer (INT type)
413            write_jsonb_int(i, buffer)
414        } else if let Some(f) = n.as_f64() {
415            // Write a float (FLOAT type)
416            write_jsonb_float(f, buffer)
417        } else {
418            Err("Invalid JSONB number type".into())
419        }
420    }
421
422    // Write an integer in JSONB format
423    pub(super) fn write_jsonb_int(i: i64, buffer: &mut Vec<u8>) -> serialize::Result {
424        let int_str = i.to_string();
425
426        write_jsonb_header(buffer, JSONB_INT, int_str.len())?;
427
428        // Write the ASCII text representation of the integer as the payload
429        buffer.extend_from_slice(int_str.as_bytes());
430
431        Ok(IsNull::No)
432    }
433
434    // Write a floating-point number in JSONB format
435    pub(super) fn write_jsonb_float(f: f64, buffer: &mut Vec<u8>) -> serialize::Result {
436        let float_str = f.to_string();
437
438        write_jsonb_header(buffer, JSONB_FLOAT, float_str.len())?;
439
440        // Write the ASCII text representation of the float as the payload
441        buffer.extend_from_slice(float_str.as_bytes());
442
443        Ok(IsNull::No)
444    }
445
446    pub(super) fn write_jsonb_string(s: &str, buffer: &mut Vec<u8>) -> serialize::Result {
447        if s.chars().any(|c| c.is_control()) {
448            // If the string contains control characters, treat it as TEXTJ (escaped JSON)
449            write_jsonb_textj(s, buffer)
450        } else {
451            write_jsonb_header(buffer, JSONB_TEXT, s.len())?;
452            // Write the UTF-8 text of the string as the payload (no delimiters)
453            buffer.extend_from_slice(s.as_bytes());
454            Ok(IsNull::No)
455        }
456    }
457
458    pub(super) fn write_jsonb_textj(s: &str, buffer: &mut Vec<u8>) -> serialize::Result {
459        // Escaping the string for JSON (e.g., \n, \uXXXX)
460        let escaped_string = serde_json::to_string(&String::from(s))
461            .map_err(|_| "Failed to serialize string for TEXTJ")?;
462
463        // Remove the surrounding quotes from serde_json::to_string result
464        let escaped_string = &escaped_string[1..escaped_string.len() - 1];
465
466        // Write the header (JSONB_TEXTJ) and the length of the escaped string
467        write_jsonb_header(buffer, JSONB_TEXTJ, escaped_string.len())?;
468
469        // Write the escaped string as the payload
470        buffer.extend_from_slice(escaped_string.as_bytes());
471
472        Ok(IsNull::No)
473    }
474
475    // Write a JSON array
476    pub(super) fn write_jsonb_array(
477        arr: &[serde_json::Value],
478        buffer: &mut Vec<u8>,
479    ) -> serialize::Result {
480        let mut tmp_buffer = Vec::new();
481
482        // Recursively write each element of the array
483        for element in arr {
484            write_jsonb_value(element, &mut tmp_buffer)?;
485        }
486
487        write_jsonb_header(buffer, JSONB_ARRAY, tmp_buffer.len())?;
488
489        buffer.extend_from_slice(&tmp_buffer);
490
491        Ok(IsNull::No)
492    }
493
494    // Write a JSON object
495    pub(super) fn write_jsonb_object(
496        obj: &serde_json::Map<String, serde_json::Value>,
497        buffer: &mut Vec<u8>,
498    ) -> serialize::Result {
499        let mut tmp_buffer = Vec::new();
500
501        // Recursively write each key-value pair of the object
502        for (key, value) in obj {
503            // Write the key (which must be a string)
504            write_jsonb_string(key, &mut tmp_buffer)?;
505
506            // Write the value
507            write_jsonb_value(value, &mut tmp_buffer)?;
508        }
509
510        write_jsonb_header(buffer, JSONB_OBJECT, tmp_buffer.len())?;
511
512        buffer.extend_from_slice(&tmp_buffer);
513
514        Ok(IsNull::No)
515    }
516}
517
518#[cfg(test)]
519#[cfg(all(feature = "sqlite", feature = "serde_json"))]
520mod tests {
521    use super::jsonb::*;
522    use super::*;
523    use crate::query_dsl::RunQueryDsl;
524    use crate::test_helpers::connection;
525    use crate::ExpressionMethods;
526    use crate::{dsl::sql, IntoSql};
527    use serde_json::{json, Value};
528    use sql_types::{Json, Jsonb};
529
530    #[diesel_test_helper::test]
531    fn json_to_sql() {
532        let conn = &mut connection();
533        let res = diesel::select(json!(true).into_sql::<Json>().eq(&sql("json('true')")))
534            .get_result::<bool>(conn)
535            .unwrap();
536        assert!(res);
537    }
538
539    #[diesel_test_helper::test]
540    fn test_read_jsonb_null() {
541        let data = vec![JSONB_NULL];
542        let result = read_jsonb_value(&data).unwrap().0;
543        assert_eq!(result, Value::Null);
544    }
545
546    #[diesel_test_helper::test]
547    fn test_read_jsonb_true() {
548        let data = vec![JSONB_TRUE];
549        let result = read_jsonb_value(&data).unwrap().0;
550        assert_eq!(result, Value::Bool(true));
551    }
552
553    #[diesel_test_helper::test]
554    fn test_read_jsonb_false() {
555        let data = vec![JSONB_FALSE];
556        let result = read_jsonb_value(&data).unwrap().0;
557        assert_eq!(result, Value::Bool(false));
558    }
559
560    #[diesel_test_helper::test]
561    fn test_read_jsonb_int() {
562        // JSONB_INT with payload "1"
563        let mut data = Vec::new();
564        data.extend(create_jsonb_header(JSONB_INT, 0x01).unwrap());
565        data.push(b'1'); // Add the payload (integer "1")
566
567        let result = read_jsonb_value(&data).unwrap().0;
568        assert_eq!(result, json!(1));
569    }
570
571    #[diesel_test_helper::test]
572    fn test_read_jsonb_float() {
573        // JSONB_FLOAT with payload "1.5"
574        let mut data = Vec::new();
575        data.extend(create_jsonb_header(JSONB_FLOAT, 0x03).unwrap());
576        data.extend_from_slice(b"1.5"); // Add the payload (float "1.5")
577
578        let result = read_jsonb_value(&data).unwrap().0;
579        assert_eq!(result, json!(1.5));
580    }
581
582    #[diesel_test_helper::test]
583    fn test_read_jsonb_text() {
584        // JSONB_TEXT with payload "foo"
585        let mut data = Vec::new();
586        data.extend(create_jsonb_header(JSONB_TEXT, 0x03).unwrap());
587        data.extend_from_slice(b"foo"); // Add the payload (text "foo")
588
589        let result = read_jsonb_value(&data).unwrap().0;
590        assert_eq!(result, json!("foo"));
591    }
592
593    #[diesel_test_helper::test]
594    fn test_read_jsonb_array() {
595        // JSONB_ARRAY with two elements: 1 and true
596        let mut data = Vec::new();
597        data.extend(create_jsonb_header(JSONB_ARRAY, 0x03).unwrap()); // Array header
598
599        // Element 1: integer "1"
600        data.extend(create_jsonb_header(JSONB_INT, 0x01).unwrap());
601        data.push(b'1');
602
603        // Element 2: true
604        data.extend(create_jsonb_header(JSONB_TRUE, 0x00).unwrap());
605
606        let result = read_jsonb_value(&data).unwrap().0;
607        assert_eq!(result, json!([1, true]));
608    }
609
610    #[diesel_test_helper::test]
611    fn test_read_jsonb_object() {
612        // JSONB_OBJECT with one key-value pair: "key": 42
613        let mut data = Vec::new();
614        data.extend(create_jsonb_header(JSONB_OBJECT, 0x07).unwrap()); // Object header
615
616        // Key: "key"
617        data.extend(create_jsonb_header(JSONB_TEXT, 0x03).unwrap());
618        data.extend_from_slice(b"key"); // Add the key payload
619
620        // Value: 42 (integer)
621        data.extend(create_jsonb_header(JSONB_INT, 0x02).unwrap());
622        data.extend_from_slice(b"42"); // Add the integer payload
623
624        let result = read_jsonb_value(&data).unwrap().0;
625        assert_eq!(result, json!({"key": 42}));
626    }
627
628    #[diesel_test_helper::test]
629    fn test_read_jsonb_nested_object() {
630        let mut data = Vec::new();
631
632        data.extend(create_jsonb_header(JSONB_OBJECT, 42).unwrap());
633
634        data.extend(create_jsonb_header(JSONB_TEXT, 9).unwrap());
635        data.extend_from_slice(b"outer_key");
636
637        data.extend(create_jsonb_header(JSONB_OBJECT, 13).unwrap());
638
639        data.extend(create_jsonb_header(JSONB_TEXT, 9).unwrap());
640        data.extend_from_slice(b"inner_key");
641
642        data.extend(create_jsonb_header(JSONB_INT, 2).unwrap());
643        data.extend_from_slice(b"42");
644
645        data.extend(create_jsonb_header(JSONB_TEXT, 14).unwrap());
646        data.extend_from_slice(b"additional_key");
647
648        data.extend(create_jsonb_header(JSONB_TRUE, 0).unwrap());
649
650        let result = read_jsonb_value(&data).unwrap().0;
651        assert_eq!(
652            result,
653            json!({
654                "additional_key": true,
655                "outer_key": {
656                    "inner_key": 42
657                },
658            })
659        );
660    }
661
662    #[diesel_test_helper::test]
663    fn test_write_jsonb_null() {
664        let value = serde_json::Value::Null;
665        let mut buffer = Vec::new();
666        write_jsonb_value(&value, &mut buffer).unwrap();
667        assert_eq!(buffer, vec![JSONB_NULL]);
668    }
669
670    #[diesel_test_helper::test]
671    fn test_write_jsonb_true() {
672        let value = serde_json::Value::Bool(true);
673        let mut buffer = Vec::new();
674        write_jsonb_value(&value, &mut buffer).unwrap();
675        assert_eq!(buffer, vec![JSONB_TRUE]);
676    }
677
678    #[diesel_test_helper::test]
679    fn test_write_jsonb_false() {
680        let value = serde_json::Value::Bool(false);
681        let mut buffer = Vec::new();
682        write_jsonb_value(&value, &mut buffer).unwrap();
683        assert_eq!(buffer, vec![JSONB_FALSE]);
684    }
685
686    #[diesel_test_helper::test]
687    fn test_write_jsonb_int() {
688        let value = serde_json::Value::Number(serde_json::Number::from(1));
689        let mut buffer = Vec::new();
690        write_jsonb_value(&value, &mut buffer).unwrap();
691
692        let mut expected_buffer = Vec::new();
693        expected_buffer.extend(create_jsonb_header(JSONB_INT, 0x01).unwrap());
694        expected_buffer.push(b'1'); // Payload: integer "1"
695
696        assert_eq!(buffer, expected_buffer);
697    }
698
699    #[diesel_test_helper::test]
700    fn test_write_jsonb_float() {
701        let value = serde_json::Value::Number(serde_json::Number::from_f64(1.5).unwrap());
702        let mut buffer = Vec::new();
703        write_jsonb_value(&value, &mut buffer).unwrap();
704
705        let mut expected_buffer = Vec::new();
706        expected_buffer.extend(create_jsonb_header(JSONB_FLOAT, 0x03).unwrap());
707        expected_buffer.extend_from_slice(b"1.5"); // Payload: float "1.5"
708
709        assert_eq!(buffer, expected_buffer);
710    }
711
712    #[diesel_test_helper::test]
713    fn test_write_jsonb_text() {
714        let mut buffer = Vec::new();
715        let input_string = "hello";
716        write_jsonb_string(input_string, &mut buffer).unwrap();
717
718        let mut expected_buffer = Vec::new();
719        expected_buffer.extend(create_jsonb_header(JSONB_TEXT, 0x05).unwrap());
720        expected_buffer.extend_from_slice(b"hello");
721
722        assert_eq!(buffer, expected_buffer);
723    }
724
725    #[diesel_test_helper::test]
726    fn test_write_jsonb_textj() {
727        let mut buffer = Vec::new();
728        let input_string = "hello\nworld"; // Contains a newline, requires escaping
729        write_jsonb_string(input_string, &mut buffer).unwrap();
730
731        let mut expected_buffer = Vec::new();
732        expected_buffer.extend(create_jsonb_header(JSONB_TEXTJ, 12).unwrap());
733        expected_buffer.extend_from_slice(b"hello\\nworld");
734
735        assert_eq!(buffer, expected_buffer);
736    }
737
738    #[diesel_test_helper::test]
739    fn test_write_jsonb_array() {
740        let value = json!([1, true]);
741        let mut buffer = Vec::new();
742        write_jsonb_value(&value, &mut buffer).unwrap();
743
744        let mut expected_buffer = Vec::new();
745        expected_buffer.extend(create_jsonb_header(JSONB_ARRAY, 0x03).unwrap()); // Array header
746        expected_buffer.extend(create_jsonb_header(JSONB_INT, 0x01).unwrap()); // Integer header
747        expected_buffer.push(b'1'); // Integer payload "1"
748        expected_buffer.extend(create_jsonb_header(JSONB_TRUE, 0x00).unwrap()); // Boolean header for "true"
749
750        assert_eq!(buffer, expected_buffer);
751    }
752
753    #[diesel_test_helper::test]
754    fn test_write_jsonb_object() {
755        let value = json!({"key": 42});
756        let mut buffer = Vec::new();
757        write_jsonb_value(&value, &mut buffer).unwrap();
758
759        let mut expected = Vec::new();
760        expected.extend(create_jsonb_header(JSONB_OBJECT, 7).unwrap());
761        expected.extend(create_jsonb_header(JSONB_TEXT, 3).unwrap());
762        expected.extend_from_slice(b"key");
763        expected.extend(create_jsonb_header(JSONB_INT, 2).unwrap());
764        expected.extend_from_slice(b"42");
765
766        assert_eq!(buffer, expected,);
767    }
768
769    #[diesel_test_helper::test]
770    fn jsonb_to_sql_bool() {
771        let conn = &mut connection();
772        let res = diesel::select(json!(true).into_sql::<Jsonb>().eq(&sql("jsonb('true')")))
773            .get_result::<bool>(conn)
774            .unwrap();
775        assert!(res);
776    }
777
778    #[diesel_test_helper::test]
779    fn jsonb_to_sql_null() {
780        let conn = &mut connection();
781        let res = diesel::select(json!(null).into_sql::<Jsonb>().eq(&sql("jsonb('null')")))
782            .get_result::<bool>(conn)
783            .unwrap();
784        assert!(res);
785    }
786
787    #[diesel_test_helper::test]
788    fn jsonb_to_sql_integer() {
789        let conn = &mut connection();
790        let res = diesel::select(json!(42).into_sql::<Jsonb>().eq(&sql("jsonb('42')")))
791            .get_result::<bool>(conn)
792            .unwrap();
793        assert!(res);
794    }
795
796    #[diesel_test_helper::test]
797    fn jsonb_to_sql_float() {
798        let conn = &mut connection();
799        let res = diesel::select(json!(42.23).into_sql::<Jsonb>().eq(&sql("jsonb('42.23')")))
800            .get_result::<bool>(conn)
801            .unwrap();
802        assert!(res);
803    }
804
805    #[diesel_test_helper::test]
806    fn jsonb_to_sql_text() {
807        let conn = &mut connection();
808
809        // Test for TEXT (simple string)
810        let res = diesel::select(
811            json!("hello")
812                .into_sql::<Jsonb>()
813                .eq(&sql("jsonb('\"hello\"')")),
814        )
815        .get_result::<bool>(conn)
816        .unwrap();
817
818        assert!(res);
819    }
820
821    #[diesel_test_helper::test]
822    fn jsonb_to_sql_textj() {
823        let conn = &mut connection();
824
825        // Test for TEXTJ (JSON-escaped string, e.g., containing \n or \uXXXX)
826        let res = diesel::select(
827            json!("hello\nworld")
828                .into_sql::<Jsonb>()
829                .eq(&sql("jsonb('\"hello\\nworld\"')")), // The string is JSON-escaped
830        )
831        .get_result::<bool>(conn)
832        .unwrap();
833
834        assert!(res);
835    }
836
837    #[diesel_test_helper::test]
838    fn jsonb_to_sql_array() {
839        let conn = &mut connection();
840        let res = diesel::select(
841            json!([1, true, "foo"])
842                .into_sql::<Jsonb>()
843                .eq(&sql("jsonb('[1, true, \"foo\"]')")),
844        )
845        .get_result::<bool>(conn)
846        .unwrap();
847        assert!(res);
848    }
849
850    #[diesel_test_helper::test]
851    fn jsonb_to_sql_object() {
852        let conn = &mut connection();
853        let res = diesel::select(
854            json!({"key": "value"})
855                .into_sql::<Jsonb>()
856                .eq(&sql("jsonb('{\"key\": \"value\"}')")),
857        )
858        .get_result::<bool>(conn)
859        .unwrap();
860        assert!(res);
861    }
862
863    #[diesel_test_helper::test]
864    fn jsonb_to_sql_object_in_object() {
865        let conn = &mut connection();
866        let json_value = json!({
867            "outer_key": {
868                "additional_key": true,
869                "inner_key": {
870                    "nested_key": 42
871                },
872            }
873        });
874        let res = diesel::select(json_value.into_sql::<Jsonb>().eq(&sql(
875            r#"jsonb('{"outer_key": {"additional_key": true, "inner_key": {"nested_key": 42}}}')"#,
876        )))
877        .get_result::<bool>(conn)
878        .unwrap();
879        assert!(res);
880    }
881
882    #[diesel_test_helper::test]
883    fn jsonb_to_sql_array_in_object() {
884        let conn = &mut connection();
885        let json_value = json!({
886            "is_valid": false,
887            "key": [1, 2, 3],
888        });
889        let res = diesel::select(
890            json_value
891                .into_sql::<Jsonb>()
892                .eq(&sql(r#"jsonb('{"is_valid": false, "key": [1, 2, 3]}')"#)),
893        )
894        .get_result::<bool>(conn)
895        .unwrap();
896        assert!(res);
897    }
898
899    #[diesel_test_helper::test]
900    fn jsonb_to_sql_object_in_array() {
901        let conn = &mut connection();
902        let json_value = json!([
903            {
904                "nested_key": "nested_value"
905            },
906            {
907                "int_value": 99
908            }
909        ]);
910        let res = diesel::select(json_value.into_sql::<Jsonb>().eq(&sql(
911            r#"jsonb('[{"nested_key": "nested_value"}, {"int_value": 99}]')"#,
912        )))
913        .get_result::<bool>(conn)
914        .unwrap();
915        assert!(res);
916    }
917
918    #[diesel_test_helper::test]
919    fn jsonb_from_sql_null() {
920        let conn = &mut connection();
921        let res = diesel::select(sql::<Jsonb>("jsonb('null')"))
922            .get_result::<serde_json::Value>(conn)
923            .unwrap();
924        assert_eq!(res, serde_json::json!(null));
925    }
926
927    #[diesel_test_helper::test]
928    fn jsonb_from_sql_true() {
929        let conn = &mut connection();
930        let res = diesel::select(sql::<Jsonb>("jsonb('true')"))
931            .get_result::<serde_json::Value>(conn)
932            .unwrap();
933        assert_eq!(res, serde_json::json!(true));
934    }
935
936    #[diesel_test_helper::test]
937    fn jsonb_from_sql_false() {
938        let conn = &mut connection();
939        let res = diesel::select(sql::<Jsonb>("jsonb('false')"))
940            .get_result::<serde_json::Value>(conn)
941            .unwrap();
942        assert_eq!(res, serde_json::json!(false));
943    }
944
945    #[diesel_test_helper::test]
946    fn jsonb_from_sql_int() {
947        let conn = &mut connection();
948        let res = diesel::select(sql::<Jsonb>("jsonb('42')"))
949            .get_result::<serde_json::Value>(conn)
950            .unwrap();
951        assert_eq!(res, serde_json::json!(42));
952    }
953
954    #[diesel_test_helper::test]
955    fn jsonb_from_sql_float() {
956        let conn = &mut connection();
957        let res = diesel::select(sql::<Jsonb>("jsonb('42.23')"))
958            .get_result::<serde_json::Value>(conn)
959            .unwrap();
960        assert_eq!(res, serde_json::json!(42.23));
961    }
962
963    #[diesel_test_helper::test]
964    fn jsonb_from_sql_object() {
965        let conn = &mut connection();
966        let res = diesel::select(sql::<Jsonb>("jsonb('{\"key\": \"value\"}')"))
967            .get_result::<serde_json::Value>(conn)
968            .unwrap();
969        assert_eq!(res, serde_json::json!({"key": "value"}));
970    }
971
972    #[diesel_test_helper::test]
973    fn jsonb_from_sql_array() {
974        let conn = &mut connection();
975        let res = diesel::select(sql::<Jsonb>("jsonb('[1, 2, 3]')"))
976            .get_result::<serde_json::Value>(conn)
977            .unwrap();
978        assert_eq!(res, serde_json::json!([1, 2, 3]));
979    }
980
981    #[diesel_test_helper::test]
982    fn jsonb_from_sql_nested_objects() {
983        let conn = &mut connection();
984        let res = diesel::select(sql::<Jsonb>("jsonb('{\"outer\": {\"inner\": 42}}')"))
985            .get_result::<serde_json::Value>(conn)
986            .unwrap();
987        assert_eq!(res, serde_json::json!({"outer": {"inner": 42}}));
988    }
989
990    #[diesel_test_helper::test]
991    fn jsonb_from_sql_nested_arrays() {
992        let conn = &mut connection();
993        let res = diesel::select(sql::<Jsonb>("jsonb('[[1, 2], [3, 4]]')"))
994            .get_result::<serde_json::Value>(conn)
995            .unwrap();
996        assert_eq!(res, serde_json::json!([[1, 2], [3, 4]]));
997    }
998
999    #[diesel_test_helper::test]
1000    fn jsonb_from_sql_nested_arrays_in_objects() {
1001        let conn = &mut connection();
1002        let res = diesel::select(sql::<Jsonb>("jsonb('{\"array\": [1, 2, 3]}')"))
1003            .get_result::<serde_json::Value>(conn)
1004            .unwrap();
1005        assert_eq!(res, serde_json::json!({"array": [1, 2, 3]}));
1006    }
1007
1008    #[diesel_test_helper::test]
1009    fn jsonb_from_sql_nested_objects_in_arrays() {
1010        let conn = &mut connection();
1011        let res = diesel::select(sql::<Jsonb>(
1012            "jsonb('[{\"key1\": \"value1\"}, {\"key2\": \"value2\"}]')",
1013        ))
1014        .get_result::<serde_json::Value>(conn)
1015        .unwrap();
1016        assert_eq!(
1017            res,
1018            serde_json::json!([{"key1": "value1"}, {"key2": "value2"}])
1019        );
1020    }
1021
1022    #[diesel_test_helper::test]
1023    fn jsonb_from_sql_text() {
1024        let conn = &mut connection();
1025        let res = diesel::select(sql::<Jsonb>("jsonb('\"hello\"')"))
1026            .get_result::<serde_json::Value>(conn)
1027            .unwrap();
1028        assert_eq!(res, serde_json::json!("hello"));
1029    }
1030
1031    #[diesel_test_helper::test]
1032    fn jsonb_from_sql_textj() {
1033        let conn = &mut connection();
1034        let res = diesel::select(sql::<Jsonb>("jsonb('\"hello\\nworld\"')"))
1035            .get_result::<serde_json::Value>(conn)
1036            .unwrap();
1037        assert_eq!(res, serde_json::json!("hello\nworld"));
1038    }
1039
1040    #[diesel_test_helper::test]
1041    fn bad_json_from_sql() {
1042        let conn = &mut connection();
1043        let res = diesel::select(json!(true).into_sql::<Json>().eq(&sql("json('boom')")))
1044            .get_result::<bool>(conn);
1045        assert_eq!(res.unwrap_err().to_string(), "malformed JSON");
1046    }
1047
1048    #[diesel_test_helper::test]
1049    fn bad_jsonb_from_sql() {
1050        let conn = &mut connection();
1051        let res = diesel::select(json!(true).into_sql::<Jsonb>().eq(&sql("jsonb('boom')")))
1052            .get_result::<bool>(conn);
1053        assert_eq!(res.unwrap_err().to_string(), "malformed JSON");
1054    }
1055
1056    #[diesel_test_helper::test]
1057    fn no_json_from_sql() {
1058        let uuid: Result<serde_json::Value, _> = FromSql::<Json, Sqlite>::from_nullable_sql(None);
1059        assert_eq!(
1060            uuid.unwrap_err().to_string(),
1061            "Unexpected null for non-null column"
1062        );
1063    }
1064
1065    #[diesel_test_helper::test]
1066    fn no_jsonb_from_sql() {
1067        let uuid: Result<serde_json::Value, _> = FromSql::<Jsonb, Sqlite>::from_nullable_sql(None);
1068        assert_eq!(
1069            uuid.unwrap_err().to_string(),
1070            "Unexpected null for non-null column"
1071        );
1072    }
1073}
diesel/sqlite/types/json.rs

diesel/sqlite/types/
json.rs
