Best Practice to go from sphinx db to somewhat complex golang struct

Question

I am trying to convert a sphinx query via MySQL into a golang struct and am having a hard time. It seems like this should be a common sort of problem, but so far I'm stuck converting it to a map or parsing output myself. Specifically, I have a schema in sphinx that looks like {Source: {ID:string, subId:string, Campaigns:[]{CampaignID:string, Status:string}}}

I've tried using the simple rows.scan but that doesn't help me parse the repeating field, I just get it as an unparsed string. In sphinx, the key's aren't in quotation marks, so JSON.unmarshal doesn't seem to help at all. And using sqlx, I've tried to build up the following struct

type CampaignStatus struct {
    CampaignId string
    Status string
}
type Source struct {
    Id               string
    SubId    string
    StatusByCampaign []CampaignStatus
}
type Status struct {
    Source
}

and passing in a Status struct to Row.ScanStruct() and I get back either a "Missing destination Name Source" error or if I name the source member in Status, I get "sql: Scan error on column index 0: unsupported Scan, storing driver.Value type []uint8 into type *v1.Source". Full disclosure, the sphinx schema has a few other columns as peers with StatusByCampaign, they come after it and I don't care about them in my use case.

This seems like something that has to have been come across before, but I can't seem to find a solution outside of writing my own parser, which I am loath to do.

Answer 1

I found out the legacy system I'm working with was using an ASP.Net library called ServiceStack to serialize/deserialize my sphinx db, and it was using it's own custom JSV format, which the author of ServiceStack say as an optimized mix of JSON and CSV, but hasn't been adopted anywhere else as far as I can tell.

So it looks like I'm going to either rewrite that library in Go, or more likely change the indexing algorithm to use JSON.

Answer 2

I think it might be the way you are defining your structs. Given the json you provided {Source: {ID:string, subId:string, Campaigns:[]{CampaignID:string, Status:string}}} your struct should look like something close to whats below.

type object struct {
    Source struct {
        ID        string     `db:"id"`
        SubId     string     `db:"sub_id"`
        Campaigns []compaign `db:"compaigns"`
    } `json:"source"`
}

type compaign struct {
    CampaignID string `db:"compaign_id"`
    Status     string `db:"status"`
}

Given the comments below. Try obj := make(map[string]interaface{}) instead of the defining a struct.

Also taking a look at https://github.com/jmoiron/sqlx the struct should be using the db flag instead of json, so I changed it. You shouldn't have to marshal the data

example db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC") Where &people is a reference to a struct. E.g var people PeopleStruct

Now I'm not famailar with your driver, but it should be the same, in that you pass a reference to a struct or interface with your query and it looks the data into that reference. You don't normally have to unmarshall in the struct after the query.