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.

Turn on debug mode and update the question to include the logged query that gorm generates. And include the exact Go/gorm code (the full method chain please) that generates the logged query. — mkopriva
– mkopriva, Commented Apr 17, 2023 at 14:45
The query you show does not restrict itself to "the first one". — jjanes
– jjanes, Commented Apr 17, 2023 at 16:14

Cetin Basoz · Accepted Answer · 2023-04-17 16:36:48Z

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"}}

Collectives™ on Stack Overflow

gorm postgres query json array for element

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related