Sqlx: Using JSON type in Postgres

[resal81]

Hi,

I'm trying to use JSON data type in Postgres. It works properly and the data is correctly inserted to the database. However when selecting multiple rows from the database, all the JSON data belongs to the last row. For example, the output of the following code:

package main

import (
    // "database/sql/driver"
    "encoding/json"
    "github.com/jmoiron/sqlx"
    "github.com/jmoiron/sqlx/types"
    _ "github.com/lib/pq"
    "log"
)

var DB *sqlx.DB

/* ----------------------------------------------------- */

type Address struct {
    Home string
    Work string
}

type Person struct {
    Id      int             `db:"id"`
    Address json.RawMessage `db:"address"`
}

func (p *Person) CreateTable() {
    cmd := `CREATE TABLE people (
        id   SERIAL PRIMARY KEY,
        address  JSONB
    )
    `
    DB.MustExec(cmd)
}

func (p *Person) DropTable() {
    cmd := `DROP TABLE IF EXISTS people`
    DB.MustExec(cmd)
}

/* ----------------------------------------------------- */

func init() {
    db, err := sqlx.Connect("postgres", "user=Reza dbname=sample sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    if err = db.Ping(); err != nil {
        log.Fatal(err)
    }

    DB = db
}

/* ----------------------------------------------------- */

func main() {
    p := Person{}
    p.DropTable()
    p.CreateTable()

    addresses := []Address{
        {"11 Home St", "11 Work St"},
        {"12 Home St", "12 Work St"},
        {"13 Home St", "13 Work St"},
    }

    // insert to the db
    tx := DB.MustBegin()
    for _, a := range addresses {
        b, err := json.Marshal(a)
        if err != nil {
            log.Fatal(err)
        }

        j := types.JsonText(string(b))

        v, err := j.Value()
        if err != nil {
            log.Fatal(err)
        }

        tx.MustExec("INSERT INTO people (address) VALUES ($1)", v)
    }
    tx.Commit()

    // get the data back
    people := []Person{}
    if err := DB.Select(&people, "SELECT id,address FROM people"); err != nil {
        log.Fatal(err)
    }

    for i, p := range people {
        log.Printf("%d => %v , %v", i, p.Id, string(p.Address))
    }
}

is:

2015/04/10 15:20:44 0 => 1 , {"Home": "13 Home St", "Work": "13 Work St"}
2015/04/10 15:20:44 1 => 2 , {"Home": "13 Home St", "Work": "13 Work St"}
2015/04/10 15:20:44 2 => 3 , {"Home": "13 Home St", "Work": "13 Work St"}

while in psql, the data is:

SELECT id,address FROM people;
id |                   address                    
----+----------------------------------------------
  1 | {"Home": "11 Home St", "Work": "11 Work St"}
  2 | {"Home": "12 Home St", "Work": "12 Work St"}
  3 | {"Home": "13 Home St", "Work": "13 Work St"}
(3 rows)

Am I missing something or is this a bug?

[jekirl]

Use types.JsonText instead of json.RawMessage:

type Address struct {
    Home string
    Work string
}

type Person struct {
    Id      int            `db:"id"`
    Address types.JsonText `db:"address"`
}

2015/04/15 16:30:13 0 => 1 , {"Home": "11 Home St", "Work": "11 Work St"}
2015/04/15 16:30:13 1 => 2 , {"Home": "12 Home St", "Work": "12 Work St"}
2015/04/15 16:30:13 2 => 3 , {"Home": "13 Home St", "Work": "13 Work St"}

[resal81]

Thanks. It solved the problem.

[mewben]

Anyone having issues using DB.Get return strange values? No problem with DB.Select though.

[brandonros]

How could this be used when not taking the time to create matching structs for every database table?

func dbQuery(db *sqlx.DB, query string, args ...interface{}) []map[string]interface {} {
    rows, err := db.Queryx(query)
    if err != nil {
        panic(err)
    }

    tableData := make([]map[string]interface{}, 0)

    for rows.Next() {
      entry := make(map[string]interface{})

      err := rows.MapScan(entry)

      if err != nil {
        panic(err)
      }

      tableData = append(tableData, entry)
    }

    return tableData
}

This works nicely, but garbles the returned JSON columns as base64-encoded.

[
    {
        "foo": "eyJmb28iOnRydWV9"
    }
]

[kaiyisg]

You should be using this instead:

type Address struct {
    Home string
    Work string
}

type Person struct {
    Id      int            `db:"id"`
    Address types.JSONText `db:"address"`
}

it's types.JSONText instead of types.JsonText