Is your feature request related to a problem? Please describe.
We have a custom api which returns an expando entity which in webapi returns brilliantly. Making the same call using Sql4Cds is triggering issues in extracting the information in a usable format.
WebAPI returns the data in:
{
"column1":"value1",
"column2":"value2"
}The serialization within Sql4Cds is resulting in something like the following:
{
"Attributes":[
{"Key":"column1", "Value": "value1"},
{"Key":"column2", "Value": "value2"},
]
}Getting at specific properties doesn't work because JsonPath syntax doesn't like $.Attributes[?(@.Key=='column1')].Value.
Describe the solution you'd like
Appreciate that the JsonPath support mirrors that of TSQL, so something on that isn't an option. Maybe using a hint to control the Entity->Json conversion method???
Describe alternatives you've considered
Gone through copilot and it's given a mess of recursive Cross apply openjson calls but had issues between getting scalar values and objects.
Additional context
The webapi response looks like the following:
{
"@odata.context": "https://REDACTED.crm11.dynamics.com/api/data/v9.2/$metadata#expando/$entity",
"@odata.type": "#Microsoft.Dynamics.CRM.expando",
"Error": false,
"Users@odata.type": "#Collection(Microsoft.Dynamics.CRM.crmbaseentity)",
"Users": [
{
"@odata.type": "#Microsoft.Dynamics.CRM.expando",
"OneDriveRoot_Error": false,
"OneDriveMSFP_Error": false,
"user": {
"@odata.type": "#Microsoft.Dynamics.CRM.systemuser",
"systemuser": "a81ca1d0-0b4f-ef11-a316-000d3a0cd126",
"__DisplayName__": "John Doe"
}
},
{
"@odata.type": "#Microsoft.Dynamics.CRM.expando",
"OneDriveRoot_Error": false,
"OneDriveMSFP_Error": false,
"user": {
"@odata.type": "#Microsoft.Dynamics.CRM.systemuser",
"systemuser": "450b303e-ff46-ef11-a317-0022481b5eda",
"__DisplayName__": "Jane Doe"
}
}
]
}The json we get from sql4cds below :
{
"Error": false,
"Users": {
"Entities": [
{
"LogicalName": null,
"Id": "00000000-0000-0000-0000-000000000000",
"Attributes": [
{
"Key": "user",
"Value": {
"Id": "a81ca1d0-0b4f-ef11-a316-000d3a0cd126",
"LogicalName": "systemuser",
"Name": "John Doe",
"KeyAttributes": [],
"RowVersion": null
}
},
{
"Key": "OneDriveRoot_Error",
"Value": false
},
{
"Key": "OneDriveMSFP_Error",
"Value": false
}
],
"EntityState": null,
"FormattedValues": [],
"RelatedEntities": [],
"RowVersion": null,
"KeyAttributes": []
},
{
"LogicalName": null,
"Id": "00000000-0000-0000-0000-000000000000",
"Attributes": [
{
"Key": "user",
"Value": {
"Id": "450b303e-ff46-ef11-a317-0022481b5eda",
"LogicalName": "systemuser",
"Name": "Jane Doe",
"KeyAttributes": [],
"RowVersion": null
}
},
{
"Key": "OneDriveRoot_Error",
"Value": false
},
{
"Key": "OneDriveMSFP_Error",
"Value": false
}
],
"EntityState": null,
"FormattedValues": [],
"RelatedEntities": [],
"RowVersion": null,
"KeyAttributes": []
}
],
"MoreRecords": false,
"PagingCookie": null,
"MinActiveRowVersion": null,
"TotalRecordCount": 0,
"TotalRecordCountLimitExceeded": false,
"EntityName": null
},
"Users@odata.type": "EntityCollection"
}
Is your feature request related to a problem? Please describe.
We have a custom api which returns an expando entity which in webapi returns brilliantly. Making the same call using Sql4Cds is triggering issues in extracting the information in a usable format.
WebAPI returns the data in:
{ "column1":"value1", "column2":"value2" }The serialization within Sql4Cds is resulting in something like the following:
{ "Attributes":[ {"Key":"column1", "Value": "value1"}, {"Key":"column2", "Value": "value2"}, ] }Getting at specific properties doesn't work because JsonPath syntax doesn't like
$.Attributes[?(@.Key=='column1')].Value.Describe the solution you'd like
Appreciate that the JsonPath support mirrors that of TSQL, so something on that isn't an option. Maybe using a hint to control the Entity->Json conversion method???
Describe alternatives you've considered
Gone through copilot and it's given a mess of recursive Cross apply openjson calls but had issues between getting scalar values and objects.
Additional context
The webapi response looks like the following:
{ "@odata.context": "https://REDACTED.crm11.dynamics.com/api/data/v9.2/$metadata#expando/$entity", "@odata.type": "#Microsoft.Dynamics.CRM.expando", "Error": false, "Users@odata.type": "#Collection(Microsoft.Dynamics.CRM.crmbaseentity)", "Users": [ { "@odata.type": "#Microsoft.Dynamics.CRM.expando", "OneDriveRoot_Error": false, "OneDriveMSFP_Error": false, "user": { "@odata.type": "#Microsoft.Dynamics.CRM.systemuser", "systemuser": "a81ca1d0-0b4f-ef11-a316-000d3a0cd126", "__DisplayName__": "John Doe" } }, { "@odata.type": "#Microsoft.Dynamics.CRM.expando", "OneDriveRoot_Error": false, "OneDriveMSFP_Error": false, "user": { "@odata.type": "#Microsoft.Dynamics.CRM.systemuser", "systemuser": "450b303e-ff46-ef11-a317-0022481b5eda", "__DisplayName__": "Jane Doe" } } ] }The json we get from sql4cds below :
{ "Error": false, "Users": { "Entities": [ { "LogicalName": null, "Id": "00000000-0000-0000-0000-000000000000", "Attributes": [ { "Key": "user", "Value": { "Id": "a81ca1d0-0b4f-ef11-a316-000d3a0cd126", "LogicalName": "systemuser", "Name": "John Doe", "KeyAttributes": [], "RowVersion": null } }, { "Key": "OneDriveRoot_Error", "Value": false }, { "Key": "OneDriveMSFP_Error", "Value": false } ], "EntityState": null, "FormattedValues": [], "RelatedEntities": [], "RowVersion": null, "KeyAttributes": [] }, { "LogicalName": null, "Id": "00000000-0000-0000-0000-000000000000", "Attributes": [ { "Key": "user", "Value": { "Id": "450b303e-ff46-ef11-a317-0022481b5eda", "LogicalName": "systemuser", "Name": "Jane Doe", "KeyAttributes": [], "RowVersion": null } }, { "Key": "OneDriveRoot_Error", "Value": false }, { "Key": "OneDriveMSFP_Error", "Value": false } ], "EntityState": null, "FormattedValues": [], "RelatedEntities": [], "RowVersion": null, "KeyAttributes": [] } ], "MoreRecords": false, "PagingCookie": null, "MinActiveRowVersion": null, "TotalRecordCount": 0, "TotalRecordCountLimitExceeded": false, "EntityName": null }, "Users@odata.type": "EntityCollection" }