Postgres json_agg包含列标题-不正确的JSON? [英] Postgres json_agg includes column header - Incorrect JSON?

问题描述

关注这篇文章，我有一个返回下表的SQL查询:

Follwoing this post, I have a SQL query that returns the following table:

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

我需要将其聚合到JSON中以在我的应用中使用.我正在使用以下内容:

I need to aggregate this into JSON to use in my app. I'm using the following:

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"}]}}]"

因此，似乎已在每个对象之前放置了团队"标签.这无法在我的C#应用​​程序中正确序列化.阅读此问题后，我相信这可能不是正确的JSON.这是正确的吗?

So it appears that it has put a "team" tag before each object. This doesn't serialise in my C# app properly. After reading this question I believe that this might not be correct JSON. Is this correct?

我想以以下形式获取它:

I want to get it in the following form:

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

我相信这是正确的JSON，它将在我的应用程序中正确解析.

I believe that this is correct JSON and it will parse in my app correctly.

我在C#中使用的类如下:

The class I'm using in C# is as follows:

//[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输出中删除列标题?

Is this correct JSON and I need to fix my C# class? Or is it incorrect and how can I remove the column header from the json_agg output?

更新:

这是我的表结构:

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]}

请参阅此问题以了解更多信息.

Please see this question for more.

推荐答案

从某种意义上讲，它是正确的JSON，但显然不是您想要的.因此答案取决于正确"的定义.我们将假定您想要的是正确的".

It is correct JSON in a sense that it's valid, but it's clearly not what you want. So the answer depends on the definition of "correct". We will assume that what you want is "correct".

您要它汇总u，这是具有列team的结果集.这意味着它将必须将此信息添加到结果中.您只应要求汇总u.team，这是您想要的字段.然后，您将获得想要的结果.

You're asking it to aggregate u, which is a resultset with column team. This means it will have to add this information to the result. You should only ask for aggregation of u.team, which is the field you want. Then you will get the result you want.

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"，玩家":[{名称":"CCC"}，{名称":"BBB"}]}}，
{名称":"TeamB"，玩家":[{名称":"AAA"}，{名称":"DDD"}]}]]

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

这篇关于Postgres json_agg包含列标题-不正确的JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！