gorm postgres query json array for element

Question

in my golang project I use postgres with gorm and I have an attributes column with the following json:

{"email": ["[email protected]", "[email protected]", "[email protected]"], "mail_folder": "some_folder"}
{"email": ["[email protected]", "[email protected]", "[email protected]"], "mail_folder": "some_folder"}

So I need to get a record which contains email [email protected] which is the first one. I can extract it with plain sql in sql editor with the following query:

select * from authors a where attributes @> '{"email": ["[email protected]"]}';

but in gorm I keep getting wrong json syntax errors etc. I tried to do it with Raw() query or with

Where(fmt.Sprintf("attributes ->> 'email' = '[\"%v\"]'", email)).

but it did not work either. Any ideas how to fix it would be welcome. Thank you.

Answer 1

Sampledb in postgreSQL:

Create Table authors
(
    id         serial,
    dummy      text,
    attributes jsonb
);

insert into authors (dummy, attributes)
VALUES ('eee', '{
  "email": [
    "[email protected]",
    "[email protected]",
    "[email protected]"
  ],
  "mail_folder": "some_folder"
}'),
       ('zzz', '{
         "email": [
           "[email protected]",
           "[email protected]",
           "[email protected]"
         ],
         "mail_folder": "some_folder"
       }');

This works fine:

package main

import (
    "fmt"
    postgres2 "github.com/jinzhu/gorm/dialects/postgres"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "log"
)

var (
    dsn = "host=localhost user=postgres password=secret dbname=sampledb port=5432 sslmode=disable TimeZone=europe/istanbul"
)

type Author struct {
    Id         int `gorm:"primaryKey"`
    Dummy      string
    Attributes postgres2.Jsonb `gorm:"type:jsonb;default:'{}'"`
}

var DB *gorm.DB

func main() {
    DB = initDb()
    listAuthors()
}

func listAuthors() {
    var authors []Author
    DB.Find(&authors, "attributes @> '{\"email\": [\"[email protected]\"]}'")

    for _, a := range authors {
        fmt.Printf("%d %s %s\n", a.Id, a.Dummy, a.Attributes)
    }
}

func initDb() *gorm.DB {
    db, err := gorm.Open(postgres.Open(dsn))
    if err != nil {
        log.Fatal("couldn't connect to db")
    }
    return db
}

For the sample data prints:

1 eee {{"email": ["[email protected]", "[email protected]", "[email protected]"], "mail_folder": "some_folder"}}