Scanning a JSON object

[codecandy2015]

I am trying to store and retrive JSON objects where I do not know the structure of the json before hand. While creating the table I did the following,

CREATE TABLE IF NOT EXISTS ks.sensordata ( sensorID text, topic text, post_date text, values text, PRIMARY KEY (sensorID, post_date) ) WITH CLUSTERING ORDER BY (post_date DESC);

Here I am storing my data in the sensor values which is a json object under the comumn values. Works fine, able to see the data in the database colum. all good.

The issue is when I am trying to read this data, my select statement looks like:-

scanner := session.Query(`SELECT json values FROM ks.sensordata WHERE sensorID = ?`, _sensorID ).WithContext(ctx).Iter().Scanner()

for scanner.Next() {
		err = scanner.Scan(&values)
		
		if err != nil {
			log.Fatal(err)
			fmt.Println("Scanner ERROR :", err)
		}  
		fmt.Println("Found as :", values)
 }

I am not able to see the JSON object in the above statement. It is just showing me a an array of integers. Ideally I want to create an array of JSON objects as I want to get the last 20. Bit kind of lost here. Any help would be a kick start for me.

Please answer these questions before submitting your issue. Thanks!

What version of Scalla are you using?

5.4.0

What version of Gocql are you using?

1.2.0

What version of Go are you using?

1.21.5

What did you do?

above is what I did

What did you expect to see?

[{"sensorid". "1223", "values": {"temp": "33" }},{{"sensorid". "1223", "values": {"temp": "33" } }}]

What did you see instead?

{%!s()}{"values" : &"7b2276616c756573223a20227b5c725c6e202020205c725c6e20202020202020205c224143545c223a205c224c544520436174204d315c222c5c725c6e20202020202020205c22414d504c315c223a205c223133373839313135313830302e30305c222c5c725c6e20202020202020205c22414d504c325c223a205c22302e31385c222c5c725c6e20202020202020205c22414d504c335c223a205c22302e33355c222c5c725c6

[martin-sucha]

SELECT JSON encodes multiple database columns as json, so if the values text is json, it would be encoded twice. Try using a plain SELECT values ... instead.

Also your snippet does not show the type of the values Go variable, the unmarshaling depends on the target type.

[codecandy2015]

Hi Martin,
Thank you so much for getting back on this. I did experiment this further and you are right - adding JSON is getting json encoded twice. The following code is now working for me.

`

var sensorData []json.RawMessage  
    var values json.RawMessage
scanner := session.Query(`SELECT values FROM ks.sensordata WHERE sensorid = ?`, sensorID).WithContext(ctx).Iter().Scanner()
	 
// fmt.Println("Data fetched--")	

for scanner.Next() {
	err = scanner.Scan(&values) 
	if err != nil {
		log.Fatal(err)
		fmt.Println("Scanner ERROR :", err)

		w.WriteHeader(http.StatusInternalServerError)
		w.Write([]byte(fmt.Sprintf("{%s}", err)))

	}  
	// fmt.Println(values)    
	sensorData = append(sensorData, values)   
}

jsonResult, _ := json.Marshal(sensorData) 
w.Header().Set("Content-Type", "application/json")
w.Write([]byte(fmt.Sprintf("{\"values\" : %s}", jsonResult))) `

The above is selecting data from the table getting the values as JSON and sending it back to the caller via http write. If this is FOR LOOP is performant enough - I dont know, but working. if there are any tips - do let me know. Else we can close this. Thanks