How to store and retrieve JSONB in a postgres DB using golang

In this article we are going to store and fetch a jsonb object in a postgres database using golang. We will be using the jinzhu’s gorm library to connect to the database. We will also be looking at the various jsonb methods provided by postgres.

We will be creating a struct in golang that contains a field of type jsonb

type Book struct {
 ID                string
 Name              string
 CreationTimestamp int64
 Author            string
 Details           postgres.Jsonb `gorm:"type:jsonb;default:'{}'"`
}

Connect to the Database

We create a books table if it doesn’t exist and create an entry in the table.

package main
import (
"encoding/json"
 "net/url"
"fmt"
"github.com/jinzhu/gorm"
 "github.com/jinzhu/gorm/dialects/postgres"
 _ "github.com/lib/pq"
)
func main() {
//Open a database connection
 dbString := fmt.Sprintf("postgres://%s:%s@%s:%s/%s?sslmode=disable", User, Password, Host, Port, Dbname)
 dbURL, err := url.Parse(dbString)
 db, err := gorm.Open(dbURL.Scheme, dbURL.String())
 defer db.DB().Close()
 if err != nil {
  fmt.Println("Could not connect to DB " + fmt.Sprint(err))
 }
//Create table if not exists
if db.HasTable(&Book{}) == false {
  db.CreateTable(&Book{})
 }
//Create an entry in the Books table
 db.Create(&Book{ID: "13", Name: "Am I Normal Yet?",             CreationTimestamp: 161035659946, Author: "Holly Bourne", Details: postgres.Jsonb{json.RawMessage(`{"rating":"4.2/5","genres":"Fiction, Humour","contents":{"title":"The Spinster Club Series","versions":[{"id":1,"types":["kindle","audio"]},{"id":2,"types":["kindle","paperback"]}]}}`)}})
}

The json we are using in the Details object is:

{
   "rating":"4.2/5",
   "genres":"Fiction, Humour",
   "contents":{
      "title":"The Spinster Club Series",
      "versions":[
         {
            "id":1,
            "types":[
               "kindle",
               "audio"
            ]
         },
         {
            "id":2,
            "types":[
               "kindle",
               "paperback"
            ]
         }
      ]
   }
}

Now say we we want to query books that have a particular type: paperback, we can do it in the following way

package main
import (
"log"
 "net/url"
"fmt"
"github.com/jinzhu/gorm"
 _ "github.com/lib/pq"
)
func main() {
// Open a database connection
dbString := fmt.Sprintf("postgres://%s:%s@%s:%s/%s?sslmode=disable",
  User, Password, Host, Port, Dbname)
 dbURL, err := url.Parse(dbString)
 db, err := gorm.Open(dbURL.Scheme, dbURL.String())
 defer db.DB().Close()
 if err != nil {
  fmt.Println("Could not connect to DB " + fmt.Sprint(err))
 }
// Query to get books based on type
 rows, err := db.DB().Query(`select * from books, 
 jsonb_array_elements(details->'contents'->'versions') versions,
 jsonb_array_elements(versions->'types') types
  where types->>'type' = 'paperback'`)
//Iterate through the rows from result
 columns, _ := rows.Columns()
 count := len(columns)
 values := make([]interface{}, count)
 valuePtrs := make([]interface{}, count)
 defer rows.Close()
 for rows.Next() {
  for i := range columns {
   valuePtrs[i] = &values[i]
  }
  if err = rows.Scan(valuePtrs...); err != nil {
   log.Fatal(err)
  }
  for i, col := range columns {
   val := values[i]
   b, ok := val.([]byte)
   var v interface{}
   if ok {
    v = string(b)
   } else {
    v = val
   }
   //Print column name and value
   fmt.Print(col)
   fmt.Println(": ", v)
  }
  db.Close()

 }
}

The output looks something like this

id:  13
name:  Am I Normal Yet?
creation_timestamp:  161035659946
author:  Holly Bourne
details:  {"genres": "Fiction, Humour", "rating": "4.2/5", "contents": {"title": "The Spinster Club Series", "versions": [{"id": 1, "types": [{"id": 1, "type": "kindle"}, {"id": 2, "type": "audio"}]}, {"id": 2, "types": [{"id": 1, "type": "kindle"}, {"id": 1, "type": "paperback"}]}]}}
value:  {"id": 2, "types": [{"id": 1, "type": "kindle"}, {"id": 1, "type": "paperback"}]}
value:  {"id": 1, "type": "paperback"}

Notice we have 2 additional columns in the result. They correspond to the ‘versions’ and ‘types’ columns we had stored temporarily while writing the query.