How to populate entity with typed values from external service?

[Mark_Teichmann]

In my CAP project i try to read data from an external service like explained here:https://community.sap.com/t5/technology-blogs-by-sap/sap-cap-remote-services-sap-fiori-elements/ba-p...

This works fine until I read a complex structure like 

"amount": { "value": 12.7, "currency": "EUR" }

In my entity I defined an Amount type, but when trying to INSERT I get the error 'table abc has no column named amount'. 
The idea is to parse the result from the external service in function parseExpenses, but the error is thrown before it gets to this point.

class MyApi extends cds.RemoteService {
  async init() {
    this.reject(["CREATE", "UPDATE", "DELETE"], "*");

    this.before("READ", "Expenses", (req) => {
      try {
        req.query = `GET /expenses`;
      } catch (error) {
        req.reject(400, error.message);
      }
    });

    this.on("READ", "Expenses", async (req, next) => {
     
        const response = await next(req);
        var items = parseExpenses(response);
        return items;
      
    });

    super.init();
  }
}

What can I do to fix the issue? Define a flat entity without typed members and use a string that I have to parse elsewhere? Or any other solution here?
SAP Cloud Application Programming Model 

[Willem_Pardaens · Accepted solution]

Assuming you declared your type and entity like the below:

type Amount {
    value    : Decimal(10, 2);
    currency : String(3);
};

entity Expenses {
    amount : Amount;
};

Then the resulting data structure will be:

export function _ExpensAspect<TBase extends new (...args: any[]) => object>(Base: TBase) {
  return class Expens extends Base {
    declare amount_value?: number | null
    declare amount_currency?: string | null
    static readonly kind: 'entity' | 'type' | 'aspect' = 'entity';
    declare static readonly keys: __.KeysOf<Expens>;
    declare static readonly elements: __.ElementsOf<Expens>;
    declare static readonly actions: globalThis.Record<never, never>;
  };
}

Resulting in a table like this:

CREATE TABLE ns_Expenses (
  amount_value DECIMAL(10, 2),
  amount_currency NVARCHAR(3)
);

So there is no column 'amount'.

To resolve this, you should 'flatten' your data before doing an insert into the database, which you can either do manually (amount_value = amount.value), or I typically use a function for that:

export function flattenObject(object: Record<string, any> | null, parent?: string) {
    const flattened: Record<string, any> = {}
    for (const [k, v] of Object.entries(object ?? {})) {
        if (typeof v === 'object' && !Array.isArray(v) && v !== null) {
            Object.assign(flattened, flattenObject(v, parent ? `${parent}_${k}` : k))
        } else {
            flattened[parent ? `${parent}_${k}` : k] = v
        }
    }
    return flattened
}

[Mark_Teichmann · Accepted solution]

that explains the error message I get. I am just not sure where to flatten the object, as my on.READ handler was not reached. But the idea is understood.

[Willem_Pardaens · Accepted solution]

depends a bit on your overall code, but I noticed your query "expenses" and on handler "Expenses" are not the same case-wise. Could be due to that.

[Mark_Teichmann · Accepted solution]

the lowercase /expenses is the query of the remote service