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 bars

FROM foo f

LEFT JOIN bar b

ON f.id = b.id

WHERE f.id = $1

GROUP 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 barsFROM foo fLEFT JOIN bar bON f.id = b.idWHERE f.id = $1GROUP BY f.id然后你必须处理在 Go 中解组 json，但除此之外你应该很高兴。另请注意，在将 json 解组为结构时，Go 会为您忽略未使用的键，因此您可以根据bar需要选择表上的所有字段来简化查询。像这样：SELECT f.*,JSON_AGG(TO_JSON(b.*)) AS bars -- or JSON_AGG(b.*)FROM foo fLEFT JOIN bar bON f.id = b.idWHERE f.id = $1GROUP BY f.id如果您还想处理 inbar中的记录没有条目的情况foo，您可以使用：SELECT f.*,COALESCE(    JSON_AGG(TO_JSON(b.*)) FILTER (WHERE b.id IS NOT NULL),    '[]'::JSON) AS barsFROM foo fLEFT JOIN bar bON f.id = b.idWHERE f.id = $1GROUP 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 bGo 的标准库为仅扫描标量值提供了开箱即用的支持。要扫描更复杂的值，例如任意对象和数组，必须寻找 3rd 方解决方案，或者实现他们自己的sql.Scanner.为了能够实现自己的sql.Scanner并正确解析 postgres 行数组，您首先需要知道 postgres 用于输出值的格式，您可以通过使用psql和一些直接查询来找到它：-- simple valuesSELECT ARRAY[ROW(123,'foo'),ROW(456,'bar')];-- output: {"(123,foo)","(456,bar)"}-- not so simple values&nbsp;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) {&nbsp; &nbsp; a = a[1 : len(a)-1] // drop surrounding curlies&nbsp; &nbsp; for i := 0; i < len(a); i++ {&nbsp; &nbsp; &nbsp; &nbsp; if a[i] == '"' { // start of row element&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row := []string{}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i += 2 // skip over current '"' and the following '('&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for j := i; j < len(a); j++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[j] == '\\' && a[j+1] == '"' { // start of quoted column value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var col string // column value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j += 2 // skip over current '\' and following '"'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for k := j; k < len(a); k++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[k] == '\\' && a[k+1] == '"' { // end of quoted column, maybe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[k+2] == '\\' && a[k+3] == '"' { // nope, just escaped quote&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; col += string(a[j:k]) + `"`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; k += 3&nbsp; &nbsp; // skip over `\"\` (the k++ in the for statement will skip over the `"`)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j = k + 1 // skip over `\"\"`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; continue&nbsp; // go to k loop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else { // yes, end of quoted column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; col += string(a[j:k])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row = append(row, col)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j = k + 2 // skip over `\"`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; &nbsp;// go back to j loop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[j] == ')' { // row end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out = append(out, row)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i = j + 1 // advance i to j's position and skip the potential ','&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; &nbsp;// go to back i loop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else { // assume non quoted column value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for k := j; k < len(a); k++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[k] == ',' || a[k] == ')' { // column value end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; col := string(a[j:k])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row = append(row, col)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j = k // advance j to k's position&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break // go back to j loop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if a[j] == ')' { // row end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out = append(out, row)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i = j + 1 // advance i to j's position and skip the potential ','&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; &nbsp;// go to back i loop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; return out}试一试playground。有了类似的东西，您就可sql.Scanner以为您的 Go 条形图实现一个。type BarList []*Barfunc (ls *BarList) Scan(src interface{}) error {&nbsp; &nbsp; switch data := src.(type) {&nbsp; &nbsp; case []byte:&nbsp; &nbsp; &nbsp; &nbsp; a := praseRowArray(data)&nbsp; &nbsp; &nbsp; &nbsp; res := make(BarList, len(a))&nbsp; &nbsp; &nbsp; &nbsp; for i := 0; i < len(a); i++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bar := new(Bar)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Here i'm assuming the parser produced a slice of at least two&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // strings, if there are cases where this may not be the true you&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // should add proper length checks to avoid unnecessary panics.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bar.Stuff = a[i][0]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bar.Other = a[i][1]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; res[i] = bar&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; *ls = res&nbsp; &nbsp; }&nbsp; &nbsp; return nil}现在，如果您将类型中的Bars字段Foo类型从[]*Bar更改为，BarList您将能够直接将字段的指针传递给(*sql.Row|*sql.Rows).Scan调用：rows.Scan(&f.Bars)如果您不想更改字段的类型，您仍然可以通过在将指针传递给Scan方法时转换指针来使其工作：rows.Scan((*BarList)(&f.Bars))JSONsql.ScannerHenry Woody 建议的 json 解决方案的实现如下所示：type BarList []*Barfunc (ls *BarList) Scan(src interface{}) error {&nbsp; &nbsp; if b, ok := src.([]byte); ok {&nbsp; &nbsp; &nbsp; &nbsp; return json.Unmarshal(b, ls)&nbsp; &nbsp; }&nbsp; &nbsp; return nil}