ExecuteCosmosSqlQuery Function

  • Reference
Namespace:
Microsoft.Dynamics.CRM

Executes a query against data in JSON columns

Parameters

Parameters allow for data to be passed to the function.

Name Type Nullable Unicode Description
QueryText
 Edm.String False False

Cosmos sql query.
EntityLogicalName
 Edm.String False False

LogicalName of the elastic table
QueryParameters
 ParameterCollection True True

Values for any parameters that are specified in the QueryText parameter.
PageSize
 Edm.Int64 True True

Number of records returned in a single page.
PagingCookie
 Edm.String True False

Paging cookie to be used.
PartitionId
 Edm.String True False

Partitionid to set the scope of the query.

Return Type

Type Nullable Description
crmbaseentity False The ExecuteCosmosSqlQuery function returns the following value.

Remarks

Use Structured Query Language (SQL) as a JSON query language to compose the query used in the QueryText parameter. More information: Azure Cosmos DB / NoSQL / Getting started with queries

When your QueryText includes named parameter references using '@', they must be included in the QueryParameters property as a parameter collection. A ParameterCollection Complex Type is a collection of Keys and Values. The values are defined using Object Complex Type so that you can specify the type of parameter using .NET names, such as System.String or System.Int32.

Example

Request

GET [Organization Uri]/api/data/v9.2/ExecuteCosmosSqlQuery(QueryText=@p1,EntityLogicalName=@p2,QueryParameters=@p3,PageSize=@p4,PartitionId=@p5)?@p1='select c.props.contoso_deviceid as deviceId, c.props.contoso_timestamp as timestamp, c.props.contoso_energyconsumption.power as power from c where c.props.contoso_sensortype=@sensortype and c.props.contoso_energyconsumption.power > @power'
&@p2='contoso_sensordata'
&@p3={"Keys":["@sensortype","@power"],"Values":[{"Type":"System.String","Value":"Humidity"},{"Type":"System.Int32","Value":"5"}]}
&@p4=50
&@p5='Device-ABC-1234'
MSCRM.SessionToken: 207:8#142792107#7=-1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json

Response

HTTP/1.1 200 OK
OData-Version: 4.0

{
  "@odata.context": "[Organization Uri]/api/data/v9.2/$metadata#expando/$entity",
  "@odata.type": "#Microsoft.Dynamics.CRM.expando",
  "PagingCookie": "W3sidG9rZW4iOiIrUklEOn5DVm9OQUpJaWRuTjBJajRBQUFBd0R3PT0jUlQ6MSNUUkM6NTAjSVNWOjIjSUVPOjY1NTUxI1FDRjo4I0ZQQzpBWFFpUGdBQUFEQVBveUkrQUFBQU1BOD0iLCJyYW5nZSI6eyJtaW4iOiIxNDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMCIsIm1heCI6IjE0ODAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwIn19XQ==",
  "HasMore": true,
  "Result@odata.type": "#Collection(Microsoft.Dynamics.CRM.expando)",
  "Result": [
    {
      "@odata.type": "#Microsoft.Dynamics.CRM.expando",
      "deviceId": "Device-ABC-1234",
      "power": 6
    },
    [ 49 records truncated for brevity]
  ]
}

The return type is a crmbaseentity, but actually it represents an expando.

This entity has the following attributes:

Name Type Description
PagingCookie String A value to set for subsequent requests when there are more results.
HasMore Bool Whether there are more records in the results.
Result String JSON with values with the results.

Despite returning expando, because the return value isn't dynamic, it is a closed type. You will need to parse the response.

When you have specified a PageSize that is less than the total number of results matching the criteria of your query, you can send additional requests with the same query by passing the PagingCookie value in subsequent requests. For example:

Request

GET [Organization Uri]/api/data/v9.2/ExecuteCosmosSqlQuery(QueryText=@p1,EntityLogicalName=@p2,QueryParameters=@p3,PageSize=@p4,PagingCookie=@p5,PartitionId=@p6)?@p1='select c.props.contoso_deviceid as deviceId, c.props.contoso_timestamp as timestamp, c.props.contoso_energyconsumption.power as power from c where c.props.contoso_sensortype=@sensortype and c.props.contoso_energyconsumption.power > @power'
&@p2='contoso_sensordata'
&@p3={"Keys":["@sensortype","@power"],"Values":[{"Type":"System.String","Value":"Humidity"},{"Type":"System.Int32","Value":"5"}]}
&@p4=50
@p5='W3sidG9rZW4iOiIrUklEOn5DVm9OQUpJaWRuTjBJajRBQUFBd0R3PT0jUlQ6MSNUUkM6NTAjSVNWOjIjSUVPOjY1NTUxI1FDRjo4I0ZQQzpBWFFpUGdBQUFEQVBveUkrQUFBQU1BOD0iLCJyYW5nZSI6eyJtaW4iOiIxNDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMCIsIm1heCI6IjE0ODAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwIn19XQ=='
&@p6='Device-ABC-1234'
MSCRM.SessionToken: 207:8#142792107#7=-1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json

You can continue to get paged results until the HasMore property returns false.

More information:

See also