array_agg equivalent in CAP

[mehmetemin]

Hi experts,

I would like to achieve the array_agg functionality in my cds view.

This question in SO will give you lots of idea about what I want to achieve (Although I also have group by) . If I have a table like this;

and I want to end up like this;

I have the following entity;

entity ContextParameterValues as select from sitdm.SituationContextWithMetadata {
    key contextId,
    key fieldValue,
        entityTableFieldDesc,
        abapTableFieldDesc,
        count(contextId) as count: Integer,
        count( distinct sitnDefTemplateId ) as templateCount: Integer,
        
        // here is the field I would like to get distinct results as an array of strings
        @Aggregation.default : #Array 
        sitnDefTemplateId as templates: array of String,
        ContextBasedSituations /* navigational prop */
} group by contextId, fieldValue, entityTableFieldDesc, abapTableFieldDesc;

As you can see, I want to serve the entity grouped by some fields, and I want to have one of the non-grouped field to be aggregated in an array of string.

This line is working perfectly;

count( distinct sitnDefTemplateId ) as templateCount: Integer,

But I could not figure out the syntax for the next line

@Aggregation.default : #Array 
sitnDefTemplateId as templates: array of String,

I search the questions, blog posts and repositories but could not come accross something similar.

Hope I could explain my problem. If you need more details, please let me know.

Best,

Emin.

[mehmetemin · Accepted solution]

I found what I needed (comes with another problem though, but will discuss this later), and in case you end up here with the same problem I would like to share it with you as well.

All we need is to use STRING_AGG function. The code looks like the following;

entity ContextParameterValues as select from sitdm.SituationContextWithMetadata {
    key contextId,
    key fieldValue,
        entityTableFieldDesc,
        abapTableFieldDesc,
        count(contextId) as count: Integer,
        count( distinct sitnDefTemplateId ) as templatesCount: Integer,
        count( distinct lob ) as lobsCount: Integer,
        STRING_AGG(sitnDefTemplateId, '~') as templates: String,
        ContextBasedSituations /* navigational prop */
} group by contextId, fieldValue, entityTableFieldDesc, abapTableFieldDesc;

An example result looks like following then;

{
			"contextId": "MARA-MATNR",
			"fieldValue": "AP_PP_FIN",
			"entityTableFieldDesc": "Material Number",
			"abapTableFieldDesc": "Material Number",
			"count": 7,
			"templateCount": 3,
			"templates": "PROC_RFQLOWNUMBEROFQTN~PROC_RFQLOWNUMBEROFQTN~MAN_MATLOVERDUESITSITN~MAN_MATLOVERDUESITSITN~MAN_MATLOVERDUESITSITN~MAN_MATLOVERDUESITSITN~PROC_POITEMCONFQTYDEFICIT"
		},

Of course, I can write a simple logic on the after hook and provide the uniqueness, or we handle that in the frontend doesn't matter.

WHAT IS THE NEXT PROBLEM?

The solution above works nicely in the HANA db, in the cloud. However, it does not work on sqlite (or in postgres it will also not work I assume).

What works for sqlite is the following;

group_concat(sitnDefTemplateId, '~') as templates: String,

Instead of the line with STRING_AGG function. So my solution proposals to the problem are;

1. Compiler directive for cds compiler (Preprocessor directives like #ifdef for example https://www.cplusplus.com/doc/tutorial/preprocessor/ )
   I dont think this exist today, I wonder what community thinks of this?
2. Writing my own logic
   I did that already, but this is not a good solution for me because the problem is O(N2) and I am not happy with the solution.
3. Running a local CAP app that has a connection with a cloud DB
   Did not try this yet
4. teaching hana what group_concat mean (or vice versa). Something like, use STRING_AGG wherever you see group_concat.
   I saw something like synonym files for HANA but no idea if what I want can be achieved
5. Running a local HANA db
   Assume this is a painful process.

I would like to discuss these points further in the community.

Thanks for your contribution.

EDIT: HOW TO USE DIFFERENT DB FUNCTIONS IN THE SAME PROJECT

Feature is already implemented, and I find the code tour in the cloud cap samples repository very useful (also cap documentation has some more explanation).

kudos to Georg and Christian, thanks to their comments below I found the answer.

[gregorw · Accepted solution]

Hello christian.georgi,

I hope you could direct this suggestion to the right people. We face the same issue and solved it by maintaining a sqlite and HANA version of our view definition. I would suggest to translate the HANA command into the corresponding sqlite statement.

CU
Gregor

[chgeo · Accepted solution]

Please see this hint in the docs for using HANA and sqlite functions in one project.

[mehmetemin · Accepted solution]

Thank you both!

I find the code tour extremely helpful, very nice idea.

[former_member865942 · Accepted solution]

Hello Mehmet,

this blog helped to solve our critical issue in project, thank you very much for sharing your solution!

BR
Mengmeng