How to scan into nested structs with sqlx?

Issue

Let’s assume that I have two models,

    type Customer struct {
       Id      int     `json:"id" db:"id"`
       Name    string  `json:"name" db:"name"`
       Address Address `json:"adress"`
    }
    
    type Address struct {
       Street string `json:"street" db:"street"`
       City   string `json:"city" db:"city"`
    }

    // ...

    customer := models.Customer{}
    err := db.Get(&customer , `select * from users where id=$1 and name=$2`, id, name)

But this scan throws an error as: missing destination name street in *models.Customer

Am I doing something wrong? As you can see I already updated the db corresponding of the value. I doubled check so case sensitivity shouldn’t be a problem.
Or is it not possible using https://github.com/jmoiron/sqlx?

I can see it in the documentation but still couldn’t figure out how to solve it.
http://jmoiron.github.io/sqlx/#advancedScanning

The users table is declared as:

    CREATE TABLE `users` (
      `id` varchar(256) NOT NULL,
      `name` varchar(150) NOT NULL,
      `street` varchar(150) NOT NULL,
      `city` varchar(150) NOT NULL,
    )

Solution

The very link you posted gives you an hint about how to do this:

StructScan is deceptively sophisticated. It supports embedded structs, and assigns to fields using the same precedence rules that Go uses for embedded attribute and method access

So given your DB schema, you can simply embed Address into Customer:

type Customer struct {
   Id     int    `json:"id" db:"id"`
   Name   string `json:"name" db:"name"`
   Address
}

In your original code, Address was a field with its own db tag. This is not correct, and by the way your schema has no address column at all. (it appears you edited it out of your code snippet)

By embedding the struct into Customer instead, Address fields including tags are promoted into Customer and sqlx will be able to populate them from your query result.

Warning: embedding the field will also flatten the output of any JSON marshalling. It will become:

{
    "id": 1,
    "name": "foo",
    "street": "bar",
    "city": "baz"
}

If you want to place street and city into a JSON address object as based on your original struct tags, the easiest way is probably to remap the DB struct to your original type.

You could also scan the query result into a map[string]interface{} but then you have to be careful about how Postgres data types are represented in Go.

Answered By – blackgreen

Answer Checked By – Gilberto Lyons (GoLangFix Admin)

Leave a Reply

Your email address will not be published.