AlaSQL嵌套阵列

Question

我试图在数组中使用AlaSQL 数组集积器，它似乎不受支持：

试图将此输出(或类似的)封装在一起：

[
    {
        "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..."
                    }
                ]
            }
        ]
    }
]

我的数据库结构

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 |

我的疑问

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");

目前，我将查询分为两个部分，然后将数据组合起来生成空洞的东西。

有什么想法吗？

Answer 1

我成功地获得了我想要的输出，获取了两次：

第一，项目：

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");

第二，元数据：

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");

然后，循环遍历项目，搜索和存储元数据：

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;
    }
}

现在，projects拥有所有的数据。

任何建议都将受到欢迎。