Golang 结构的 Postgres 数组

Question

我有以下 Go 结构：type Bar struct {    Stuff string `db:"stuff"`    Other string `db:"other"`}type Foo struct {    ID    int    `db:"id"`    Bars  []*Bar `db:"bars"`}所以Foo包含一片Bar指针。我在 Postgres 中也有以下表格：CREATE TABLE foo (    id  INT)CREATE TABLE bar (    id      INT,    stuff   VARCHAR,    other   VARCHAR,    trash   VARCHAR)我想LEFT JOIN放在桌子上bar并将其聚合为要存储在 struct 中的数组Foo。我试过了：SELECT f.*,ARRAY_AGG(b.stuff, b.other) AS barsFROM foo fLEFT JOIN bar bON f.id = b.idWHERE f.id = $1GROUP BY f.id但看起来ARRAY_AGG函数签名不正确（function array_agg(character varying, character varying) does not exist）。有没有办法在不单独查询的情况下做到这一点bar？

Answer 1

看起来你想要的是bars一个 bar 对象数组来匹配你的 Go 类型。为此，您应该使用JSON_AGG而不是ARRAY_AGG因为ARRAY_AGG仅适用于单列，并且在这种情况下会生成文本类型 ( TEXT[]) 的数组。JSON_AGG，另一方面，创建一个 json 对象数组。您可以将其与JSON_BUILD_OBJECT仅选择所需的列相结合。

这是一个例子：

SELECT f.*,

JSON_AGG(JSON_BUILD_OBJECT('stuff', b.stuff, 'other', b.other)) AS bars

FROM foo f

LEFT JOIN bar b

ON f.id = b.id

WHERE f.id = $1

GROUP BY f.id

然后你必须处理在 Go 中解组 json，但除此之外你应该很高兴。

另请注意，在将 json 解组为结构时，Go 会为您忽略未使用的键，因此您可以根据bar需要选择表上的所有字段来简化查询。像这样：

SELECT f.*,

JSON_AGG(TO_JSON(b.*)) AS bars -- or JSON_AGG(b.*)

FROM foo f

LEFT JOIN bar b

ON f.id = b.id

WHERE f.id = $1

GROUP BY f.id

如果您还想处理 inbar中的记录没有条目的情况foo，您可以使用：

SELECT f.*,

COALESCE(

    JSON_AGG(TO_JSON(b.*)) FILTER (WHERE b.id IS NOT NULL),

    '[]'::JSON

) AS bars

FROM foo f

LEFT JOIN bar b

ON f.id = b.id

WHERE f.id = $1

GROUP BY f.id

如果没有FILTER，您将获得[NULL]infoo中没有相应行的行bar，而FILTER只是给您NULL，然后只需使用它COALESCE来转换为空的 json 数组。

Answer 2

正如您已经知道的那样array_agg，接受一个参数并返回参数类型的数组。因此，如果您希望所有行的列都包含在数组的元素中，您可以直接传入行引用，例如：

SELECT array_agg(b) FROM b

但是，如果您只想在数组元素中包含特定列，则可以使用ROW构造函数，例如：

SELECT array_agg(ROW(b.stuff, b.other)) FROM b

Go 的标准库为仅扫描标量值提供了开箱即用的支持。要扫描更复杂的值，例如任意对象和数组，必须寻找 3rd 方解决方案，或者实现他们自己的sql.Scanner.

为了能够实现自己的sql.Scanner并正确解析 postgres 行数组，您首先需要知道 postgres 用于输出值的格式，您可以通过使用psql和一些直接查询来找到它：

-- simple values

SELECT ARRAY[ROW(123,'foo'),ROW(456,'bar')];

-- output: {"(123,foo)","(456,bar)"}

-- not so simple values 

SELECT ARRAY[ROW(1,'a b'),ROW(2,'a,b'),ROW(3,'a",b'),ROW(4,'(a,b)'),ROW(5,'"','""')];

-- output: {"(1,\"a b\")","(2,\"a,b\")","(3,\"a\"\",b\")","(4,\"(a,b)\")","(5,\"\"\"\",\"\"\"\"\"\")"}

正如你所看到的，这可能会变得很复杂，但它是可解析的，语法看起来是这样的：

{"(column_value[, ...])"[, ...]}

wherecolumn_value是未加引号的值，或者是带有转义双引号的引用值，并且这样的引用值本身可以包含转义的双引号，但只能包含两个，即单个转义的双引号不会出现在column_value. 所以解析器的粗略和不完整的实现可能看起来像这样：

注意：在解析过程中可能需要考虑其他我不知道的语法规则。除此之外，下面的代码不能正确处理 NULL。

func parseRowArray(a []byte) (out [][]string) {

    a = a[1 : len(a)-1] // drop surrounding curlies

    for i := 0; i < len(a); i++ {

        if a[i] == '"' { // start of row element

            row := []string{}

            i += 2 // skip over current '"' and the following '('

            for j := i; j < len(a); j++ {

                if a[j] == '\\' && a[j+1] == '"' { // start of quoted column value

                    var col string // column value

                    j += 2 // skip over current '\' and following '"'

                    for k := j; k < len(a); k++ {

                        if a[k] == '\\' && a[k+1] == '"' { // end of quoted column, maybe

                            if a[k+2] == '\\' && a[k+3] == '"' { // nope, just escaped quote

                                col += string(a[j:k]) + `"`

                                k += 3    // skip over `\"\` (the k++ in the for statement will skip over the `"`)

                                j = k + 1 // skip over `\"\"`

                                continue  // go to k loop

                            } else { // yes, end of quoted column

                                col += string(a[j:k])

                                row = append(row, col)

                                j = k + 2 // skip over `\"`

                                break     // go back to j loop

                            }

                        }

                    }

                    if a[j] == ')' { // row end

                        out = append(out, row)

                        i = j + 1 // advance i to j's position and skip the potential ','

                        break     // go to back i loop

                    }

                } else { // assume non quoted column value

                    for k := j; k < len(a); k++ {

                        if a[k] == ',' || a[k] == ')' { // column value end

                            col := string(a[j:k])

                            row = append(row, col)

                            j = k // advance j to k's position

                            break // go back to j loop

                        }

                    }

                    if a[j] == ')' { // row end

                        out = append(out, row)

                        i = j + 1 // advance i to j's position and skip the potential ','

                        break     // go to back i loop

                    }

                }

            }

        }

    }

    return out

}

试一试playground。

有了类似的东西，您就可sql.Scanner以为您的 Go 条形图实现一个。

type BarList []*Bar

func (ls *BarList) Scan(src interface{}) error {

    switch data := src.(type) {

    case []byte:

        a := praseRowArray(data)

        res := make(BarList, len(a))

        for i := 0; i < len(a); i++ {

            bar := new(Bar)

            // Here i'm assuming the parser produced a slice of at least two

            // strings, if there are cases where this may not be the true you

            // should add proper length checks to avoid unnecessary panics.

            bar.Stuff = a[i][0]

            bar.Other = a[i][1]

            res[i] = bar

        }

        *ls = res

    }

    return nil

}

现在，如果您将类型中的Bars字段Foo类型从[]*Bar更改为，BarList您将能够直接将字段的指针传递给(*sql.Row|*sql.Rows).Scan调用：

rows.Scan(&f.Bars)

如果您不想更改字段的类型，您仍然可以通过在将指针传递给Scan方法时转换指针来使其工作：

rows.Scan((*BarList)(&f.Bars))

JSON

sql.ScannerHenry Woody 建议的 json 解决方案的实现如下所示：

type BarList []*Bar

func (ls *BarList) Scan(src interface{}) error {

    if b, ok := src.([]byte); ok {

        return json.Unmarshal(b, ls)

    }

    return nil

}