JSON_AGG is not automatically inferenced for type generation

[adhupraba]

Version

1.20.0

What happened?

I am using Golang for my backend and sqlc to generate types for the queries. I have a query like the following:

-- name: FindPostsOfASubreddit :many
SELECT
  posts.*,
  sqlc.embed(users),
  JSON_AGG(votes.*) AS votes,
  JSON_AGG(comments.*) AS comments
FROM posts
  INNER JOIN users ON users.id = posts.author_id
  LEFT JOIN votes ON votes.post_id = posts.id
  LEFT JOIN comments ON comments.post_id = posts.id
WHERE posts.subreddit_id = $1
GROUP BY posts.id, users.id
OFFSET $2 LIMIT $3;

i am querying all posts of a subreddit and within each post i want the author of the post, votes and comments of the post to be embedded.

expected generated type:

type FindPostsOfASubredditRow struct {
	ID          int32                `json:"id"`
	Title       string               `json:"title"`
	Content     types.NullRawMessage `json:"content"`
	SubredditID int32                `json:"subredditId"`
	AuthorID    int32                `json:"authorId"`
	CreatedAt   time.Time            `json:"createdAt"`
	UpdatedAt   time.Time            `json:"updatedAt"`
	User        User                 `json:"user"`
	Votes       []Vote      `json:"votes"`
	Comments    []Comment      `json:"comments"`
}

instead what i am getting is:

type FindPostsOfASubredditRow struct {
	ID          int32                `json:"id"`
	Title       string               `json:"title"`
	Content     types.NullRawMessage `json:"content"`
	SubredditID int32                `json:"subredditId"`
	AuthorID    int32                `json:"authorId"`
	CreatedAt   time.Time            `json:"createdAt"`
	UpdatedAt   time.Time            `json:"updatedAt"`
	User        User                 `json:"user"`
	Votes       json.RawMessage      `json:"votes"`
	Comments    json.RawMessage      `json:"comments"`
}

Votes and Comments are getting typed as json.RawMessage. Is there a way to safely type them like in the expected output? I tried to use sqlc.embed() here. but it seems to be working only for one-to-one relations.

Relevant log output

No response

Database schema

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  image TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);


CREATE TABLE subreddits (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  creator_id INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE posts (
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  content JSONB,
  subreddit_id INT NOT NULL REFERENCES subreddits(id) ON DELETE CASCADE,
  author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE comments (
  id SERIAL NOT NULL PRIMARY KEY,
  text TEXT NOT NULL,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  reply_to_id INT REFERENCES comments(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TYPE vote_type AS ENUM ('UP', 'DOWN');

CREATE TABLE votes (
  id SERIAL NOT NULL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type vote_type NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

  UNIQUE(user_id, post_id)
);

SQL queries

-- name: FindPostsOfASubreddit :many
SELECT
  posts.*,
  sqlc.embed(users),
  JSON_AGG(votes.*) AS votes,
  JSON_AGG(comments.*) AS comments
FROM posts
  INNER JOIN users ON users.id = posts.author_id
  LEFT JOIN votes ON votes.post_id = posts.id
  LEFT JOIN comments ON comments.post_id = posts.id
WHERE posts.subreddit_id = $1
GROUP BY posts.id, users.id
OFFSET $2 LIMIT $3;

Configuration

version: "2"
sql:
  - schema: "internal/migrations"
    queries: "internal/queries"
    engine: "postgresql"
    gen:
      go:
        out: "internal/database"
        emit_json_tags: true
        json_tags_case_style: "camel"
        overrides:
          - column: "public.users.password"
            go_struct_tag: 'json:"-"'
          - db_type: "pg_catalog.int4"
            go_type:
              import: "xxx"
              type: "NullInt32"
            nullable: true
          - db_type: "text"
            go_type:
              import: "xxx"
              type: "NullString"
            nullable: true
          - db_type: "jsonb"
            go_type:
              import: "xxx"
              type: "NullRawMessage"
            nullable: true

Playground URL

https://play.sqlc.dev/p/a4d72d72ec6333625752527286b88f5254731465c5d77047ebc93cb2dcfcf0df

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[topazur]

• column Only officially existing columns are supported

• db_type Overwrite JSON type，create a big struct to deserialize

[topazur]

I hope the author can support intermediate state columns

[adhupraba]

@topazur if i do as you specified, won't wherever I query any aggregated data becomes []*TestStructXXX type. How do I make sqlc generate slice of TestStructXXX struct only for this particular query?

[topazur]

@topazur if i do as you specified, won't wherever I query any aggregated data becomes []*TestStructXXX type. How do I make sqlc generate slice of TestStructXXX struct only for this particular query?

#145
I can't understand it from here. I'm going to use a separate struct to serialize JSON, where all the fields are placed in this struct. This is an ugly method

[kyleconroy]

sqlc does not support unmarshalling JSON into Go types. I'm tracking this issue here: #2794

[kyleconroy]

Actually tracking here #2761