Why is JSONB returned as a string?

Question content

I have started an "instagram clone" backend to try to learn about golang, but I ran into a problem that I don't know how to solved problem.

I built a simple api using fiber to get some posts:

package server

import (
    "fmt"
    "instagram/internal/psql"
    queries "instagram/internal/sql"
    "net/http"

    "github.com/gofiber/fiber/v2"
)

type like struct {
    username string
    name     string
    picture  string
}

type post struct {
    id          string `json:"id"`
    description string `json:"description"`
    media       string `json:"media"`
    type        string `json:"type"`
    likes       string `json:"likes"`
    user_id     string `json:"user_id"`
    created_at  string `json:"created_at"`
    updated_at  string `json:"updated_at"`
}

func listposts(c *fiber.ctx) error {
    rows, err := psql.db().query(queries.getallposts)

    if err != nil {
        c.sendstatus(400)
        return nil
    }

    defer rows.close()
    var data []post

    for rows.next() {
        var post post
        err := rows.scan(&post.id, &post.description, &post.media, &post.type, &post.created_at, &post.updated_at, &post.likes, &post.user_id)
        if err != nil {
            fmt.print(err)
            c.sendstatus(400)
            return nil
        }

        data = append(data, post)
    }

    return c.status(http.statusok).json(data)
}

The problem is that likes properties are returned as strings:

[
    {
        "id": "...",
        "description": "...",
        "media": "...",
        "type": "...",
        "likes": "[]",
        "user_id": "...",
        "created_at": "...",
        "updated_at": "..."
    }
]

I tried some things like using json.marshal(data) and I also wrote a like structure but I couldn't make it work because when I put ## The following message appears when calling scan when the type of #likes is changed from string to []like

sql: scan error on column index 6, name "likes": unsupported scan, storing driver.value type []uint8 into type *[]server.likesql: scan error on column index 6, name "likes": unsupported scan, storing driver.value type []uint8 into type *[]server.like

I'm using postgresql database and I'm trying to use

jsonb columns for the first time, so likes are jsonb columns in the database.

My ideal payload would be this:

[
    {
        "id": "...",
        "description": "...",
        "media": "...",
        "type": "...",
        "likes": [],
        "user_id": "...",
        "created_at": "...",
        "updated_at": "..."
    }
]

Please note that

likes is now an array rather than a string. So does anyone know how to solve this problem?

Correct answer

You will most likely need to implement the interface of

sql.driver to "know" how to scan into your structure, probably something like

type Like struct {
    Username string
    Name     string
    picture  string
}

type Likes []Like

func (l Likes) Value() (driver.Value, error) {
    return json.Marshal(l)
}

// Make the Likes implement the sql.Scanner interface.
func (l *Likes) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }

    return json.Unmarshal(b, &l)
}

The above is the detailed content of Why is JSONB returned as a string?. For more information, please follow other related articles on the PHP Chinese website!