tokio_postgres: serialize Json into Vec<i32>

Question

TLDR: Given a query that returns rows with a column containing a Json array:

 id |  name   | post_ids 
----+---------+----------
 1  | JohnDoe | [1,2]

Serializing it using tokio_postgres into the User model —which contains a post_ids: Vec<i32> field— seems impossible.

#[derive(Debug, Serialize, Deserialize, JsonSchema)]
pub struct User {
  pub id: String,
  pub name: String,
  pub post_ids: Vec<i32>
}

Tried implementing tokio_postgres::types::FromSql for i32, but it is only allowed for structs.

I wonder what is the common way to convert between this Json and Vec<i32>.

---

Long version with full code:

I have a Postgres Database with a User and Post models:

 id |  name   
----+---------
  1 | JohnDoe

 id |    title    | user_id 
----+-------------+---------
  1 | first post  |       1
  2 | second post |       1

I have a function to retrieve the user along with posts from database. I know I can do it better, but it is intended, as my question is about the posts_ids that this function is returning:

create function user_get_one(
  user_id int
)
returns table (
  "id"      text,
  "name"    text,
  post_ids  json
) AS $$
  select
    "user"."id",
    "user"."name",
    (select to_json(array_agg("posts"."id"))
      from (
        select
          "id"
        from
          "post"
        where
          "post"."user_id" = user_id
      ) posts
    ) posts_ids
  from "user"
  where
    "user".id = user_id;
$$ language sql;

Here is a fiddle with all DB.

Now, for testing purposes, I want an API in Rust that connects to this DB, calls this function, instantiates the appropriate models and print the data in the console, returning nothing:

Cargo.toml:

[package]
name = "test"
version = "0.1.0"
edition = "2018"

[dependencies]
tokio-postgres = "0.7.5"
tokio = { version = "1.14.0", features = ["full"] }

The imports:

use tokio_postgres::{NoTls, Row};

The models:

#[derive(Debug, Serialize, Deserialize, JsonSchema)]
pub struct User {
  pub id: String,
  pub name: String,
  // pub post_ids: Vec<i32>, // Uncomment
}

impl From<Row> for User {
  fn from(row: Row) -> Self {
    Self {
      id: row.get("id"),
      name: row.get("name"),
      // post_ids: row.get("post_ids"), // Uncomment
    }
  }
}

And the main function:

#[tokio::main]
async fn main() -> () {
  let (client, connection) = tokio_postgres::connect(
    "postgresql://localhost/rc_forum?user=test_user&password=secret_password ",
    NoTls,
  )
  .await
  .unwrap();

  tokio::spawn(async move {
    if let Err(e) = connection.await {
      eprintln!("connection error: {}", e);
    }
  });

  // Now we can execute a simple statement that just returns its parameter.
  let result = client
    .query_one("select * from user_get_one($1)", &[&1])
    .await
    .unwrap();

  let user: User = User::from(result);

  println!("-----------");
  println!("{:#?}", user);
  println!("-----------");
}

Here is the full code in Rust playground: https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=677a4f7710f6a29c7e33d3228679881f

Now, with posts_ids commented out the user is loaded and instantiated; but if we uncomment posts_ids, postgres_tokio panicks as can not convert types:

error retrieving column post_ids: error deserializing column 2: 
cannot convert between the Rust type `alloc::vec::Vec<i32>` and the Postgres type `json`',

What is the common way to convert between this Json and Vec<i32>?

Answer 1

I don't know much about Rust or tokio_postgres, but I gather that a RUST vector is basically an array that can grow in size - the equivalent of a Postgres array. Vec<i32> is a RUST vector of 4-byte integers. I don't think that can take a Postgres json value. Use a Postgres array of integer (int[]) instead:

While being at it, I simplified your convoluted function a bit:

CREATE FUNCTION user_get_one(_user_id int)
  RETURNS TABLE (id text, name text, post_ids int[])
  LANGUAGE sql AS
$func$
SELECT u.id
     , u.name
     , ARRAY(SELECT p.id FROM post p
             WHERE p.user_id = _user_id) AS posts_ids
FROM   "user" u  -- very unfortunate name!
WHERE  u.id = _user_id;
$func$;

db<>fiddle here

About the ARRAY constructor:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

I would add an ORDER BY clause to the subquery to get a deterministic result. Else, the next call may report the same array with a different sort order. So:

...
 , ARRAY(SELECT p.id FROM post p
         WHERE p.user_id = _user_id ORDER BY 1) AS posts_ids
...

In case you already have a JSON array, here's how to convert it:

• How to turn JSON array into Postgres array?

Aside: don't use reserved words like "user" as Postgres identifier.