SQL joins are not reflected in json output

[sbeaupre]

When using the join from the example, the json output is different than the csv output (only the latter is correct)

❯ cat hist.csv
1,2017-7-10
2,2017-7-10
2,2017-7-11%

❯ cat user.csv
1,userA
2,userB%

❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql "SELECT u.c1,u.c2,h.c2 FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)"
1,userA,2017-7-10
2,userB,2017-7-10
2,userB,2017-7-11


❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql -ojson "SELECT u.c1,u.c2,h.c2 FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)"
[
  {
    "c1": "1",
    "c2": "2017-7-10"
  },
  {
    "c1": "2",
    "c2": "2017-7-10"
  },
  {
    "c1": "2",
    "c2": "2017-7-11"
  }
]

[noborus]

Thank you for a good issue.
The reason is that json is unified when there is the same column name.
If you add an alias to the column name, all columns will be output.

trdsql -ojson "SELECT u.c1 as no,u.c2 as user ,h.c2 as date FROM user.csv as u LEFT JOIN hist.csv as h ON(u.c1=h.c1)"

[
  {
    "date": "2017-7-10",
    "no": "1",
    "user": "userA"
  },
  {
    "date": "2017-7-10",
    "no": "2",
    "user": "userB"
  },
  {
    "date": "2017-7-11",
    "no": "2",
    "user": "userB"
  }
]

[sbeaupre]

Giving an alias to the column names solves the issue:

❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql -ojson "SELECT u.c1 as uc1,u.c2 as uc2,h.c2 as hc2 FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)"

Was expecting a nested result though, hence my initial question.

[sbeaupre]

sorry, just saw your reply after mine ;-)

Thank you for a good issue.
The reason is that json is unified when there is the same column name.
If you add an alias to the column name, all columns will be output.

trdsql -ojson "SELECT u.c1 as no,u.c2 as user ,h.c2 as date FROM user.csv as u LEFT JOIN hist.csv as h ON(u.c1=h.c1)"

[
  {
    "date": "2017-7-10",
    "no": "1",
    "user": "userA"
  },
  {
    "date": "2017-7-10",
    "no": "2",
    "user": "userB"
  },
  {
    "date": "2017-7-11",
    "no": "2",
    "user": "userB"
  }
]

[sbeaupre]

Maybe as an extra comment for future reference or addition to the docs. Here is a grouping example done via jq (https://stedolan.github.io/jq/)

❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql -ojson "SELECT u.c1 as id,u.c2 as name,h.c2 as date FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)" > out.json

❯ cat out.json
[
  {
    "date": "2017-7-10",
    "id": "1",
    "name": "userA"
  },
  {
    "date": "2017-7-10",
    "id": "2",
    "name": "userB"
  },
  {
    "date": "2017-7-11",
    "id": "2",
    "name": "userB"
  }
]

❯ cat out.json | jq 'group_by({id}) | map ({id: .[0].id, data: map({date: .date, name: .name}) })'
[
  {
    "id": "1",
    "data": [
      {
        "date": "2017-7-10",
        "name": "userA"
      }
    ]
  },
  {
    "id": "2",
    "data": [
      {
        "date": "2017-7-10",
        "name": "userB"
      },
      {
        "date": "2017-7-11",
        "name": "userB"
      }
    ]
  }
]

[noborus]

Thank you very much.
I will refer to it.