Alasql: Nested Arrays

[benjaroa]

Hi,

I'm trying tu use the AlaSQL Array agregator inside an array, it appears to be not supported :(

Trying to acomplish this JSon output (or like this):

[
    {
        "keyword":"project 1",
        "projects": [
            {
                "year":2014,
                "description":"this is a project description",
                "budget": 5600,
                "status": "closed",
                "metadata": [
                    {
                        "key":"provider",
                        "value":"just me"
                    },
                    {
                        "key":"client",
                        "value":"someone"
                    },
                    {
                        "key":"any thing",
                        "value":"any data..."
                    }
                ]
            }
        ]
    }
]

My Database Structure

people e
| name      | type    |
|-----------|---------|
| name      | varchar |
| lastname  | varchar |
| person_id | varchar |
| cel       | int     |

projects p
| name        | type    |
|-------------|---------|
| keyword     | varchar |
| year        | int     |
| description | text    |
| budget      | int     |

PeopleProjects x
| name        | type    |
|-------------|---------|
| e_person_id | varchar |
| p_keyword   | varchar |
| p_year      | int     |
| status      | varchar |

metadata m
| name        | type    |
|-------------|---------|
| e_person_id | varchar |
| p_keyword   | varchar |
| p_year      | int     |
| key         | varchar |
| value       | varchar |

My query

alasql("SELECT p.keyword, \
ARRAY({year:p.year, description:p.description, budget:p.budget, status:x.status, \
    ARRAY({key:m.key, value:m.value}) as metadata \
}) AS projects \
FROM projects p, PeopleProjects x, metadata m \
WHERE \
    p.keyword = x.p_keyword AND x.p_keyword = m.p_keyword AND \
    p.year = x.p_year AND x.p_year = m.p_year \
    x.e_person_id = x.e_person_id AND AND x.e_person_id = 2 \
GROUP BY p.keyword");

At the moment, I divided the query in two and then combine the data to produce the hole thing.

Any ideas?

[mathiasrw]

You really should post questions like this on stack overflow.

Please provide your expected result from the SQL given the input that you gave as example

[benjaroa]

Hi Mathias,

At the time I post this, stackoverflow was having problems with publishing, that's why I did it here. I tried now and succeed.

As I originally say, I'm expecting this output:

[
    {
        "keyword":"project 1",
        "projects": [
            {
                "year":2014,
                "description":"this is a project description",
                "budget": 5600,
                "status": "closed",
                "metadata": [
                    {
                        "key":"provider",
                        "value":"just me"
                    },
                    {
                        "key":"client",
                        "value":"someone"
                    },
                    {
                        "key":"any thing",
                        "value":"any data..."
                    }
                ]
            }
        ]
    }
]

Regards!

[mathiasrw]

Sorry - I was too quick reading this on. You did provide all the data needed in your first post.

I really suggest you do a looped based build of your output. AlaSQL is good to "flatten" results, not to create nested json structures.

Let me know if you need any inptus to this

[benjaroa]

Anyone?

Also asked this in Stackoverflow, same luck...

(http://stackoverflow.com/questions/42298265/alasql-nested-arrays)

[mathiasrw]

I really suggest you do a loop based formatting of your desired output.

[mathiasrw]

If you want inputs let me know

[benjaroa]

Thanks! I managed to get the output the best I could, but my solution is far from perfection.

It would be nice to see what you suggest!

[mathiasrw]

I have many things to do. If you found a solution that works, im happy...

[benjaroa]

As I say in my original post:

At the moment, I divided the query in two and then combine the data to produce the hole thing.

Far for scalable.

Can you leave me a clue?

[mathiasrw]

I have no better way than to combine data from several SQLs

As access is cheap (the data is in memory - and not in the cloud somewhere) we can be more relaxed with how many times we do SQLs

if this is not ment to be running more than 1 time per second and the data is less than a few hundred rows I would not spend much time optimizing but just go along something like:

var sqlGetProject = function(){/*
SELECT year, 
       description, 
       budget, 
       status 
FROM   projects 
       JOIN peopleprojects 
         ON p_keyword = keyword 
            AND p_year = year 
WHERE  keyword = ? 
           */};

var sqlGetMetadata = function(){/*
SELECT `key`, `value`
FROM   projects 
       JOIN metadata 
         ON p_keyword = keyword 
            AND p_year = year 
WHERE  keyword = ? 
               AND 
               year = ?
           */};


var data = [];
alasql('column of select distinct `keyword` from projects').foreach(keyword=>data.push({ keyword:keyword,projects:getProjects(keyword)}))
console.log(data);

function getProjects(keyword){
    let projects = [];
    alasql(sqlGetProject,keyword).foreach(project=>{
                 project.metadata = alasql(sqlGetMetadata,[project.keyword, project.year])
                 projects.push(project);
            })
    return projects
}

AlaSQL does not schedule the query, so we dont gain much from only fetching the results only once (to cut the overhead of involving the database)

Please note that function(){/*select 'MY MULTILINE SQL'*/} will execute the SQL in the comment.

[benjaroa]

Thanks a lot!

[mathiasrw]

What was your solution? lets see if we had the same ideas

[benjaroa]

I managed to get the output I want by fetching two times:

First, projects:

var projects = alasql("SELECT p.keyword, ARRAY({status:x.status, year:p.year, description:p.description, budget:p.budget }) AS years \
FROM PeopleProjects x, projects p \
WHERE x.p_keyword = p.keyword AND x.p_year = p.year AND x.e_person_id = "+person_id+" \
GROUP BY p.keyword");

Second, metadata:

var mdata = alasql("SELECT m.e_person_id, m.p_keyword, m.p_year, ARRAY({key:m.key,value:m.value}) as [metadata] \
FROM metadata m, PeopleProjects x \
WHERE x.e_person_id = m.e_person_id AND x.p_keyword = m.p_keyword AND x.p_year = m.p_year AND x.e_person_id = "+person_id+" \
GROUP BY m.e_person_id, m.p_keyword, m.p_year");

Then, loop over projects, searching and storing the metadata:

for(i=0;i<projects.length;i++){
    for(j=0;j<projects[i].years.length;j++){
        current = projects[i].keyword;
        current_year = projects[i].years[0].year;
        search = alasql("SEARCH / * WHERE(year="+current_year+" AND keyword='"+current+"') FROM ?",[mdata]);

        projects[i].years[j]["metadata"] = search[0].metadata;
    }
}

Now projects has all the data.

[mathiasrw]

This is really nice too.

I especially like the ARRAY(...) where you generate the object on the fly