gorm postgres queries elements in json array

gorm postgres Querying elements in a json array is a common need, especially when dealing with complex data structures. When using GORM for database queries, we can achieve this goal through some techniques. In this article, we will show you how to query elements in a json array using GORM and Postgres database. Whether you are a beginner or an experienced developer, this article will provide you with detailed guidance to help you solve this problem easily. let's start!

Question content

In my golang project, I use postgres with gorm and have an attribute column containing 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 the records containing the email [email protected], which is the first record. I can extract it using pure sql in sql editor using the following query:

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

But in gorm I keep getting bad json syntax errors etc. I tried using raw() query or using

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

But it doesn't work either. Any ideas on how to fix it would be welcome. Thanks.

Solution

sampledb in postgresql:

create table authors
(
    id         serial,
    dummy      text,
    attributes jsonb
);

insert into authors (dummy, attributes)
values ('eee', '{
  "email": [
    "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="0762626247646464296464">[email&#160;protected]</a>",
    "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="bccececefcdedede92dfdf">[email&#160;protected]</a>",
    "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="5d2929291d3e3e3e732727">[email&#160;protected]</a>"
  ],
  "mail_folder": "some_folder"
}'),
       ('zzz', '{
         "email": [
           "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="0e7474744e6d6d6d206d6d">[email&#160;protected]</a>",
           "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="b3d2d2d2f3d1d1d19dd0d0">[email&#160;protected]</a>",
           "<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="a7c5c5c5e7c4c4c489dddd">[email&#160;protected]</a>"
         ],
         "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\": [\"<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="95f0f0f0d5f6f6f6bbf6f6">[email&#160;protected]</a>\"]}'")

    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 sample data printing:

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

The above is the detailed content of gorm postgres queries elements in json array. For more information, please follow other related articles on the PHP Chinese website!