Edit

Query JSON columns in elastic tables

  • Article

[This topic is pre-release documentation and is subject to change.]

Elastic tables support the JavaScript Object Notation (JSON) format for text columns. These columns can be used to store schema-less arbitrary JSON according to application needs. You can use the ExecuteCosmosSQLQuery message to run any Cosmos SQL query directly against your elastic table and filter rows based on properties inside JSON.

For an example that shows how to create a JSON column, see Create a column with JSON format.

Set JSON column data

This example creates a row in the contoso_SensorData elastic table that has JSON data in the contoso_EnergyConsumption column.

public static Guid CreateWithJsonData(
    IOrganizationService service, 
    string deviceId, 
    ref string sessionToken)
{
    var entity = new Entity("contoso_sensordata")
    {
        Attributes =
        {
            { "contoso_deviceid", deviceId },
            { "contoso_sensortype", "Humidity" },
            { "contoso_value", 40 },
            { "contoso_energyconsumption", "{ \"power\": 0.55, \"powerUnit\":\"kWh\", \"voltage\": 2, \"voltageUnit\": \"kV\" }",
            { "contoso_timestamp", DateTime.UtcNow},
            { "partitionid", deviceId },
            { "ttlinseconds", 86400  }  // 86400  seconds in a day
        }
    };

    var request = new CreateRequest
    {
        Target = entity
    };

    var response = (CreateResponse)service.Execute(request);

    // Capture the session token
    sessionToken = response.Results["x-ms-session-token"].ToString();

    return response.id;
}

Query JSON column data

This example runs a query on the contoso_SensorData elastic table to filter for all rows where the energyconsumption.power value is more than 5.

All table columns can be queried by using a c.props prefix on the schema name of the columns. In this prefix, c is an alias or shorthand notation for the elastic table that is being queried. For example, the contoso_deviceid column in the contoso_sensordata table can be referenced in the Cosmos SQL query by using c.props.contoso_deviceid.

Learn about queries in Azure Cosmos DB for NoSQL.

The SDK for .NET doesn't yet have request and response classes for the ExecuteCosmosSqlQuery message. You can use the OrganizationRequest class and the OrganizationResponse class.

The ExecuteCosmosSqlQuery message has the following input parameters.

Name Type Description
QueryText String (Required) The Cosmos SQL query.
EntityLogicalName String (Required) The logical name of the table.
QueryParameters ParameterCollection (Optional) Values for any parameters that are specified in the QueryText parameter.
PageSize Long (Optional) The number of records to return on a single page.
PagingCookie String (Optional) The paging cookie to use.
PartitionId String (Optional) The Partitionid value to set the scope of the query.

ExecuteCosmosSqlQuery returns an Entity that is an open type. 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 A value that indicates whether there are more records in the results.
Result String JSON with values for the results. 
public static List<QueryResult> QueryJsonAttribute(IOrganizationService service)
{
    StringBuilder query = new();
    query.Append("select c.props.contoso_deviceid as deviceId, ");
    query.Append("c.props.contoso_timestamp as timestamp, ");
    query.Append("c.props.contoso_energyconsumption.power as power ");
    query.Append("from c where c.props.contoso_sensortype='Humidity' ");
    query.Append("and c.props.contoso_energyconsumption.power > 5");

    var request = new OrganizationRequest("ExecuteCosmosSqlQuery")
    {
        Parameters = {
            { "EntityLogicalName","contoso_sensordata" },
            { "QueryText", query.ToString() }
        }
    };

    OrganizationResponse response = service.Execute(request);

    // Deserialized query result into a class with expected schema.
    Entity result = (Entity)response.Results["Result"];
    return JsonConvert.DeserializeObject<List<QueryResult>>(result["Result"].ToString());
}

public class QueryResult
{
    [JsonProperty("deviceId")]
    public string DeviceId {get;set;}
    [JsonProperty("timestamp")]
    public DateTime Timestamp {get;set;}
    [JsonProperty("power")]
    public int Power {get;set;}
}

Next steps

Elastic table sample code

See also

Elastic tables for developers
Create elastic tables using code
Use elastic tables using code
Bulk operation messages
Elastic table sample code