Invalid input syntax with nested json

[Kav91]

When performing a query with nested JSON (in the special value field)

It will cause this Error: E("22P02:invalid input syntax for type json")

    let settings = json!({
        "tags": ["rust", "memory-safety", "programming"],
        "category": "programming",
        "featured": false,
        "special_value": "{\"abc\": 123 }",
    });

if you remove the special_value field it will work eg.

    let settings = json!({
        "tags": ["rust", "memory-safety", "programming"],
        "category": "programming",
        "featured": false,
    });

Example app to reproduce

[dependencies]
rbdc-pg = { version = "4.7.1" }
rbs = { version = "4.7.0"}
rbatis = { version = "4.7.2"}
tokio = { version = "1", features = ["full"] }
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"

use rbatis::RBatis;
use rbdc_pg::driver::PgDriver;
use rbs::value;
use serde::{Deserialize, Serialize};
use serde_json::json;

#[derive(Debug, Serialize, Deserialize)]
struct Post {
    id: Option<i32>,
    user_id: i32,
    title: String,
    content: String,
    slug: String,
    view_count: i32,
    rating: f64,
    settings: serde_json::Value,
    status: String,
    published_at: String,
    created_at: Option<i64>,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let rb = RBatis::new();
    rb.init(
        PgDriver {},
        "postgresql://test:test@localhost:5432/testdb?connect_timeout=10",
    )?;

    let user_id = 1;
    let title = "@Understanding Rust Ownership";
    let content = "Rust's ownership system is one of its most distinctive features.";
    let slug = "understanding-rust-ownership-9";
    let view_count = 0;
    let rating = 4.5;
    let settings = json!({
        "tags": ["rust", "memory-safety", "programming"],
        "category": "programming",
        "featured": false,
        "special_value": "{\"abc\": 123 }", // if you remove this it will work
    });

    let status = "published";
    let published_at = "2024-02-06T10:30:00Z";

    let query = r#"
        INSERT INTO posts (user_id, title, content, slug, view_count, rating, settings, status, published_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::timestamp)
        RETURNING *;
    "#;

    let rbs_settings = value!(settings);

    println!("\nrbs settings -> {:?}", rbs_settings);

    let params = vec![
        value!(user_id),
        value!(title),
        value!(content),
        value!(slug),
        value!(view_count),
        value!(rating),
        rbs_settings,
        value!(status),
        value!(published_at),
    ];

    println!("Executing query with params:");
    for (i, param) in params.iter().enumerate() {
        println!("  ${}: {:?}", i + 1, param);
    }

    // Execute the query
    match rb.query_decode::<Post>(query, params).await {
        Ok(result) => {
            println!("\n✓ Query executed successfully!");
            println!("Result: {:#?}", result);
        }
        Err(e) => {
            eprintln!("\n✗ Query failed!");
            eprintln!("Error: {:?}", e);
            return Err(e.into());
        }
    }

    Ok(())
}

Example table schema

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    content TEXT,
    slug VARCHAR(500) UNIQUE,
    view_count BIGINT DEFAULT 0,
    rating REAL,
    settings JSONB,
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

[zhuxiujia]

pg json column must be use json value

let settings = rbdc::types::Json::from(json!({
       "tags": ["rust", "memory-safety", "programming"],
       "category": "programming",
       "featured": false,
       "special_value": {"abc": 123 }, // must be json not json string
   });