How to create SQL query with multiple JSON parameters in golang application?

As you can see in my Golang application I have an array called layers.

type Details struct {
    Total int `json:"total"`
    Gender string `json:"gender"`
}

type Layer struct {
    ID int `json:"id"`
    City string `json:"city"`
    Details []Details `json:"details"`
}

layers := []Layer{
    {
        ID: 107509018555,
        City: "London",
        Details: []Details{
            {
                Total: 158,
                Gender: "Male",
            },
            {
                Total: 689,
                Gender: "Female",
            },
        },
    },
    {
        ID: 108509018556,
        City: "New York",
        Details: []Details{
            {
                Total: 756,
                Gender: "Male",
            },
            {
                Total: 356,
                Gender: "Female",
            },
        },
    },
}

I want to insert data of that array to the table of the PostgreSQL database. My question is how to create such SQL query in the application?

QUERY:

INSERT INTO layers (ID, CITY, DETAILS) VALUES
(107509018555, 'London', '[{"total":158,"gender":"Male"},{"total":689,"gender":"Female"}]'::json),
(108509018556, 'New York', '[{"total":756,"gender":"Male"},{"total":356,"gender":"Female"}]':json);

Answer

Because I cannot comment, I assume that:

1. You’re using golang’s database/sql or similar package.
2. In your database, details column has type JSONB

A simple approach is to loop your slice layers and build query string to this:

"INSERT INTO layers (id,city,details) VALUES ($1,$2,$3), ($4,$5,$6)"

For id and city, you can pass the params easily, however you need to pass JSON bytes for details. Which means, you’ll need to marshal Details struct to JSON bytes for insert/update and unmarshal ‘details’ result to struct when SELECT

You will need to:

1. Define new struct that encapsulates the slice of Detail (we’ll call it Details) then the Details should implement these interfaces.
2. Implements driver.Valuer interface to convert Details to JSON byte slice that can be understood by the database
3. Implements sql.Scanner interface to unmarshal JSON byte slice from database to your struct

The code should look like this:

type Detail struct {
    Total  int    `json:"total"`
    Gender string `json:"gender"`
}

// this will implement driver.Valuer and sql.Scanner
type Details []Detail

// so that the database can understand your value, useful for INSERT/UPDATE
func (d Details) Value() (driver.Value, error) {
    return json.Marshal(d)
}

// so that the database can convert db value to your struct, useful for SELECT
func (d *Details) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed for scanning Details")
    }

    return json.Unmarshal(b, &d)
}

The complete code:

package main

import (
    "database/sql"
    "database/sql/driver"
    "encoding/json"
    "errors"
    "fmt"
    "log"
    "strings"

    _ "github.com/lib/pq"
)

type Layer struct {
    ID      int     `json:"id"`
    City    string  `json:"city"`
    Details Details `json:"details"`
}

// this will implement driver.Valuer and sql.Scanner
type Details []Detail

// so that the database can understand your value, useful for INSERT/UPDATE
func (d Details) Value() (driver.Value, error) {
    return json.Marshal(d)
}

// so that the database can convert db value to your struct, useful for SELECT
func (d *Details) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed for scanning Details")
    }

    return json.Unmarshal(b, &d)
}

type Detail struct {
    Total  int    `json:"total"`
    Gender string `json:"gender"`
}

func main() {
    db, err := sql.Open("postgres", "postgres://user:pass@host:port/db?sslmode=disable")
    exitIfError(err)

    query, params := prepareQuery([]Layer{
        {
            ID:      107509018555,
            City:    "London",
            Details: []Detail{{Total: 158, Gender: "Male"}, {Total: 689, Gender: "Female"}},
        },
        {
            ID:      108509018556,
            City:    "New York",
            Details: []Detail{{Total: 756, Gender: "Male"}, {Total: 356, Gender: "Female"}},
        },
    })

    log.Println(query) 
    // INSERT INTO layers (id, city, details) VALUES ($1, $2, $3),($4, $5, $6)
    log.Println(params) 
    // [107509018555 London [{158 Male} {689 Female}] 108509018556 New York [{756 Male} {356 Female}]]

    result, err := db.Exec(query, params...)
    exitIfError(err)


    rows, _ := result.RowsAffected()
    log.Println(rows) // 2 rows inserted
}

func exitIfError(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

func prepareQuery(layers []Layer) (string, []interface{}) {
    query := "INSERT INTO layers (id, city, details) VALUES "
    params := []interface{}{}
    x := 1

    for _, layer := range layers {
        query += fmt.Sprintf("($%d, $%d, $%d),", x, x+1, x+2)
        params = append(params, layer.ID, layer.City, layer.Details)
        x += 3
    }

    query = strings.TrimSuffix(query, ",")
    return query, params
}

Reference:

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

https://golang.org/pkg/database/sql/