go scan Postgres array_agg

Question

I have a one-to-many relationship in postgres (Event has many EventUser), and would like to scan and store into a struct Event.

// EventUser struct
type EventUser struct {
    ID              int64
    CheckedIn       bool
    PaidAmount      float32
}

// Event struct
type Event struct {
    ID         int64  `json:"id"`
    Name       string `json:"name"`
    StartTime  string `json:"startTime"`
    EventUsers string
}

Here is the query:

SELECT events.id, events.name, events."startTime", e."eventUsers" as "eventUsers"
FROM "Events" as events
LEFT JOIN (
        SELECT events.id as id, array_to_json(array_agg(eu.*)) as "eventUsers"
        FROM "EventUsers" as eu
        JOIN "Events" AS "events" ON events.id = eu."eventId"
        WHERE eu.status = 'RESERVED'
        GROUP BY events.id
    ) AS e USING (id)
WHERE events.status = 'COMPLETED'

The query returns this:

{
  id: 2,
  name: "2 Events are 48 days from now",
  startTime: 1590471343345,
  eventUsers: [
    {
      id: 2,
      checkedIn: false,
      paidAmount: 8
    },
    {
      id: 3,
      checkedIn: false, 
      paidAmount: 8,
    },
  ],
};

This is what I am trying to do, which directly scan each item under eventUsers into struct, and store in the Event's struct.

got := []Event{}

for rows.Next() {
    var r Event
    err = rows.Scan(&r.ID, &r.Name, &r.StartTime, &r.EventUsers)
    if err != nil {
        panic(err)
    }
}

I think I could achieve this by storing the array as a string and then unmarshal it.

What I want is something similar to sql.NullString.

mkopriva · Accepted Answer · 2020-04-09 03:50:20Z

1

You could define a slice type that implements the sql.Scanner interface. Note that when you pass an instance of a type that implements Scanner to a (*sql.Rows).Scan or (*sql.Row).Scan call, the impelmenter's Scan method will be invoked automatically.

type EventUserList []*EventUser

func (list *EventUserList) Scan(src interface{}) error {
    if data, ok := src.([]byte); ok && len(data) > 0 {
        if err := json.Unmarshal(data, list); err != nil {
            return err
        }
    }
    return nil
}

Then, assuming that the e."eventUsers" in the select query is a json array, you could use it like this:

// EventUser struct
type EventUser struct {
    ID         int64
    CheckedIn  bool
    PaidAmount float32
}

// Event struct
type Event struct {
    ID         int64         `json:"id"`
    Name       string        `json:"name"`
    StartTime  string        `json:"startTime"`
    EventUsers EventUserList `json:"eventUsers"`
}

// ...

var events []*Event
for rows.Next() {
    e := new(Event)
    if err := rows.Scan(&e.ID, &e.Name, &e.StartTime, &e.EventUsers); err != nil {
        return err
    }
    events = append(events, e)
}

answered Apr 9, 2020 at 3:50

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

leogoesger Over a year ago

Is this the polymorphism?

mkopriva Over a year ago

@leogoesger I'm not sure if polymorphism is the precise technical term, but wikipedia's section on go's interface system mentions that it provides "runtime polymorphism" so yeah, maybe that's what one would call it.

Collectives™ on Stack Overflow

go scan Postgres array_agg

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related