Using postgres row_to_json as a Projection type

[tylermmorton]

Hi! I'd like to use the postgres row_to_json function with jet. My end goal is to convert a row selected via subquery to a single json column that can then be unmarshaled later.

The serialized query would look something like:

SELECT assignments.*,
    (
        SELECT row_to_json(projects.*)
        FROM public.projects
        WHERE projects.id = assignments.project_id
    ) AS "project"
FROM public.assignments;

I'm not sure how to do this with jet or if its even supported. I would expect this syntax to look something like:

A modifier on the ColumnList type

Assignments.
  SELECT(
    Assignments.AllColumns,
    Projects.
        Select(Projects.AllColumns.RowToJSON()). // <- Proposed RowToJSON method
        Where(Projects.ID.EQ(Assignments.ProjectID)).
        As("project")
  )

Or a wrapper

import (
  pg "github.com/go-jet/jet/v2/postgres"
)

Assignments.
  SELECT(
    Assignments.AllColumns,
    Projects.
        Select(pg.RowToJSON(Projects.AllColumns)).
        Where(Projects.ID.EQ(Assignments.ProjectID)).
        As("project")
  )

I did notice there is a pg.Func helper that seems to do something like this but this produces invalid syntax, where the row_to_json function wraps the entire expression instead of the projection.

  pg.Func("row_to_json", Projects.
      SELECT(Projects.AllColumns).
      WHERE(Projects.ID.EQ(Assignments.ProjectID)),
  ).AS("project"),

This is an invalid query since a subquery can only return a single column

SELECT assignments.*,
    row_to_json((
        SELECT projects.*
        FROM public.projects
        WHERE projects.id = assignments.project_id
    )) AS "project"
FROM public.assignments;

It looks like we would need a similar helper for the ColumnList type.

If this isn't implemented I'd be happy to put something together with a little guidance!

Thanks in advance

[tylermmorton]

Update:

A raw expression works fine

Assignments.
        INSERT(Assignments.AllColumns).
        MODEL(&input).
        RETURNING(
	        Assignments.AllColumns,
	        Projects.
		        SELECT(jet.Raw("row_to_json(projects.*)")).
		        WHERE(Projects.ID.EQ(Assignments.ProjectID)).
		        AS("project"),
        )

I also made a utility to do this in a naive way:

func ROW_TO_JSON(table jet.Table) jet.Expression {
	return jet.Raw(fmt.Sprintf("row_to_json(%s.*)", table.TableName()))
}

However, I think it could be improved using a SelectTable because this allows you to use the Projection utilities to select out the columns of Project you want in the json instead of selecting everything.

Assignments.
        INSERT(Assignments.AllColumns).
        MODEL(&input).
        RETURNING(
	        Assignments.AllColumns,
	        Projects.
		        SELECT(
			        pg.Raw("row_to_json(t)"),
		        ).
		        FROM(
			        Projects.
				        SELECT(Projects.AllColumns.Except(Projects.DeletedAt)).
				        WHERE(Projects.ID.EQ(Assignments.ProjectID)).
				        AsTable("t"),
		        ).
		        AS("project"),
        )

The issue with the above is that the SQL serializer automatically appends aliases to every column in the projection, which muddles the json:

INSERT INTO public.assignments (...)
VALUES (...)
RETURNING assignments.id AS "assignments.id",
          (
               SELECT row_to_json(t)
               FROM (
                         SELECT projects.id AS "projects.id",
                              projects.name AS "projects.name",
                              projects.created_at AS "projects.created_at",
                              projects.updated_at AS "projects.updated_at",
                              projects.deleted_at AS "projects.deleted_at"
                         FROM public.projects
                         WHERE projects.id = assignments.project_id
                    ) AS t
          ) AS "project";

Result:

{
  "projects.id": "2s6UISqaxZlEdkuKxh4EsIOvi5P",
  "projects.name": "Test Project",
  "projects.created_at": "2025-01-25T03:23:57.848449+00:00",
  "projects.updated_at": null,
  "projects.deleted_at": null
}

[trevlt]

Maybe you can use STAR to avoid automatic aliasing:

SELECT(
	pg.Raw("row_to_json(t)"),
).
FROM(
	Projects.SELECT(STAR).
		WHERE(Projects.ID.EQ(Assignments.ProjectID)).
		AsTable("t"),
).
AS("project"),

[houtn11]

Is JSON a requirement or a way to return all projects for inserted assignments? If it is not a requirement you can achieve the same using CTE:

stmt := WITH(
    insertedAssigment.AS( 
        Assignments.INSERT(Assignments.AllColumns).
        MODEL(&input).
        RETURNING(Assignments.AllColumns)
    ),
)(
   SELECT(
       insertedAssigment.AllColumns(),
       Projects.AllColumns,
   ).FROM(
      insertedAssigment.LEFT_JOIN(Projects, ....
   )
)

This way you can scan directly using qrm:

var dest struct {
    model.Assigment

    Projects []model.Projects
}

err := stmt.Query(db, &dest)

[go-jet]

From version v2.13.0, you can achieve this with SELECT_JSON statements also:

stmt := Assignments.INSERT(Assignments.AllColumns).
        MODEL(&input).
        RETURNING(
	        Assignments.AllColumns,
	       
		SELECT_JSON_ARR(Projects.AllColumns.Except(Projects.DeletedAt)).
		        FROM(Projects).
                        WHERE(Projects.ID.EQ(Assignments.ProjectID)).
		        AS("Projects"),
        )

var dest struct {
    model.Assigments

    Projects []model.Projects `json_column:"Projects"`
}

err := stmt.Query(db, &dest)