How to filter fields from the database in JSON response?

Question

i am making a REST API in golang and i want to add support for filtering fields but i don't know the best way to implement that, lets say i have this structure representing an Album model

type Album struct {
  ID            uint64    `json:"id"`
  User          uint64    `json:"user"`
  Name          string    `json:"name"`
  CreatedDate   time.Time `json:"createdDate"`
  Privacy       string    `json:"privacy"`
  Stars         int       `json:"stars"`
  PicturesCount int       `json:"picturesCount"`
}

and a function that returns an instance of an Album

func GetOne(id uint64, user uint64) (Album, error) {

  var album Album

  sql := `SELECT * FROM "album" WHERE "id" = $1 AND "user" = $2;`

  err := models.DB.QueryRow(sql, id, user).Scan(
    &album.ID,
    &album.User,
    &album.Name,
    &album.CreatedDate,
    &album.Privacy,
    &album.Stars,
    &album.PicturesCount,
  )

  return album, err

}

and the client was to issue a request like this
https://api.localhost.com/albums/1/?fields=id,name,privacy

obvious security issues aside, my first thought was to filter the fields in the database using something like this

func GetOne(id uint64, user uint64, fields string) {

  var album Album

  sql := fmt.Sprintf(`SELECT %s FROM "album" WHERE "id" = $1 AND "user" = $2;`, fields)

  // i don't know what to do after this

}

and then i thought of adding omitempty tag to all the fields and setting the fields to their zero value before encoding it to JSON,

• would this work?
• which one is the better way?
• is there a best way?
• how would i go about implementing the first method?

Thank you.

Answer 1

For your first proposal (querying only the requested fields) there are two approaches (answering "would this work?" and "how would I go about implementing the first method?"):

1. Dynmaically reate a (possibly anonymous) struct and generate JSON from there using encoding/json.
2. Implement a wrapper that will translate the *database/sql.Rows you get back from the query into JSON.

For approach (1.), you will somehow need to create structs for any combination of attributes from your original struct. As reflect cannot create a new struct type at runtime, your only chance would be to generate them at compile time. The combinatorial explosion will bloat your binary, so do not do that.

Approach (2.) is to be handled with caution and can only be a last resort. Taking the list of requested fields and writing out JSON with the values you got from DB sounds straightforward and does not involve reflection. However your solution will be (very likely) much more unstable than encoding/json.

When reading your question I too thought about using the json:"omitempty" struct tag. And I think that it is the preferable solution. It does neither involve metaprogramming nor writing your own JSON encoder, which is a good thing. Just be aware of the implications in case some fields are missing (client side maybe has to account for that). You could query for all attributes always and override the unwanted ones using reflection.

In the end, all above solutions are suboptimal, and the best solution would be to not implement that feature at all. I hope you have a solid reason to make attributes variable, and I am happy to further clarify my answer based on your explaination. However, if one of the attributes of a resource is too large, it maybe should be a sub-resource.