Golang: insert nested structure model into Postgres

i have this model data structure:

type Order struct {
	OrderEntry OrderEntry `db:"order"`
}

type OrderEntry struct {
	Order_uid          string
	Track_number       string
	Entry              string
	Delivery           Delivery
	Payment            Payment
	Items              []Item
	Locale             string
	Internal_signature string
	Customer_id        string
	Delivery_service   string
	Shardkey           string
	Sm_id              int64
	Date_created       string
	Oof_shard          string
}

type Delivery struct {
	...
}

type Payment struct {
	...
}

type Item struct {
	...
}

And i have table

CREATE TABLE "order"
(
    "order" jsonb NOT NULL
);

How do i insert Order object to this psq table? I use sqlx and for this code im getting error

func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
	tx := r.db.MustBegin()
	tx.MustExec("INSERT INTO order (order) VALUES ($1)", order.OrderEntry)
	err := tx.Commit()

	if err != nil {
		return err
	}

	return nil
}

panic: sql: converting argument $1 type: unsupported type L0.OrderEntry, a struct

How to properly fix this? Thanks

There are a couple of changes you need to make here.

1. You need to marshal to JSON the struct you want to store. You can do that with:

orderB, err := json.Marshal(order.OrderEntry)
if err != nil {
    // handle err
}

tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderB)

This will now store your struct as a JSON byte-array in the table.

2. In order to generate JSON from your struct correctly, you need to add JSON struct tags to each field. e.g.:

type OrderEntry struct {
    Order_uid          string `json:"order_uid"`
    Track_number       string `json:"track_number"`
    Entry              string `json:"entry"`
    Delivery           Delivery `json:"delivery"`
    Payment            Payment `json:"payment"`
    Items              []Item `json:"items"`
    Locale             string `json:"locale"`
    Internal_signature string `json:"internal_signature"`
    Customer_id        string `json:"customer_id"`
    Delivery_service   string `json:"delivery_service"`
    Shardkey           string `json:"shard_key"`
    Sm_id              int64 `json:"sm_id"`
    Date_created       string `json:"date_created"`
    Oof_shard          string `json:"oof_shard"`
}

You can name these however you like, snake-case being the common format. These tags provide the names for the JSON fields to the parser.

Without these fields, you'll end up with empty JSON, and you won't be able to retrieve the data.

The INSERT statement's unsupported type "L0.OrderEntry" is the cause of the issue you're experiencing. "L0.OrderEntry" cannot be directly entered into the JSONB column since it is a nested struct within "L0.Order". Instead, before entering the OrderEntry object into the table, you must marshal it into a JSON representation.

You can change your code as below to resolve the error.

    import "encoding/json"

    func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()

    orderJSON, error := json.Marshal(order.OrderEntry)
    if error != nil {
      return error
    }

    tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderJSON)
    error = tx.Commit()

    if error != nil {
     return error
    }

     return nil
    }

The order.OrderEntry object is marshalled into JSON in the modified code using the "json.Marshal" method. The generated JSON byte slice (orderJSON) is then provided to the INSERT statement as the value.

You may make sure the data is in the correct format for insertion into the JSONB column by marshalling the nested struct into JSON.