Azure Digital Twins query language reference: SELECT clause

This document contains reference information on the SELECT clause for the Azure Digital Twins query language.

The SELECT clause is the first part of a query. It specifies the list of columns that the query will return.

This clause is required for all queries.

SELECT *

Use the * character in a select statement to project the digital twin document as is, without assigning it to a property in the result set.

Syntax

SELECT *
--FROM ...

Returns

The set of properties that are returned from the query.

Example

The following query returns all digital twins in the instance.

SELECT *
FROM DIGITALTWINS

SELECT columns with projections

You can use projections in the SELECT clause to choose which columns a query will return. You can specify named collections of twins and relationships, or properties of twins and relationships.

Projection is now supported for both primitive properties and complex properties.

Syntax

To project a collection:

SELECT <twin-or-relationship-collection>

To project a property:

SELECT <twin-or-relationship-collection>.<property-name>

Returns

A collection of twins, properties, or relationships specified in the projection.

If a property included in the projection isn't present for a particular data row, the property will similarly not be present in the result set. For an example of this behavior, see Project property example: Property not present for a data row.

Examples

Example scenario

For the following examples, consider a twin graph that contains the following data elements:

• A Factory twin called FactoryA
  • Contains a property called name with a value of FactoryA
• A Consumer twin called Contoso
  • Contains a property called name with a value of Contoso
• A consumerRelationship relationship from FactoryA to Contoso, called FactoryA-consumerRelationship-Contoso
  • Contains a property called managedBy with a value of Jeff

Here's a diagram illustrating this scenario:

Project collection example

Below is an example query that projects a collection from this graph. The following query returns all digital twins in the instance, by naming the entire twin collection T and projecting T as the collection to return.

SELECT T
FROM DIGITALTWINS T

Here's the JSON payload that's returned from this query:

{
  "value": [
    {
      "result": [
        {
          "T": {
            "$dtId": "FactoryA",
            "$etag": "W/\"d22267a0-fd4f-4f6b-916d-4946a30453c9\"",
            "$metadata": {
              "$model": "dtmi:contosocom:DigitalTwins:Factory;1",
              "name": {
                "lastUpdateTime": "2021-04-19T17:15:54.4977151Z"
              }
            },
            "name": "FactoryA"
          }
        },
        {
          "T": {
            "$dtId": "Contoso",
            "$etag": "W/\"a96dc85e-56ae-4061-866b-058a149e03d8\"",
            "$metadata": {
              "$model": "dtmi:com:contoso:Consumer;1",
              "name": {
                "lastUpdateTime": "2021-04-19T17:16:30.2154166Z"
              }
            },
            "name": "Contoso"
          }
        }
      ]
    }
  ],
  "continuationToken": "null"
}

Project with JOIN example

Projection is commonly used to return a collection specified in a JOIN. The following query uses projection to return the data of the Consumer, Factory, and Relationship. For more about the JOIN syntax used in the example, see Azure Digital Twins query language reference: JOIN clause.

SELECT Consumer, Factory, Relationship
FROM DIGITALTWINS Factory
JOIN Consumer RELATED Factory.consumerRelationship Relationship
WHERE Factory.$dtId = 'FactoryA'

Here's the JSON payload that's returned from this query:

{
  "value": [
    {
      "result": [
        {
          "Consumer": {
            "$dtId": "Contoso",
            "$etag": "W/\"a96dc85e-56ae-4061-866b-058a149e03d8\"",
            "$metadata": {
              "$model": "dtmi:com:contoso:Consumer;1",
              "name": {
                "lastUpdateTime": "2021-04-19T17:16:30.2154166Z"
              }
            },
            "name": "Contoso"
          },
          "Factory": {
            "$dtId": "FactoryA",
            "$etag": "W/\"d22267a0-fd4f-4f6b-916d-4946a30453c9\"",
            "$metadata": {
              "$model": "dtmi:contosocom:DigitalTwins:Factory;1",
              "name": {
                "lastUpdateTime": "2021-04-19T17:15:54.4977151Z"
              }
            },
            "name": "FactoryA"
          },
          "Relationship": {
            "$etag": "W/\"f01e07c1-19e4-4bbe-a12d-f5761e86d3e8\"",
            "$relationshipId": "FactoryA-consumerRelationship-Contoso",
            "$relationshipName": "consumerRelationship",
            "$sourceId": "FactoryA",
            "$targetId": "Contoso",
            "managedBy": "Jeff"
          }
        }
      ]
    }
  ],
  "continuationToken": "null"
}

Project property example

Here's an example that projects a property. The following query uses projection to return the name property of the Consumer twin, and the managedBy property of the relationship.

SELECT Consumer.name, Relationship.managedBy
FROM DIGITALTWINS Factory
JOIN Consumer RELATED Factory.consumerRelationship Relationship
WHERE Factory.$dtId = 'FactoryA'

Here's the JSON payload that's returned from this query:

{
  "value": [
    {
      "result": [
        {
          "managedBy": "Jeff",
          "name": "Contoso"
        }
      ]
    }
  ],
  "continuationToken": "null"
}

Project property example: Property not present for a data row

If a property included in the projection isn't present for a particular data row, the property will similarly not be present in the result set.

Consider for this example a set of twins that represent people. Some of the twins have ages associated with them, but others don't.

Here's a query that projects the name and age properties:

SELECT name, age 
FROM DIGITALTWINS

The result might look something like this, with the age property missing from some twins in the result where the twins don't have this property.

{
  "value": [
    {
      "result": [
        {
          "name": "John",
          "age": 27
        },
        {
          "name": "Keanu"
        }
      ]
    }
  ],
  "continuationToken": "null"
}

SELECT COUNT

Use this method to count the number of items in the result set and return that number.

Syntax

SELECT COUNT()

Arguments

None.

Returns

An int value.

Example

The following query returns the count of all digital twins in the instance.

SELECT COUNT()
FROM DIGITALTWINS

The following query returns the count of all relationships in the instance.

SELECT COUNT()
FROM RELATIONSHIPS

SELECT TOP

Use this method to return only some of the top items that meet the query requirements.

Syntax

SELECT TOP(<number-of-return-items>)

Arguments

An int value specifying the number of top items to select.

Returns

A collection of twins.

Example

The following query returns only the first five digital twins in the instance.

SELECT TOP(5)
FROM DIGITALTWINS