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.
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.
Obs!
SELECT *
is only valid syntax when the query does not use a JOIN
. For more information on queries using JOIN
, see Azure Digital Twins query language reference: JOIN clause.
SELECT *
--FROM ...
The set of properties that are returned from the query.
The following query returns all digital twins in the instance.
SELECT *
FROM DIGITALTWINS
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.
To project a collection:
SELECT <twin-or-relationship-collection>
To project a property:
SELECT <twin-or-relationship-collection>.<property-name>
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.
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 ofFactoryA
- Contains a property called
- A Consumer twin called Contoso
- Contains a property called
name
with a value ofContoso
- Contains a property called
- A consumerRelationship relationship from FactoryA to Contoso, called
FactoryA-consumerRelationship-Contoso
- Contains a property called
managedBy
with a value ofJeff
- Contains a property called
Here's a diagram illustrating this scenario:
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"
}
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"
}
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"
}
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"
}
Use this method to count the number of items in the result set and return that number.
SELECT COUNT()
None.
An int
value.
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
Use this method to return only some of the top items that meet the query requirements.
SELECT TOP(<number-of-return-items>)
An int
value specifying the number of top items to select.
A collection of twins.
The following query returns only the first five digital twins in the instance.
SELECT TOP(5)
FROM DIGITALTWINS