Golang N1qlQuery json marshal empty result [resolved]

Hi,

I build a simple Api in go, and I’m trying to list some document, like this one

{
  "artist": "Tugalo Holler",
  "song": "Prince of peace",
  "picture": "Tugalo Holler.png",
  "mix": "princeofpeace.mp3",
  "before": "princeofpeace_before.mp3",
  "facebook": "https://www.facebook.com/tugaloholler/",
  "leadVocal": "f",
  "table": "portfolio"
}

I’m following the example from this blog post : N1QL – Typed and Untyped JSON Schemas in GO

My struct is this one :

package main

import "encoding/json"

type Portfolios []Portfolio
type Portfolio struct {
	Artist    string `json:"artist"`
	Song      string `json:"song"`
	Picture   string `json:"picture"`
	Mix       string `json:"mix"`
	Before    string `json:"before"`
	Facebook  string `json:"facebook"`
	LeadVocal string `json:"leadVocal"`
}

here is the function code :

func portfoliosHandler(w http.ResponseWriter, r *http.Request, ps httprouter.Params) {
	strQuery := Concat("SELECT * FROM ", getBucket().Name(), " where table = 'portfolio'")
	fmt.Println(strQuery)
	myQuery := gocb.NewN1qlQuery(strQuery)
	myQuery.AdHoc(false)

	var row Portfolio
	var retValues Portfolios

	rows, err := getBucket().ExecuteN1qlQuery(myQuery, nil)
	if err != nil {
		fmt.Println(err)
		http.Error(w, "err", http.StatusInternalServerError)
		return
	}

	for rows.Next(&row) {
		fmt.Println(row)
		retValues = append(retValues, row)
		row = Portfolio{}
	}
	err = rows.Close()
	if err != nil {
		fmt.Println(err)
		http.Error(w, "err", http.StatusInternalServerError)
		return
	}

	bytes, err := json.Marshal(retValues)
	if err != nil {
		fmt.Println(err)
		http.Error(w, "err", http.StatusInternalServerError)
		return
	}

	w.Write(bytes)
}

The result is actualy :

[{“artist”:“”,“song”:“”,“picture”:“”,“mix”:“”,“before”:“”,“facebook”:“”,“leadVocal”:“”}]

I know that the trouble is around the “for” loop, with the “Next” func, because when I replace that

var row Portfolio
	var retValues Portfolios

with that :

var row interface{}
	var retValues []interface{}

I haven’t the same result :

`[{"ssogo":{"artist":"Tugalo Holler","before":"princeofpeace_before.mp3","facebook":"https://www.facebook.com/tugaloholler/","leadVocal":"f","mix":"princeofpeace.mp3","picture":"Tugalo Holler.png","song":"Prince of peace","table":"portfolio"}}]`

I espected that :

[    
{
      "artist": "Tugalo Holler",
      "song": "Prince of peace",
      "picture": "Tugalo Holler.png",
      "mix": "princeofpeace.mp3",
      "before": "princeofpeace_before.mp3",
      "facebook": "https://www.facebook.com/tugaloholler/",
      "leadVocal": "f",
      "table": "portfolio"
    }
]

I don’t understand what is wrong. Any idea ?

Regards

Steeve

Hi @steeve.descarpentrie, the issue here is actually your query statement. In the blog post that you’re following you’ll see that the struct format used is

type TypedJSONAirport struct {
Inner struct {
	Airport string `json:"airportname"`
	City    string `json:"city"`
	Country string `json:"country"`
	FAA     string `json:"faa"`
	Geo     struct {
		Alt int     `json:"alt"`
		Lat float64 `json:"lat"`
		Lon float64 `json:"lon"`
	} `json:"geo"`
} `json:"travel-sample"`
}

You can see here that there the document body is wrapped in Inner which uses the json key of “travel-sample” (the bucket name). What’s happening is that your query is putting each of your results into a namespace of whatever your bucket name is. e.g. if your bucket name was default then you’d see something like:

{"default":{
 "artist": "Tugalo Holler",
 "song": "Prince of peace",
 "picture": "Tugalo Holler.png",
 "mix": "princeofpeace.mp3",
 "before": "princeofpeace_before.mp3",
 "facebook": "https://www.facebook.com/tugaloholler/",
 "leadVocal": "f",
 "table": "portfolio"
}}

You can avoid this by either listing your field names or by using bucketname.*, so that’d be something like
"SELECT artist,song,picture,mix,before,facebook,leadVocal FROM ", getBucket().Name(), " where table = 'portfolio'"
or
"SELECT ", getBucket().Name(), ".* FROM ", getBucket().Name(), " where table = 'portfolio'"

There’s a bit more info at https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/selectclause.html. Hope that helps.

1 Like

Hi Charles,

Thank you very much for your help. It’s working just fine.

Have a good day.

Steeve