Use JSON field with golang sqlx driver

sqlx_json.go

package main

import (
	"encoding/json"
	"errors"
	"database/sql/driver"
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

var schema = `
CREATE TABLE person (
    first_name text,
    last_name text,
    contact JSON
);`

type PersonContact map[string]interface{};

func (pc *PersonContact) Scan(val interface{}) error {
	switch v := val.(type){
	case []byte:
		json.Unmarshal(v, &pc)
		return nil
	case string:
		json.Unmarshal([]byte(v), &pc)
		return nil	
	default:
		return errors.New(fmt.Sprintf("Unsupported type: %T", v))
	}
}
func (pc *PersonContact) Value() (driver.Value, error) {
	return json.Marshal(pc)
}

 
type Person struct {
	FirstName string            `db:"first_name"`
	LastName  string            `db:"last_name"`
	Contact   PersonContact `db:"contact"`
}

func main() {
	db, err := sqlx.Connect("postgres", "user=test dbname=test password=test")
	if err != nil {
		log.Fatalln(err)
	}
	defer db.Close()

	// exec the schema or fail
	db.MustExec("DROP TABLE IF EXISTS person;")
	db.MustExec(schema)

	var person Person
	contact := map[string]string{"email": "jmoiron@jmoiron.net"}
	contact_json, _ := json.Marshal(contact)

	db.MustExec("INSERT INTO person (first_name, last_name, contact) VALUES ($1, $2, $3)", "Jason", "Moiron", contact_json)

	row := db.QueryRowx("SELECT first_name, last_name, contact FROM person LIMIT 1")
	err = row.StructScan(&person)
	if err != nil {
		panic(err)
	}
	fmt.Printf("=> %v\n", person)
}

fix valuer func (pc PersonContact) Value() (driver.Value, error) { return json.Marshal(&pc) }

How can I use it for json array

Thanks. This is great.

This is awesome, thank you! Tried to add Scan/Value to my json field and it works with Select, just like this:

items:= []Item{}
err = db.Select(&items, "SELECT id, name, json_field::text,  FROM items ORDER BY id ASC")

what's the purpose of

func (pc *PersonContact) Value() (driver.Value, error) {
	return json.Marshal(pc)
}

when it's marshalled manually anyway?

	contact := map[string]string{"email": "jmoiron@jmoiron.net"}
	contact_json, _ := json.Marshal(contact)

After version 1.18, generic types can be used.

type JsonColumn[T any] struct {
	v *T
}

func (j *JsonColumn[T]) Scan(src any) error {
	if src == nil {
		j.v = nil
		return nil
	}
	j.v = new(T)
	return json.Unmarshal(src.([]byte), j.v)
}

func (j *JsonColumn[T]) Value() (driver.Value, error) {
	raw, err := json.Marshal(j.v)
	return raw, err
}

func (j *JsonColumn[T]) Get() *T {
	return j.v
}

type Product struct {
	DescriptionI18n  JsonColumn[I18n]     `db:"description_i18n"` // struct
	Depends        JsonColumn[[]string] `db:"depends"`           // array
}