How to avoid Scan pair: <nil> *json.RawMessage errors on nullable JSON columns?

Question

Several weeks ago I started to learn go and trying to build a simple blogging application while learning the basics.

Currently I'm trying to fetch and persist blog posts using database/sql and github.com/lib/pq packages. I dont prefer utilizing 3rd party packages like sqlx or gorm without fully understand the native behaviours and basics of the go.

My Post struct is something like this:

type Post struct {
    Id        int
    Title     string
    Body      string
    Tags      json.RawMessage
}

When persisting posts, my save() function works without any problem:

func (p *Post) save() (int, error) {
    const query = `INSERT INTO post (title, body, tags) VALUES($1, $2, $3) RETURNING id`
    db := GetDB()
    var postid int
    err := db.QueryRow(query, p.Title, p.Body, p.Tags).Scan(&postid)
    return postid, err
}

and to read the latest posts, I wrote a small function:

func getLatestPosts(page int) (*[]Post, error) {
    const query = `SELECT id, title, body, tags FROM posts ORDER BY id DESC LIMIT 10`
    var items []Post
    db := GetDB()
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }

    for rows.Next() {
        var item Post
        if err := rows.Scan(&item.Id, &item.Title, &item.Body, &item.Tags); err != nil {        
            log.Fatal(err)
        }
        items = append(items, item)
    }
    return &items, err
}

This also works until hitting a post row which tags column is null and I'm getting the following error at this point:

2015/04/16 21:53:04 sql: Scan error on column index 4: unsupported driver -> Scan pair: -> *json.RawMessage

My question is, what is the proper way to handle nullable json columns while scanning the resultset? Is this error related with the lib/pq?

My schema is:

CREATE TABLE post (
    "id" int4 NOT NULL DEFAULT nextval('post_id_seq'::regclass),
    "title" varchar(255) NOT NULL COLLATE "default",
    "body" text COLLATE "default",
    "tags" json,
    PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
);

and here is an already persisted (not-null) tags field content:

 {
     "black-and-white" : "Black and White",
     "the-godfather" : "The Godfather"
 }

Any ideas?

Why are you using json.RawMessage? Why not just []byte?

Mr_Pink
– Mr_Pink

2015-04-17 19:06:59 +00:00
Commented Apr 17, 2015 at 19:06 — Mr_Pink
– Mr_Pink, Commented Apr 17, 2015 at 19:06

Dave C · Accepted Answer · 2015-04-17 21:52:44Z

2

TL;DR: change your struct to have Tags *json.RawMessage or use a temporary of that type.

Note you can search the Go source for the error message to get a better idea of what's going on behind the scenes if you're interested (the standard packages are mostly a good source of well written Go code).

I tested against a fresh PostgreSQL-9.4.1 server with the following table:

CREATE TABLE post (
        "id"    serial          PRIMARY KEY,
        "title" varchar(255)    NOT NULL,
        "body"  text,
        "tags"  json
);

(by the way, it's probably better to give the commands to re-create, or that where used to create the table rather than the form use gave which can't directly be used. Also, when I was familiar with PostgreSQL I recall it was exceedingly rare that a varchar column wasn't a mistake instead of just using text, possibly with a length constraint.)

Using that table with your struct type I got:

converting Exec argument #2's type: unsupported type json.RawMessage, a slice

On the insert. Changing to []byte(p.Tags) made that go away (but see below) and then querying worked as you had it.

I only got the same error you did on querying when I put a NULL value into the table. The solution to this was to change the struct field to Tags *json.RawMessage. I could then remove the cast I added on insert and and the query worked fine, either setting the field to nil or not as appropriate.

If you do this, don't forget to check if item.Tags is nil before using it. Alternatively, make the database field NOT NULL.

I'm not overly familiar with Go's database support to know if requiring a pointer to a slice to handle NULLs is reasonable; I'd have hoped not since Go already distinguishes between an empty slice and a nil slice.

Alternatively, you can leave your type as-is and use a temporary like this:

    var item post
    var tmp *json.RawMessage
    if err := rows.Scan(&item.Id, &item.Title, &item.Body, &tmp); err != nil {
        log.Fatal(err)
    }
    if tmp != nil {
        item.Tags = *tmp
    }

You'll likely experience a similar issue when you test with a NULL body. Either make the database column NOT NULL or use sql.NullString, either in your type or as a temporary as above (using the Valid field of NullString to see if you should copy the string).

A few other minor notes:

golint suggests using ID instead of Id.
You don't provide your GetDB implementation, I hope it's just getting a shared/global *sql.DB. You don't want to call sql.Open repeatedly.
In both of your functions you could use a pre-prepared statement (created once outside those functions). That might help if you call those functions often.
Your query tries to use the "posts" table/view instead of "post"; I'm guessing a typo while cut-n-pasting to the question?
Your getLatestPosts function reutrns *[]Post; don't do that. Just return []Post. You almost never want to use a pointer to a slice, and certainly not as a return type.

edited Apr 17, 2015 at 21:52

answered Apr 17, 2015 at 20:03

Dave C

7,9764 gold badges51 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave C Over a year ago

Interesting, I only have an old PostgreSQL server running that lacks the "json" type you appear to be using so I substituted "text" … but that works going directly into a json.RawMessage field so I suspect the above won't help and the problem may be related to the pq driver and "json" fields.

Dave C Over a year ago

Okay, with an updated postgresql with a json query works fine they way you had it but on insert I had to use []byte(item.Tag)

Dave C Over a year ago

Answer changed due to the above testing (the revision history has my old not-so-correct answer for anyone interested)

Collectives™ on Stack Overflow

How to avoid Scan pair: <nil> *json.RawMessage errors on nullable JSON columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related