Dynamic Schema for a table

[UpmaR]

Does ServiceNow support Schema on read with SQL like 

SELECT CAST(order_amount AS INT)
FROM Order_table

We need to do type casting in memory where Order amount is stored as String in the database. Our goal is something like this which identifies the field type in memory

gr.addQuery('CAST(u_order_amount AS INT)', '>', 1000); //not exactly this syntax, this is just an example

Client wants to implement a Datalake table in ServiceNow with all the fields as string type or data is stored in a JSON field and while accessing for query and filerting, they wants to do casting for field type. Mapping between logical name of the field, backend name of the field and field type will be stored in the mapping table. Please let me know if field casting is passible in Servicenow in semantic layer without defining proper field types in the table?

#Dynamic Schema # Integration # Data Lake #ITSM @Dynamic Schema

[Joe Walters · Accepted solution]

Hi UpmaR. The short answer is no (or at least, I can't think of any way to do it).

If you want to have the full menu of options available when you query INT fields, you'll need to define a typed INT field and populate the converted values into it prior to executing your query.