Postgres json_agg包含列头-错误的JSON

本文关键字：错误 JSON 包含列 json agg Postgres | 更新日期: 2023-09-27 17:51:02

在这篇文章之后，我有一个SQL查询，返回以下表:

team (json)
"{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]}"
"{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}"

我需要将其聚合为JSON以在我的应用程序中使用。我使用以下内容:

SELECT json_agg(u) FROM (SELECT DISTINCT ON (t.team->>'Name') t.team
FROM   matches m, json_array_elements(m.match->'Teams') t(team)
ORDER  BY t.team->>'Name', m.id DESC) AS u

返回:

"[{"team":{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]}}, 
 {"team":{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}}]"

所以它似乎在每个对象之前都放了一个"team"标签。这在我的c#应用程序中没有正确序列化。在阅读了这个问题之后，我认为这可能不是正确的JSON。这是正确的吗?

我想以以下形式得到它:

 "[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]}, 
  {"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}]"

我相信这是正确的JSON，它会在我的应用程序正确解析。

我在c#中使用的类如下: 
//[JsonObject(Title="team")]  I tried adding this too
public class Team
{
    public string Name { get; set; }
    public List<Player> Players { get; set; }
}

这是正确的JSON，我需要修复我的c#类?或者它是不正确的，我如何从json_agg输出中删除列标头?

:

这是我的表结构:

CREATE TABLE matches
(
  id serial NOT NULL,
  match json,
  CONSTRAINT matches_pkey PRIMARY KEY (id)
)

和样本数据:

5;{"Id":1,"Teams":[{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}],"TeamRank":[1,2]}
6;{"Id":2,"Teams":[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}],"TeamRank":[1,2]}

Postgres json_agg包含列头-错误的JSON

在某种意义上它是正确的JSON，但它显然不是您想要的。所以答案取决于"正确"的定义。我们将假设你想要的是"正确的"。

你要求它聚合u，这是一个包含team列的结果集。这意味着它必须将这些信息添加到结果中。您应该只要求u.team的聚合，这是您想要的字段。然后你就会得到你想要的结果。

WITH matches as
(
select 5 as id, '{"Id":1,"Teams":[{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}],"TeamRank":[1,2]}'::json as match
union all
select 6 as id, '{"Id":2,"Teams":[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}],"TeamRank":[1,2]}' as match
)
SELECT json_agg(u.team) FROM (
SELECT DISTINCT ON (t.team->>'Name') t.team
FROM   matches m, json_array_elements(m.match->'Teams') t(team)
ORDER  BY t.team->>'Name', m.id DESC) AS u;
结果:

[{"名称":"TeamA","玩家":[{" Name ": " CCC "},{"名称":" BBB "}]},
{" Name ": " TeamB"、"玩家":[{"名称":"AAA"},{"名称":"DDD"}]}]

相关文章：