Why JSONB is returning as a string? - Postgresql

WilliamCaetano
April 6, 2023
232 views
0 votes
2 Answers

I have started a "instagram clone" backend to try learn somethings about golang, but I’m having a problem that I do not know how to solve.

I have build a simple API with Fiber that 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 attribute is returning as a string:

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

I have tried some things, like use json.Marshal(data), I also wrote that Like struct, but I could not make it work, because when I change the type of Likes from string to []Like the following message appears when Scan is called

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 a postgresql database, and I’m trying to work with jsonb columns for the first time, so likes is in the database a jsonb column.

My ideal payload would be like:

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

Note that likes is now a array instead of a string. So anybody knows how to solve this?

Tags: go go-fiber json postgresql

Answers

Maybe that’s just because the database driver you use does not support the data type jsonb.

I have tested the package github.com/jackc/pgx/v5, and it can decode the jsonb field correctly. See the example below:

package main

import (
    "context"
    "log"

    "github.com/jackc/pgx/v5"
)

func main() {
    // remember to modify the connString below.
    connString := "postgres://username:password@localhost:5432/database_name"
    conn, err := pgx.Connect(context.Background(), connString)
    if err != nil {
        log.Fatalf("Unable to connect to database: %vn", err)
    }
    defer conn.Close(context.Background())

    type Like struct {
        Name    string
        Picture string
    }

    type Post struct {
        Id    string
        Likes []Like
    }

    var post Post
    err = conn.QueryRow(context.Background(), `select 'the_id', '[{"name":"a","picture":"a.png"},{"name":"b","picture":"b.png"}]'::jsonb`).Scan(&post.Id, &post.Likes)
    if err != nil {
        log.Fatalf("QueryRow failed: %vn", err)
    }

    log.Printf("%#v", post)
}

Output:

main.Post{Id:"the_id", Likes:[]main.Like{main.Like{Name:"a", Picture:"a.png"}, main.Like{Name:"b", Picture:"b.png"}}}

You most likely need to implement the interface for sql.Driver to "know" how to scan into your struct, 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)
}

Please signup or login to give your own answer.

Click here to cancel reply.

Why JSONB is returning as a string? – Postgresql

Answers