如何在Hasura+Postgres中创建"json"列和"int"(id)列之

Question

我有两个表users和post

表users有列id和post，列包含形式为[1, 2, 3, 4, 5]的数组，其中1, 2, 3, 4, 5是表post中的id

在表posts中，以下列id和text

表users:

https://i.stack.imgur.com/ywdS7.png

表posts:

https://i.stack.imgur.com/IBdpb.png

在hasura中做了一个数组关系

https://i.stack.imgur.com/311sd.png

接下来我提出了以下请求

{
users_test {
postz {
id
}
}
}

我想收到这样的数据作为回应：

postz: [
   {
     text: 'qwe'
   },
   {
     text: 'sdf'
   }
]

但有了这样的请求，我得到了线索。错误：

{
"errors": [
{
"extensions": {
"internal": {
"statement": "SELECT  coalesce(json_agg("root" ), '[]' ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_5_e"  FROM  (SELECT  "_4_root.ar.root.postz"."postz" AS "postz"       ) AS "_5_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."users_test"  WHERE ('true')     ) AS "_0_root.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("postz" ), '[]' ) AS "postz" FROM  (SELECT  row_to_json((SELECT  "_2_e"  FROM  (SELECT  "_1_root.ar.root.postz.base"."id" AS "id"       ) AS "_2_e"      ) ) AS "postz" FROM  (SELECT  *  FROM "public"."posts"  WHERE (("_0_root.base"."post") = ("id"))     ) AS "_1_root.ar.root.postz.base"      ) AS "_3_root.ar.root.postz"      ) AS "_4_root.ar.root.postz" ON ('true')      ) AS "_6_root"      ",
"prepared": true,
"error": {
"exec_status": "FatalError",
"hint": "No operator matches the given name and argument type(s). You might need to add explicit type casts.",
"message": "operator does not exist: json = integer",
"status_code": "42883",
"description": null
},
"arguments": [
"(Oid 114,Just ("{\"x-hasura-role\":\"admin\"}",Binary))"
]
},
"path": "$",
"code": "unexpected"
},
"message": "postgres query error"
}
]
}

我做错了什么？我该如何解决？

Answer 1

几个建议：

据我所知，您的查询中有一些拼写错误。尝试：

{
users {
id
posts {
text
}
}
}

2. 您不需要users表上的post列。您只需要posts表上的user_id列，以及从posts表到users表的外键约束，分别使用表的user_id和id列。查看此处的文档：

https://docs.hasura.io/1.0/graphql/manual/schema/relationships/create.html#step-3-创建阵列关系

https://docs.hasura.io/1.0/graphql/manual/schema/relationships/database-modelling/one-to-many.html

3. 如果由于某种原因必须使用post数组列，则可以使用计算字段在json数组和另一个表的id之间创建"关系">

https://docs.hasura.io/1.0/graphql/manual/schema/computed-fields.html#table-计算字段

你的功能是：

• 接收json数组列

• 提取id的

• 返回select*from table where id in id’s

示例：

https://jsonb-relationships-hasura.herokuapp.com/console/api-explorer

计算的字段定义位于：https://jsonb-relationships-hasura.herokuapp.com/console/data/schema/public/tables/authors/modify

运行以下查询：

# Get list of articles for each author
query {
authors {
id
name
articles
}
}

# Get actual articles for each author
query {
authors {
id
name
owned_articles {
id
title
}
}
}