Edit

Query JSON columns in elastic tables (preview)

  • Article

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

Elastic table supports the JSON format for text columns. This column can be used to store schema-less arbitrary json as per 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 showing how to create a JSON column, see Create a column with Json format

Set Json column data

This example creates a row in contoso_SensorData elastic table with 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 contoso_SensorData elastic table with filters all rows which has energyconsumption.power greater than 5

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

More information: Azure Cosmos DB / NoSQL / Getting started with queries

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

ExecuteCosmosSqlQuery message has the following input parameters:

Name Type Description
QueryText string (Required) 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) Number of records returned in a single page.
PagingCookie string (Optional) Paging cookie to be used.
PartitionId string (Optional) Partitionid 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 Whether there are more records in the results.
Result String JSON with values with 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;}
}

More information:

Next steps

Bulk operations with elastic tables

See also

Use elastic tables
Create elastic tables using code
Use elastic tables
Bulk operations with elastic tables
Elastic table sample code (preview)