2

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

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

Which returns:

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

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

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

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

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?

UPDATE:

Here's my table structure:

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

And sample data:

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.

janderson
  • 963
  • 4
  • 14
  • 26
  • Use this link http://jsonlint.com/ and check whether your json valid or not. I checked your json and it seems to be invalid!! – Santhucool May 29 '15 at 04:37
  • Both appear to be valid. It seems to be redundant to have the extra team tag though? – janderson May 29 '15 at 04:41
  • It looks valid to me, it just has an unnecessary 1-element array with a 1-key object within it. Since you have not provided *sample data and table definitions* I cannot really help you though; maybe you should fix that then comment here when done. – Craig Ringer May 29 '15 at 04:55
  • @CraigRinger Updated with table definition and sample data. – janderson May 29 '15 at 05:00

1 Answers1

4

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".

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;

Result:

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

Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74