Gorm: Selecting JSONB with lib/pq

[devchakraborty]

Hi,

I'm experiencing issues with reading a JSONB column in Postgres. My test environment has Go1.4, OS X 10.10.2, PostgreSQL 9.4.0.

I haven't dived too deep into what might be causing this inside gorm, but it appears that nearby data in the PostgreSQL TCP session is being mixed into the JSON byte array. I wasn't able to reproduce this by using lib/pq on its own, which is why I'm posting here first.

Here's a program to reproduce (set CONN_STRING env var to the appropriate lib/pq connection string):

package main

import (
    _ "github.com/lib/pq"
    "github.com/jinzhu/gorm"
    "fmt"
    "encoding/json"
    "os"
)

func main() {

    // Setup Postgres - set CONN_STRING to connect to an empty database
    db, err := gorm.Open("postgres", os.Getenv("CONN_STRING"))
    if err != nil {
        panic(err)
    }

    // db.LogMode(true)

    // Create Table
    db.Exec(`CREATE TABLE IF NOT EXISTS classrooms(
                id TEXT PRIMARY KEY,
                created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 
                archived TIMESTAMP WITH TIME ZONE,
                name TEXT NOT NULL DEFAULT 'Name',
                description TEXT NOT NULL DEFAULT '',
                id_number TEXT,
                course TEXT,
                section TEXT,
                conversion_table JSONB NOT NULL DEFAULT '[]'::JSONB,
                state JSONB NOT NULL DEFAULT '{}'::JSONB,
                creator_id TEXT,
                school_id TEXT NOT NULL,
                semester_id TEXT
                )`)

    // Truncate
    db.Exec("DELETE FROM classrooms")

    // JSON to insert
    STATE := `{"uses-kica": false, "hide-assessments-intro": true, "most-recent-grade-skew": 1.5}`

    // Insert a row
    db.Exec(`insert into classrooms(id, created, updated, archived, name, description, id_number, course, section, conversion_table, state, creator_id, school_id, semester_id) values ('2GdR7e', '2014-10-06 19:36:35.390563-04', '2015-01-26 13:08:15.620348-05', '2015-06-03 10:08:14.957953-04', '10th Grade Science', '1st Period', '', '', '', '[{"key": "MI", "val": null}]', '`+STATE+`', '', '45BbbG', '');`)

    // Select the row
    row := db.Table("classrooms").Where("id = ?", "2GdR7e").Select("state").Row()

    // Get the JSON back
    var state json.RawMessage

    row.Scan(&state)

    // Compare inserted JSON to what we got back
    if string(state) == STATE {
        fmt.Println("SUCCESS: Selected JSON == inserted JSON")
    } else {
        fmt.Println("FAILED: Selected JSON != inserted JSON")
        fmt.Println("Inserted: " + STATE)
        fmt.Println("Selected: " + string(state))
    }
}

My output:

FAILED: Selected JSON != inserted JSON
Inserted: {"uses-kica": false, "hide-assessments-intro": true, "most-recent-grade-skew": 1.5}
Selected: 00000Mduration: 0.048 ms  execute <unnamed>: SELECT  state FROM "classrooms"  WHER

[ansel1]

Hmm...I ran into a similar problem defining the Scan method on a JSON type I created... The Scan function is passed a byte slice, and is expected to marshal that into another variable. I found that I needed to make a copy of the incoming bytes, or things would get corrupted. The incoming byte slice must be re-used. I'm not clear on why you are encountering that problem though. I'm not sure how row.Scan() is even successfully scanning into a json.RawMessage, since json.RawMessage doesn't implement the Scanner interface...

Anyway, here's my JSONB type. It works for me. One thing that might be a bit non-standard about mine is that it considers itself NULL if its empty. That was what I needed for my app.

// Postgres' JSONB type. It's a byte array of already encoded JSON (like json.RawMessage)
// which also saves itself correctly to PG's jsonb type.  It would probably also work on
// PG json types.
type JSONB []byte

func (j JSONB) Value() (driver.Value, error) {
    if j.IsNull() {
        //      log.Trace("returning null")
        return nil, nil
    }
    return string(j), nil
}

func (j *JSONB) Scan(value interface{}) error {
    if value == nil {
        *j = nil
        return nil
    }
    s, ok := value.([]byte)
    if !ok {
        errors.New("Scan source was not string")
    }
    // I think I need to make a copy of the bytes.
    // It seems the byte slice passed in is re-used
    *j = append((*j)[0:0], s...)

    return nil
}

// MarshalJSON returns *m as the JSON encoding of m.
func (m JSONB) MarshalJSON() ([]byte, error) {
    if m == nil {
        return []byte("null"), nil
    }
    return m, nil
}

// UnmarshalJSON sets *m to a copy of data.
func (m *JSONB) UnmarshalJSON(data []byte) error {
    if m == nil {
        return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
    }
    *m = append((*m)[0:0], data...)
    return nil
}

func (j JSONB) IsNull() bool {
    return len(j) == 0 || string(j) == "null"
}

func (j JSONB) Equals(j1 JSONB) bool {
    return bytes.Equal([]byte(j), []byte(j1))
}

[jinzhu]

Hi @devchakraborty

I found you are just using default golang sql package functions and get this issue.

I tested in gorm with this https://gist.github.com/jinzhu/4fd1ab15eff169468eae

seems it works for me.