There are cases when you want to store JSON blob directly instead of storing across multiiple tables. In this tutorial we will learn how to store JSON objects in Postgres and how we can implement that in Golang.

Understanding JSON data types in Postgres

PostgreSQL provides with two JSON-related data types that help us with this — JSON and JSONB. Both full-fill our usecase but why does Postgres provide us with 2 data types but there is a major difference in terms of efficiency.

JSON

This stores the exact copy of the JSON input. This means that you need to parse this whenever you need this. So Insertion is fast but we need to parse this everytime.

JSONB

Instead of storing the exact copy, JSONB stores a binary representation of the input. Now you will not have to reparse this since its already stored in decomposed binary format which makes it slower to insert but faster to query. But, that’s not all, there are more advantages which come with this.

• It supports indexing.
• Remove white space.
• Key order is not preserved.
• Duplicate keys are not allowed. JSON will store duplicate but JSONB will only store last value.
• It has existence operator that tests whether a string appears as an object key or array element at the top level of the jsonb value.

You can explore more details with examples in the official Postgres documentation.

---

Storing JSON object in Postgres

Now it’s time to show how we can store JSON object in Postgres. We will create a order where we have a shopping cart which contains list of items. This cart will be stored as JSON object. First, let’s create a order table.

CREATE TABLE order
(
    cart_id int PRIMARY KEY,
    user_id int NOT NULL,
    cart JSONB,
);

We will add 2 items to the cart table.’

INSERT INTO order (cart_id, user_id, cart) VALUES ('1', '123',
'{
  "items": [
    {
      "item_id": 111,
      "name": "T-shirt",
      "quantity": 1,
      "price": 250
    },
    {
      "item_id": 222,
      "name": "Trousers",
      "quantity": 1,
      "price": 600
    }
  ]
}');

You can verify the insert using

select cart from order;

---

Storing JSON in Postgres using Golang

Note: If you are not familiar with basic query on Postgres in Golang. I recommend to read through this article first.

First we need to create appropriate structs based on JSON keys.

type Order struct {
  CartID int `json:"cart_id"`
  UserID int `json:"user_id"`
  Cart Cart `json:"cart"`
}

type Cart struct {
  Items []CartItem `json:"items"`
}

type CartItem struct {
  ItemID       uuid.UUID `json:"id"`
  Name         string    `json:"name"`
  Quantity     int       `json:"quantity,omitempty"`
  Price        int       `json:"price,omitempty"`
}

Now we cannot directly parse the JSON struct since json.Marshal works with predefined datatypes. For JSON we need to create two functions -

Value() - This function will return JSON encoding for our Cart struct.

Scan() - To parse JSON from database to Go struct.

Here is a implementation for the two methods we need for our struct to work. You can simply replace Cart with any struct you are working with.

func (c Cart) Value() (driver.Value, error) {
  return json.Marshal(c)
}

func (c *Cart) Scan(value interface{}) error {
  b, ok := value.([]byte)
  if !ok {
    return errors.New("type assertion to []byte failed")
  }
  return json.Unmarshal(b, &c)
}

Now, your DB queries should work as expected. I am not covering how to make queries in Postgres in this article. I’ll leave that as a simple exercise to you.

---

I hope you learned something new. Feel free to suggest improvements ✔️

I share regular updates and resources on Twitter. Let’s connect!

Keep exploring 🔎 Keep learning 🚀