Is SQL capable of returning nested data? If not, is there a best practice for this?

Question

Right now I'm working with a query which outputs about 20k lines of data from table1. Node conveniently converts this to a simple JSON. So it's a huge object containing the outputted rows as smaller objects.

{
    { // item 1
        column1: value1,
        column2: value2,
        ...
    },
    { // item 2
        column1: value1,
        column2: value2,
        ...
    },
    ...
}

For each item in this result I'd like to select more values from table2. However, the correlation between an item in the list and the rows of table 2 is one to many. That is, if I do a simple left join, it puts out something like this:

{
    { // item 1 with added value from table2
        ...
    },
    { // item 1 with different value from table2
        ...
    },
    { // item 1 with yet another value from table2
        ...
    },
    ...
}

This is incredebly wasteful on bandwith and is way above the speed limit I want it to work at for 20k rows. What I'd somehow want to achieve is a structure like this one:

{
    { // item 1
        column1: value1,
        column2: value2,
        ...
        {
            table2value1: value1,
            table2value2: value2,
            ...
        }
    },
    { // item 2
        column1: value1,
        column2: value2,
        ...
        {
            table2value1: value1,
            table2value2: value2,
            ...
        }
    },
    ...
}

For now, just looping through the 20000 items in a prepared statement is my best guess on how to do this. However, this is also quite slow, and there's probably a better way I don't know of (or couldn't think of). Some specs: working with Node, mssql package and SQL Server 2000 (so sorry) as the database.

Any input is deeply appreciated!

LEFT JOIN is the exactly what gives this result. Sorry for not specifying. — mathaway__, Jan 20 '15 at 19:55

score 0 · Answer 1 · answered Jan 20 '15 at 19:47

You could write a query that returns an ordered list:

customer   order   orderline   ...
1          1       1
1          1       2
1          2       1
2          3       1

Client side, you can iterate over this rowset and create a new customer whenever the customer id changes, a new order when the order number changes, and so on. The fact that this approach uses only one database call makes it pretty fast.

Is SQL capable of returning nested data? If not, is there a best practice for this?

1 Answers1