Azure DIgital Twin, from C#, retrieve aggregate of relationship type

Question

Accessing ADT from .NET, I am trying to obtain the number of a particular relationship each of my nodes has.  For example, there are a number of people nodes and each person could have 1, 2 or more friends (a relationship of type friend). I want to retrieve a list of the node name (e.g. John) and the number of friends they have, e.g.: 

John, 3 Harry, 2 Frank, 4

However, when you use JOIN in ADT, it seems you have to combine it with WHERE and use a single $dtId.  So for example,

SELECT COUNT() FROM DIGITALTWINS twin JOIN parent RELATED twin.Friend WHERE twin.$dtId= 'John' 

Only retrives the number of Friend relationships for this one node. 

From the documentation, it seems it is possible to retrieve more than one node when using JOIN by using keyword IN e.g. (copying from docs) 

SELECT Room
FROM DIGITALTWINS Floor
JOIN Room RELATED Floor.contains
WHERE Floor.$dtId IN ['floor1','floor2', ..'floorn']
AND Room. Temperature > 72
AND IS_OF_MODEL(Room, 'dtmi:com:contoso:Room;1') 

However, when I apply this with variations such as: 

SELECT Count() AS what FROM DIGITALTWINS twin JOIN parent RELATED twin.HasDocument WHERE twin.$dtId IN ['John', 'Harry'] 

I don't get what I'm looking for. 

Ultimately, in c#. I would like to loop through the results with something like: await foreach (BasicDigitalTwin twin in queryResult) {}, saving to a List (where node contains nodename and numberoffriends properties. 

Any help much appreciated. Maybe I need something entirely different, not a join.

Answer 1

The IN operator would return the aggregate of all the relationships of the Azure Twin Id's specified in the clause. The SQL implementation of the use case you are looking for would need the use of GROUP BY operator. I could not find a similar approach in querying Azure Twin graphs.

However, you can loop the Twin nodes and get the relationships per node using C#. I have pushed the values to a Dictionary as it made more sense to have a key value pair. You can modify the code to add it to a list as well. Here is the code I have used to achieve this.

static async Task Main(string[] args)
    {
        Console.WriteLine("Hello World!");

        string adtInstanceUrl = "https://<DigitalTwinHostName>";

        var credential = new DefaultAzureCredential();
        var client = new DigitalTwinsClient(new Uri(adtInstanceUrl), credential);
        Console.WriteLine($"Service client created – ready to go");

        string twinname;
        string twinQuery = "SELECT * FROM DIGITALTWINS DT WHERE IS_OF_MODEL(DT, 'dtmi:example:GraphModel;2')";
        // Run a query for all twins
        string query;
        Dictionary<string, int> relationship = new Dictionary<string, int>();
        AsyncPageable<BasicDigitalTwin> twinResult = client.QueryAsync<BasicDigitalTwin>(twinQuery);

        await foreach (BasicDigitalTwin twin in twinResult)
        {
            Console.WriteLine(JsonSerializer.Serialize(twin.Id));
            twinname = twin.Id;
            query = "SELECT COUNT() FROM DIGITALTWINS twin JOIN DT RELATED twin.contains WHERE twin.$dtId = '" + twinname + "'";
            AsyncPageable<JsonElement> queryResult = client.QueryAsync<JsonElement>(query);
            await foreach (JsonElement twinCount in queryResult)
            {
                Console.WriteLine("Total relationships for the twin " + twinname + " is " + twinCount.GetProperty("COUNT"));
                try
                {
                    relationship.Add(twinname, int.Parse(twinCount.GetProperty("COUNT").GetRawText()));
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            Console.WriteLine("---------------");
        }
        Console.WriteLine();
        foreach (KeyValuePair<string, int> kvp in relationship)
        {
            Console.WriteLine("Key = {0}, Value = {1}",
                kvp.Key, kvp.Value);
        }

}

You have to modify the parameters twinQuery and queryin the above code to get the twins from the corresponding model and the count based on the appropriate relationship.

Please find the below model graph I have created on the Azure Digital Twin Explorer.

Here is the output I get from the above code for the relationship count of all the nodes.

Hope this helps!