How to handle nullable Postgres JSONB data and parse it as JSON

Question

DB Records

---------------------------------------------------------
| id | test_json                                        |
---------------------------------------------------------
| 1  | NULL                                             |
---------------------------------------------------------
| 2  | { "firstName": "Hello", "lastName": "World" }    |
---------------------------------------------------------

I have JSONB column in postgres which can be NULL. I want to read this records DB in golang and send it to the client.

I'm getting below error on SQL scan:

sql: Scan error on column index 2, name "test_json": unsupported Scan, storing driver.Value type []uint8 into type *models.TestJSONNullable
exit status 1

I'm using echo web server.

package models

import (
    "fmt"
    "github.com/lib/pq"
    "encoding/json"
)

type TestJson struct {
    First_name *string `json:"firstName"`
    Last_name *string `json:"lastName"`
}

type TestJSONNullable struct {
  Valid bool
}

func (i *TestJSONNullable) UnmarshalJSON(data []byte) error {
  if string(data) == "null" {
    i.Valid = false
    return nil
  }

  // The key isn't set to null
  var temp *TestJson
  if err := json.Unmarshal(data, &temp); err != nil {
    return err
  }
  i.Valid = true
  return nil
}

type Test01 struct {
    Id string `json:"id"`
    Test_json *TestJSONNullable `json:"testJson"`
}


func (db *DB) TestRecords () ([]*Test01, error)  {
    rows, err := db.Query("SELECT id, test_json FROM table_1 where success = true")

    if err != nil {
        log.Fatal(err)
        return nil, err
    }

    defer rows.Close()

    recs := []*Test01{}

    for rows.Next() {
        r := new(Test01)

        err := rows.Scan(&r.Id, &r.Test_json)

        if err != nil {
            log.Fatal(err)
            return nil, err
        }

        recs = append(recs, r)
    }

    if err = rows.Err(); err != nil {
        log.Fatal(err)
        return nil, err
    }

    return recs, nil
}

Soroush · Accepted Answer · 2020-10-26 13:48:09Z

Here's another solution: You can implement a nullable type for raw JSON data, similar to sql.NullString, and use it as a scan destination. In this scenario, you will first check whether the value is null, and then unmarshal it only if it's not null. For instance, the NullRawMessage type from github.com/soroushj/sqlt is a nullable json.RawMessage which can be used for this purpose.

Here's an example:

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq"
    "github.com/soroushj/sqlt"
)

func main() {
    db, err := sql.Open("postgres", "dbname=dbname user=user password=password sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    row := db.QueryRow(`SELECT test_json FROM my_table WHERE id = $1`, 1)
    testJSON := sqlt.NullRawMessage{}
    err = row.Scan(&testJSON)
    if err != nil {
        log.Fatal(err)
    }
    if testJSON.Valid {
        // test_json is not null
        // Unmarshal testJSON.RawMessage
    } else {
        // test_json is null
    }
}

sravis · Accepted Answer · 2019-12-09 13:24:58Z

0

After doing some research i found the solution.

type TestJSONMap map[string]interface{}

func (t TestJSONMap) Value() (driver.Value, error) {
    j, err := json.Marshal(t)
    return j, err
}

func (p *TestJSONMap) Scan(val interface{}) error {
    value, ok := val.([]byte)
    if !ok {
        return errors.New("Type assertion .([]byte) failed.")
    }

    var i interface{}
    err := json.Unmarshal(value, &i)
    if err != nil {
        return err
    }

    *p, ok = i.(map[string]interface{})
    if !ok {
        return errors.New("Type assertion .(map[string]interface{}) failed.")
    }

    return nil
}

type Test01 struct {
    Id string `json:"id"`
    Test_json *TestJSONMap `json:"testJson"`
}

Got help from https://coussej.github.io/2016/02/16/Handling-JSONB-in-Go-Structs/

answered Dec 9, 2019 at 13:24

sravis

3,6808 gold badges41 silver badges76 bronze badges

1 Comment

user5728991 Over a year ago

The information in the answer itself is not very informative. Add a short description of what solution is. Mentioning the interfaces will be a big improvement.

arshpreet · Accepted Answer · 2019-12-09 10:32:55Z

-1

Instead of using struct to store values, You can use map.

type TestJson struct {
    First_name *string `json:"firstName"`
    Last_name *string `json:"lastName"`
}

You can use interface as

var TestJson interface{}
err := json.Unmarshal(b, &TestJson)

On the other side you can also use Dynamic creation of structs as well. Something like

m := map[string]interface{}{
    "key": "value",
}

And rather than having TestJSONNullable it would be better to have Switch case while saving data.

switch v := TestJson.(type) {
case int:

case float64:

case string:

default:
    // i isn't one of the types above
}

Look into this for more details--> https://godoc.org/encoding/json#Unmarshal

answered Dec 9, 2019 at 10:32

arshpreet

7392 gold badges11 silver badges28 bronze badges

5 Comments

mkopriva Over a year ago

The question is about retrieving a possibly NULL json value from a db column, not about how to unmarshal json. Also your recommendation to use maps instead of structs is backward in my experience, if possible/practical it is always better to use structs instead of maps.

arshpreet Over a year ago

OK,@mkopriva thanks for your intention, There does not seem to be any rule related to Structs over maps, If there is could you please pass more information on that? Although if you look into code, it make sense to use MAP with Switch case.

mkopriva Over a year ago

There is no rule, that's why I've written "in my experience". But why would you need a rule, just look at your code and it becomes clear that it is sub-optimal, because you've introduced maps you now have to do a type switch to find out the type of a member of the map. How would that be better than having a concrete struct type with fields of concrete types?

mkopriva Over a year ago

"Although if you look into code, it make sense to use MAP with Switch case." No it doesn't.

mkopriva Over a year ago

Go is a statically typed language, using interface{} subverts that very much desired feature. While interface{} can be very useful in situations where it makes sense, in most other situations if it can be avoided it should be.

Collectives™ on Stack Overflow

How to handle nullable Postgres JSONB data and parse it as JSON

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related