Deep Scan with JSON

[graineri]

type User struct {
    Name string
    Customers []Customer
}

type Customer struct {
    Name string
    Type CustomerType
}

type CustomerType struct {
    Id int
    Name string
}

sqlQuery := `
    SELECT 
        u.name AS "name", 
        COALESCE(c.customers, '[]') AS "customers"
    FROM user u
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object(
                'name', customer.name,
                'type.id', ct.id,
                'type.name', ct.name
            )
        ) AS customers
        FROM customer 
        INNER JOIN user_customer uc ON uc.customer_id = customer.id
        INNER JOIN customer_type ct ON ct.id = customer.customer_type
        WHERE u.id = uc.user_id
    ) c AS TRUE
`

var users []*User
err := pgxscan.Select(ctx, db, &users, sqlQuery) 

Issue: customer type does not get populated. Only customer name does.

[georgysavva]

Hello! I understand the issue, but it's not related to scany capabilities. Scany isn't an ORM, when you define your type like that:

type User struct {
    Name string
    Customers []Customer
}

The only thing scany can do with the Customers field is to pass it as a reference to the underlying library: &Customers. After that pgx is responsible for handling []Customer type. pgx indeed tries to fill it as JSON data and you are right to build a JSON object inside the inner SELECT statement.

Since scany has nothing to do with JSON part, you need to follow Go JSON serialization rules, instead of type.id and type.name (scany style) try to do:

json_agg(
            json_build_object(
                'name', customer.name,
                'type', json_build_object(
                    'id', ct.id,
                    'name', ct.name,
                )
            )
        )

I hope it helps!

[graineri]

Yes, that's correct. I ended up doing it in a similar way. Thanks Georgy.