AlaSQL nested arrays

Question

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

Trying to acomplish this 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?

score 1 · Answer 1 · answered Mar 06 '17 at 15:04

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.

Any suggestion will be welcome.

AlaSQL nested arrays

1 Answers1