postgresql - Postgres join JSON list -

i'm having problems joining json list in postgres 9.4

here's 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 players on team in team's last game. query getting team name , list of players (the current team) follows:

select distinct on (t.team->>'name') t.team   matches m, json_array_elements(m.match->'teams') t(team) order  t.team->>'name', m.id desc 

this returns following (teams table):

"{"name":"teama","players":[{"name":"ccc"},{"name":"bbb"}]}" "{"name":"teamb","players":[{"name":"aaa"},{"name":"ddd"}]}" 

i want join each player in above result height , weight in list output 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 using inner join:

with u (select distinct on (t.team->>'name') t.team   matches m, json_array_elements(m.match->'teams') t(team)   -- above!!! order  t.team->>'name', m.id desc)   select player (select json_array_elements(team->'players') u) v inner join players on v->>'name'=player->>'name'; 

this step towards want, following error:

error: operator not exist: record ->> unknown sql state: 42883 hint: no operator matches given name , argument type(s). might need add explicit type casts. character: 2822 

can suggest solution problem or way this?

thanks

this:

(select json_array_elements(team->'players') u) v 

declares v relation, references v elsewhere mean "a record in relation". this:

v->>'name'=player->>'name' 

doesn't work, because it's trying use v json value instead of record.

to fix this, need use column alias; example, write:

select player (select json_array_elements(team->'players') u) v(v_player) inner join players on v_player->>'name' = player->>'name';