Sunday, October 23, 2016

nodejs postgres pg.client returning partial results in json

i had a very weird problem with a nodejs and postgre app i'm building:
i'm running a sql query with join in dbeaver:
select matches.match_date, t1.team_long, t1.team_short, t1.id, 
t1.logo, t2.team_long, t2.team_short, t2.id, t2.logo 
from teams t1, teams t2, matches 
where t1.id=matches.host_team_id 
and t2.id=matches.guest_team_id
and in dbeaver it is returning the expected result
match_date team_long team_short 
2016-11-22 Juventus JUVE 
id logo team_long team_short id logo
6 1juventus.png Everton EVE 7 1everton.png
which includes all expected columns.
but when run in my node app with pg client, i'm only receiving a portion of the columns in the resulting json:
{"match_date":"2016-11-22","team_long":"Everton",
"team_short":"EVE","id":7,"logo":"everton-badge-2014-90x90.png"}
in the above json, the data for Juventus is missing.
for some crazy reason, i thought.

here is the js code i'm using to read the data in node:
// SQL Query > Select Data
const query = client.query(' select matches.match_date, 
t1.team_long as host_team_long, t1.team_short 
as host_team_short, 
t1.id as host_id, t1.logo as host_logo, 
t2.team_long as guest_team_long, 
t2.team_short as guest_team_short, t2.id guest_id, 
t2.logo as guest_logo from teams t1, 
teams t2, matches where t1.id=matches.host_team_id 
and t2.id=matches.guest_team_id');

// Stream results back one row at a time
query.on('row', (row) => {
    console.log(row);
    resultData.push(row);
});
so, somewhere here hides the key to the weird behavior.
but what could it be???
is it something in the postgre db 'driver'?
or nodejs itself?

about an hour of guessing later, i turned to write a question about this problem on stackoverflow.com.

copy/paste the sql query, copy paste the results...should i include the column names as well?
let's do it for readibility...

wait a second!

the column names for the same fields of different records are the same!
but, but, but - how will this project in the json results?

apparently you cannot have two properties with the same name so....SOOOOOOOO...here lies the reason for my 'trimmed' results!

whatever json lib the pg client is using it doesn't care (perhaps rightly so, perhaps not) that we may be overwriting values in result of a sql query


 schoooop

quickly changing the sql query with custom result column names (using 'as') solved the problem! here are the correct and beautiful results:
{ match_date: '2016-10-22',
  host_team_long: 'Atlético de Madrid',
  host_team_short: 'ATL',
  host_id: 3,
  host_logo: 'Atletico_Madrid.png',
  guest_team_long: 'Internazionale',
  guest_team_short: 'INT',
  guest_id: 8,
  guest_logo: 'Inter_Milan.png' }


on to the next problem!
cya!