How to Manage PostgreSQL JSON data with Go(golang)

the simple way

This story is the continuation of How to Manage JSON data in PostgreSQL. If you didn’t read that story, I advice you to review it before continue to this one.

To manage data with go programming language, we’ll use a Object Relational Mapping (ORM) called “gorm”. The reason why I choose it among of others is just because gorm is simple and well documented.

So, having the following data;

planets data

Let’s create a structure in go:

We’re are going to use gorm with JSONMaptype. JSONMaptype is a special type defined as following:

This will help us to properly Serialize, and implement sql.Valuer and sql.Scanner

Defining the database connection

Assuming you’ve already a runningPostgreSQL instance, here is the possible configuration:

dsn := "host=localhost user=medium dbname=medium port=5432 sslmode=disable"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
if err != nil {
  panic(err)
}

Database Migration

After defining Planets structure and the database connection, let’s create the planets table in the database by running the following command:

db.AutoMigrate(&Planet{})

If everything OK,planets table should have the following structure:

\d planets

Data Management

Create

Time to create some data!

The data will consist in some well-known planets with different specifications:

For Earth

earthSpecs := map[string]interface{}{
  "alt_name": "gaia",
  "main_composition": []string{"nitrogen", "oxygen"},
  "others": map[string]interface{}{
    "is_nice": true,
    "orbital_period_days": 365,
  },
}
earth := Planet{
  Name: "Earth", 
  NumSatellites: 1, 
  Specs: JSONMap(earthSpecs)
}

For Mars

marsSpecs := map[string]interface{}{
  "main_composition": []string{
    "carbon dioxide",
    "argon",
    "nitrogen",
    "oxygen",
  },
  "is_red": true,
  "others": map[string]interface{}{
    "is_nice": "hum...",
    "orbital_period_days": 686,
  },
}
mars := Planet{
  Name: "Mars",
  NumSatellites: 2,
  Specs: JSONMap(marsSpecs)
}

For Jupiter

jupiterSpecs := map[string]interface{}{
  "main_composition": []string{"hydrogen", "helium"},
}
jupiter := Planet{
  Name: "Jupiter",
  NumSatellites: 79,
  Specs: JSONMap(jupiterSpecs)
}

All planets are now ready to be added to the database. Let’s have a list of them and add them at once, by use Create method of db

planets := []Planet{earth, mars, jupiter}
db.Create(&planets)

Read

After data creation our planets table should look like this:

select id,name,num_satellites, jsonb_pretty(specs) as specs from planets;

To query data, we’ll be using JSONQuery from datatypes module.

Getting all planets in the database:

func GetAllPlanets() {
  planets := []Planet{}
  db.Find(&planets)
  fmt.Printf("%+v", planets)
}

GetAllPlanets function returns a list of Planets, created on “Create” session. Here what this function should return:

[
  {
    ID:1 
    Name:Earth 
    NumSatellites:1 
    Specs:map[
      alt_name:gaia 
      main_composition:[nitrogen oxygen] 
      others:map[is_nice:true orbital_period_days:365]
    ]
  } 
  {
    ID:2 
    Name:Mars 
    NumSatellites:2 
    Specs:map[
      is_red:true 
      main_composition:[carbon dioxide argon nitrogen oxygen]
      others:map[is_nice:hum... orbital_period_days:686]
    ]
  } 
  {
    ID:3 
    Name:Jupiter 
    NumSatellites:79 
    Specs:map[main_composition:[hydrogen helium]]
  }
]

To deep query inside JSONB data, we’ll use HasKey and Equals methods

Checking all planets with alternative name

func GetPlanetsWithAltName() {
  planets := []Planet{}
  db.Find(&planets, datatypes.JSONQuery("specs")
    .HasKey("alt_name"))
  fmt.Printf("%+v", planets)
}

Let’s query for planets which alternative name is “gaia”:

func GetPlanetByAltName(alt_name string) {
  planets := []Planet{}
  db.First(&planets, datatypes.JSONQuery("specs")
    .Equals(alt_name, "alt_name"))
  fmt.Printf("%+v", planets)
}

Let’s query for planetd in which main composition is oxygen:

func GetPlanetsByComposition(composition string) {
  planets := []Planet{}
  db.Debug().Model(&Planet{})
    .Find(&planets, datatypes.JSONQuery("specs")
    .HasKey("main_composition", composition))
  fmt.Printf("%+v", planets)
}

Update

Let’s now add the key “type” with the value “cool!” for every planet in which the composition is “oxygen”.

To accomplish that, we’ll use Find and Savemethods of db

func UpdatePlanetByItsComposition(composition string) {
  planets := []Planet{}
  updatedPlanets := []Planet{}
  db.Debug().Model(&Planet{})
    .Find(&planets, datatypes.JSONQuery("specs")
    .HasKey("main_composition", composition))
for _, planet := range planets {
    specs := planet.Specs
    specs["type"] = "cool!"
    planet.Specs = JSONMap(specs)
    db.Save(&planet)
  }
  //double check
  db.Debug().Model(&Planet{})
    .Find(&updatedPlanets, datatypes.JSONQuery("specs")
    .HasKey("type", "cool!"))
  fmt.Printf("%+v", updatedPlanets)
}

Delete

Finally, Jupiter has no ‘oxygen’ as “main_composition”, so let’s delete it from the planetstable :D

func DeletePlanetWithoutAComposition(composition string) {
  planets := []Planet{}
  db.Not(datatypes.JSONQuery("specs")
    .HasKey("main_composition", composition))
    .Find(&planets)
for _, planet := range planets {
    db.Delete(&planet)
  }
//double check
  db.Find(&planets)
  fmt.Printf("%+v", planets)
}

That’s it!

Here is the complete example:

Next

I’ll present a simple way to manage the same data using a simple REST API. We’ll reuse all functions we use to Create, Read, Update and Delete data in the database in this story as actions to respond to the API calls.