Postgres join JSON list

Question

I'm having problems joining a JSON list in postgres 9.4

Here's my problem:

Table Structure:

CREATE TABLE players
(
  id serial NOT NULL,
  player json,
  CONSTRAINT players_pkey PRIMARY KEY (id)
)

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

Sample Data:

players
1;"{"Name":"AAA","Height":186,"Weight":65}"
2;"{"Name":"BBB","Height":195,"Weight":85}"
3;"{"Name":"CCC","Height":175,"Weight":72}"
4;"{"Name":"DDD","Height":168,"Weight":56}"

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

The players on each team are the players that were on the team in that team's last game. A query for getting the team name and a list of players (the current team) is as follows:

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

This returns the following (Teams table):

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

I want to join each player in the above result with their height and weight in the list so that the output is the following:

"{"Name":"TeamA","Players":[{"Name":"CCC","Height":175,"Weight":72},{"Name":"BBB","Height":195,"Weight":85}]}"
"{"Name":"TeamB","Players":[{"Name":"AAA","Height":186,"Weight":65},{"Name":"DDD","Height":168,"Weight":56}]}"

I have tried to do this using an INNER JOIN:

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

SELECT player FROM (SELECT json_array_elements(team->'Players') FROM u) AS v
INNER JOIN players on v->>'Name'=player->>'Name';

This is a step towards what I want, but I get the following error:

ERROR: operator does not exist: record ->> unknown
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Character: 2822

Can anyone suggest a solution to this problem or another way to do this?

Thanks

ruakh · Accepted Answer · 2015-05-31 07:07:07Z

2

This:

(SELECT json_array_elements(team->'Players') FROM u) AS v

declares v as a relation, so any references to v elsewhere will mean "a record in this relation". So this:

v->>'Name'=player->>'Name'

doesn't work, because it's trying to use v as a JSON value instead of as a record.

To fix this, you need to use a column alias; for example, you could write:

SELECT player FROM (SELECT json_array_elements(team->'Players') FROM u) AS v(v_player)
INNER JOIN players on v_player->>'Name' = player->>'Name';

answered May 31, 2015 at 7:07

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

janderson Over a year ago

That fixed it, thanks a lot. Is Inner Join the best way to do this?

janderson Over a year ago

I still need to get it into the output above, how can I join this back with my previous query?

Collectives™ on Stack Overflow

Postgres join JSON list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related