SQL joins are not reflected in json output about trdsql HOT 5 CLOSED

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

from trdsql.

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

from trdsql.

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

from trdsql.

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.

from trdsql.

Thank you very much.
I will refer to it.

from trdsql.