Postgres json to Go template directly?

I can get json directly from Postgresql by:

SELECT json_agg(hr)::text FROM hr

like this:

[map[json_agg:[
{“hr_id”:34,“hr_sign”:“JD”,“hr_job”:“20-0001”,“hr_code”:“PM”,“hr_subject”:“asdf”,“hr_qty”:0.50,“hr_ppu”:1000.00,“hr_sum”:500.00,“hr_date”:“2020-08-22”},

{“hr_id”:35,“hr_sign”:“JD”,“hr_job”:“20-0001”,“hr_code”:“PM”,“hr_subject”:“456”,“hr_qty”:5.25,“hr_ppu”:1000.00,“hr_sum”:5250.00,“hr_date”:“2020-08-22”}]]]

Is there a simple way to send this to a go template?

There is a solution in another thread solved by @Lutzhorn that I think could be a part of the solution.

I wonder now if there is a automagical way to strip this in the beginning

[map[json_agg:

and this in the end?

]]

Or if it is another way to fetch json from Postgresql and pour into a go html template?

Maybe this is not the best way to select JSON from PostgreSQL to be consumed by Go.

I’ve not tried it but this looks promising:

https://www.alexedwards.net/blog/using-postgresql-jsonb

Can you elaborate? What could be better?

I look at this as creating a json output from a stored procedure. Faster on the server and hopefully less iterating in Go. You still have the RDMS functionality as you do not store values in a json column.

What PostgreSQL type does the column hr have?

{“hr_id”:34,“hr_sign”:“JD”,“hr_job”:“20-0001”,“hr_code”:“PM”,“hr_subject”:“asdf”,“hr_qty”:0.50,“hr_ppu”:1000.00,“hr_sum”:500.00,“hr_date”:“2020-08-22”}

In this case int64, varchar, numeric, date columns. It can be all types of columns.
Except json column :slight_smile:

I still don’t understand the structure of the table.

This

SELECT json_agg(hr)::text

indicates uses hr. What is the PostgreSQL type of hr?

I am new to json in Postgresql, but I assume this is about the sames as *.

Like "create json aggregare of all fields in table hr"

You can select ceratain columns with:

select json_agg((hr_id,hr_date)) from hr

Don’t you know the columns of this table? Why aggregate them to JSON which you then have to parse in Go again? Why not directly select the columns into a matching Go struct without the JSON roundtrip?

Because of this: Numeric values shown as [53 48 48 46 48 48] in html table

Is there a workaround for this, I should be glad.

Here is how the data is fetched from Postgresql (numeric values shows as byte):

func get(query string) interface{} {
	if len(query) > 0 {
		var list []map[string]interface{}
		rows, err := db.Queryx(query)

		if err != nil {
			log("get error" + err.Error())
		}

		defer rows.Close()

		for rows.Next() {
			row := make(map[string]interface{})
			err = rows.MapScan(row)
			if err != nil {
				log("next " + err.Error())
			}
			list = append(list, row)
		}

		rows.Close()
		
		return (list)
	}
	return nil
}

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.