I am using this library from http://jmoiron.github.io/sqlx/ to do a query. Following the documentation led me to the code below.
func cities(w http.ResponseWriter, r *http.Request, _ httprouter.Params) error {
var main string
var secondary string
var limit string
queryParams := make(map[string]interface{})
if k := r.PostFormValue("keyword"); k != "" {
main = "city.name LIKE :keyword"
queryParams["keyword"] = k + "%"
}
if sk := r.PostFormValue("secondaryKeyword"); sk != "" && sk != "null" {
secondary = "OR city.name = :secondaryKeyword"
queryParams["secondaryKeyword"] = sk
}
if mr := r.PostFormValue("maxResults"); mr != "" {
limit = "LIMIT :maxResults"
queryParams["maxResults"] = mr
}
if lr := r.PostFormValue("lastRequest"); lr != "" && lr == "1" {
limit = ""
}
query := fmt.Sprintf(`
SELECT
city.geonameid AS cityId,
city.name AS cityName,
COALESCE(admin1.name_local, '') AS admin1Name,
country.name AS countryName,
CONCAT_WS(' ', city.name, city.asciiname, country.name) AS searchString
FROM geonames_cities1000 AS city
INNER JOIN geonames_countryinfo AS country
ON city.iso_alpha2 = country.iso_alpha2
LEFT OUTER JOIN geonames_admin1_codes_ascii as admin1
ON admin1.code = CONCAT(city.iso_alpha2, '.', city.admin1_code)
WHERE %s %s
ORDER BY city.name ASC %s;
`, main, secondary, limit)
nstmt, err := sql.DB.PrepareNamed(query)
if err != nil {
return err
}
rows, err := nstmt.Queryx(queryParams)
if err != nil {
return err
}
results := []interface{}{}
for rows.Next() {
row := make(map[string]interface{})
err := rows.MapScan(row)
if err != nil {
return err
}
results = append(results, row)
}
b, err := json.Marshal(results)
if err != nil {
return err
}
w.Write(b)
return nil
}
Sending a POST request postman chrome plugin with these values:
keyword: "tron"
maxResults: 7
lastRequest: 0
gave this JSON output:
[
{
"admin1Name": "VXR0YXJhZGl0",
"cityId": 1605268,
"cityName": "VHJvbg==",
"countryName": "VGhhaWxhbmQ=",
"searchString": "VHJvbiBUcm9uIFRoYWlsYW5k"
},
{
"admin1Name": "Q2FsYWJyaWE=",
"cityId": 8949073,
"cityName": "VHJvbmNh",
"countryName": "SXRhbHk=",
"searchString": "VHJvbmNhIFRyb25jYSBJdGFseQ=="
},
{
"admin1Name": "QXJhZ29u",
"cityId": 3107444,
"cityName": "VHJvbmNow7Nu",
"countryName": "U3BhaW4=",
"searchString": "VHJvbmNow7NuIFRyb25jaG9uIFNwYWlu"
},
{
"admin1Name": "UHVlYmxh",
"cityId": 8859151,
"cityName": "VHJvbmNvbmFs",
"countryName": "TWV4aWNv",
"searchString": "VHJvbmNvbmFsIFRyb25jb25hbCBNZXhpY28="
},
{
"admin1Name": "U2NobGVzd2lnLUhvbHN0ZWlu",
"cityId": 2821000,
"cityName": "VHLDtm5kZWw=",
"countryName": "R2VybWFueQ==",
"searchString": "VHLDtm5kZWwgVHJvbmRlbCBHZXJtYW55"
},
{
"admin1Name": "U8O4ci1UcsO4bmRlbGFn",
"cityId": 3133880,
"cityName": "VHJvbmRoZWlt",
"countryName": "Tm9yd2F5",
"searchString": "VHJvbmRoZWltIFRyb25kaGVpbSBOb3J3YXk="
},
{
"admin1Name": "VG9uZ3Nh",
"cityId": 1252408,
"cityName": "VHJvbmdzYQ==",
"countryName": "Qmh1dGFu",
"searchString": "VHJvbmdzYSBUcm9uZ3NhIEJodXRhbg=="
}
]
Why are the results like this? Putting the results in spew.Dump() will give something like this (I'm just outputting one of the rows):
(map[string]interface {}) (len=5) {
(string) (len=11) "countryName": ([]uint8) (len=6 cap=6) {
00000000 4e 6f 72 77 61 79 |Norway|
},
(string) (len=12) "searchString": ([]uint8) (len=26 cap=26) {
00000000 54 72 6f 6e 64 68 65 69 6d 20 54 72 6f 6e 64 68 |Trondheim Trondh|
00000010 65 69 6d 20 4e 6f 72 77 61 79 |eim Norway|
},
(string) (len=6) "cityId": (int64) 3133880,
(string) (len=8) "cityName": ([]uint8) (len=9 cap=9) {
00000000 54 72 6f 6e 64 68 65 69 6d |Trondheim|
},
(string) (len=10) "admin1Name": ([]uint8) (len=15 cap=15) {
00000000 53 c3 b8 72 2d 54 72 c3 b8 6e 64 65 6c 61 67 |S..r-Tr..ndelag|
}
What do I do wrong here?'
EDIT:
I tried doing as Elwinar suggested with structs instead. But something really weird is going on.
This code works:
type City struct {
AdminName string `json:"admin1Name" db:"admin1Name"`
CityID int64 `json:"cityId" db:"cityId"`
CityName string `json:"cityName" db:"cityName"`
CountryName string `json:"countryName" db:"countryName"`
SearchString string `json:"searchString" db:"searchString"`
}
But this code does not work and outputs error "missing destination name cityId":
type City struct {
CityId int64 `json:"cityId" db:"cityId"`
CityName string `json:"cityName" db:"cityName"`
Admin1Name string `json:"admin1Name" db:"admin1Name"`
CountryName string `json:"countryName" db:"countryName"`
SearchString string `json:"searchString" db:"searchString"`
}
What is the difference?
Solution: Must separate the tags with space key. Can not do spacing with tab key, and can not use commas to separate tags.
As spew.Dump tells you, the SQL driver returns []uint8 for textual column. In fact, it is equivalent to []byte, which json.Marshal encode as base64 strings.
The simplest way to work around that is to scan your rows into a real struct (which sqlx does quite well with StructScan), which will have string fields so that json.Marshal will display them as you expect. You can control the name of both the SQL and JSON names of the field using tags, so your entity can have conventionnal names in every language…
Example:
type City struct {
AdminName string `json:"admin1Name" sql:"admin1Name"`
CityID int64 `json:"cityId" sql:"cityId"`
CityName string `json:"cityName" sql:"cityName"`
CountryName string `json:"countryName" sql:"countryName"`
SearchString string `json:"searchString" sql:"searchString"`
}
Package encoding/json will marshal []byte slices base64 encoded (see http://golang.org/pkg/encoding/json/#Marshal). So the problem is just in the output via json.Marshal(results).
Somewhere your code calls a function which produces a []byte which is nicely hidden in one of these {}interfaces . Get rid of each of the empty interfaces, use strings where appropriate and the problem will either vanish or the solution will be obvious.
(Calling json.Marshal and outputting the result for debug purpose might not be a good idea, especially if you do not really know what you are actuall marshaling.)