CAP Node.JS flatten structure for expand column in the SELECT statement

[worldstes]

Hi,

I'm trying to build a query using the standard Node.JS CAP library via the SELECT.from(...).columns(...), and inside the columns function, I'm expanding some column, such as SELECT.from(...).columns((item) => item.column((c) => c`.*`)). So, the question is: how can I get a result with c`.*` columns as a flatten structure, not directly provided as an object?

I mean, after this query execution, I got the following structure:

{
   "ID": "some-id",
   "name": "some column value",
   "column": {
       "ID": "some-id-from-nested-column",
       ...
   }
   ...
}

But, I want to receive something like:

{
   "ID": "some-id",
   "name": "some column value",
   "column_ID": "some-id-from-nested-column"
   ...
}

Thanks!

[gregorw]

Have you tried creating a view that solves your requirement. That way you maybe don't need any additional coding.

[Willem_Pardaens]

Don't think this is possible via a normal CAP query, but if you can do some manipulation to the object afterwards, then this might work:

items.forEach(item => {
    const column = item.column;
    Object.keys(column).forEach(c => {
        item['column_' + c] = column[c];
    });
    delete item.column;
});

where "items" is your initial list of objects.

[worldstes]

Thanks for the answer. I've tried that already with the handlers, but this is not suitable for some functions like $inlinecount using such columns in the OData query.

For example: http://localhost:4004/v2/overview/WorklistItemAnalyticsView?$skip=0&$top=100&$select=urgencyLevel_co...

won't work properly due to the fact that the urgencyLevel_color_ID is expanded (flattened) in such a way in a handler.

[Willem_Pardaens]

The output of the handler should correspond with the data structure of your schema, so if your schema is not 'flattened' then this won't work indeed. You could create a new table and have a custom SRV.ON handler call the custom query and return the data flattened according to that new data structure.

srv.on('READ', 'newTable', async req => {

const rawData = await SELECT.from('oldTable').columns(...)

const flatData = flattenStructure(rawData);

req.reply(flatData);

});