postgresql - 如何将 HashMap 作为 JSON 类型插入到 PostgreSQL 中？

Question

contacts数据结构为HashMap ，我正在使用 PostgreSQL客户 - rust-postgres插入 contact的键和值放到一个表中，然后我要从表中选择。以下是我到目前为止所尝试的。我需要帮助来编写正确的语法。

use postgres::{Client, NoTls};
use std::collections::HashMap;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;

    client.simple_query("
        DROP TABLE 
        IF EXISTS following_relation;
        ")?;

    client.simple_query("
        CREATE TABLE following_relation (
            id               SERIAL NOT NULL PRIMARY KEY,
            relation         JSON NOT NULL
        )
    ")?;

    let mut contacts = HashMap::new();
    let mut v: Vec<String> = Vec::new();

    v = vec!["jump".to_owned(), "jog".to_string()];
    contacts.insert("Ashley", v.clone());

    for (name, hobby) in contacts.iter() {
        // 1. How to write sql statement with parameters?
        client.execute(
        "INSERT INTO following_relation(relation) 
         VALUE ('{"name" : $1, "hobby" : $2}')", 
        &[&name, &hobby],
    )?;  
    }

    for row in client.query("SELECT id, relation FROM following_relation", &[])? {
        // 2. How to read from parse the result?
        let id: i32 = row.get(0);
        let relation = row.get(1);
        //println!("found person: {} {} {:?}", id, relation["name"], relation["hobby"]); 
    }
    Ok(())
}

我得到了提示

1. Like the error message says, your query has VALUE but it needs to be VALUES.
2. Query parameters cannot be interpolated into strings. You should build the object in Rust, and use https://docs.rs/postgres/0.17.0/postgres/types/struct.Json.html to wrap the types when inserting.

我不知道如何申请pub struct Json<T>(pub T);在这里。

如何构建 query函数中需要 execute ？

pub fn execute<T: ?Sized>(
    &mut self,
    query: &T,
    params: &[&(dyn ToSql + Sync)]
) -> Result<u64, Error>
where
    T: ToStatement, 

---

已更新，我尝试使用更简短的代码示例

use postgres::{Client, NoTls};
use postgres::types::Json;
use serde::{Deserialize, Serialize};

#[derive(Debug, Serialize, Deserialize)]
struct relations {
    name : String,
    hobby: Vec<String>
}
pub struct Json<T>(pub T);

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;

    client.simple_query("
        DROP TABLE 
        IF EXISTS following_relation;

        ")?;

    client.simple_query("
        CREATE TABLE following_relation (
            id      SERIAL PRIMARY KEY,
            relation    JSON NOT NULL
        )
    ")?;

    let rel = relations {
        name: "czfzdxx".to_string(),
        hobby: vec![
            "basketball".to_string(),
            "jogging".to_string()
        ],
    };

    client.execute(
        r#"INSERT INTO following_relation(relation)
             VALUE ($1)"#,
        &[&Json(&rel)]
    )?;

    Ok(())
}

我明白了

error[E0432]: unresolved import `postgres::types::Json`

Answer 1

你想要 Rust raw string literal :

for (name, hobby) in contacts.iter() {
    client.execute(
        r#"INSERT INTO following_relation(relation) 
           VALUE ('{"name" : ($1), "hobby" : ($2)}')"#,
        &[&name, &following],
    )?;
}

在开头 r#" 和结尾 "# 之间，您的字符串文字可以包含除 # 本身之外的任何字符，无需转义。如果您还想要 # 本身，则原始字符串文字以多个 # 开始，并以匹配数量的 # 结束。