在这篇文章之后,我有一个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]}
在某种意义上它是正确的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"}]}]