Azure Question posted in
The official documentation to get you started can be found here.

Azure Steam Analytics Query

I want to store the below JSON data to the Azure table. Here the input and the output for Azure Stream Analytics are Azure IoT Hub and table respectively.

[
  {
    "device1": [
      {
        "name": "temperature",
        "value": 50
      },
      "name": "voltage",
      "value": 220
    }
  ],
  "device2": [
    {
      "name": "temperature",
      "value": 10
    },
    "name": "voltage",
    "value": 200
  }
]
}
]

final result would look like below image

device list table
device info table

I tried with simple json formate

  [{
    "messageId": messageId,
    "name": "temperature"
   }]



SELECT
    i.messageId AS messageId,
    i.name AS name,
    i.PartitionId,
    i.EventProcessedUtcTime,
    i.EventEnqueuedUtcTime
INTO
    [table]
FROM
    [input] i

But for nested and dynamic key data i am confused.

Tags:

2

Answers


  1. 0

    Here’s how you can flatten the JSON data and insert it into an Azure table using C# and Azure Table Storage SDK:

    using System;
using System.Collections.Generic;
using Microsoft.Azure.Cosmos.Table;
using Newtonsoft.Json;

public class DeviceEntity : TableEntity
{
    public string Name { get; set; }
    public int Value { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        string json = @"
        [
            {
                'device1': [
                    {
                        'name': 'temperature',
                        'value': 50
                    },
                    {
                        'name': 'voltage',
                        'value': 220
                    }
                ]
            },
            {
                'device2': [
                    {
                        'name': 'temperature',
                        'value': 10
                    },
                    {
                        'name': 'voltage',
                        'value': 200
                    }
                ]
            }
        ]";

        var devices = 
        JsonConvert.DeserializeObject<List<Dictionary<string, 
        List<Dictionary<string, object>>>>>(json);

        List<DeviceEntity> entities = new List<DeviceEntity>();

        foreach (var device in devices)
        {
            foreach (var keyValuePair in device)
            {
                string deviceId = keyValuePair.Key;
                foreach (var sensorData in keyValuePair.Value)
                {
                    string name = sensorData["name"].ToString();
                    int value = Convert.ToInt32(sensorData["value"]);

                    var entity = new DeviceEntity
                    {
                        PartitionKey = deviceId,
                        RowKey = name,
                        Name = name,
                        Value = value
                    };
                    entities.Add(entity);
                }
            }
        }

        // Connect to Azure Table Storage
        string connectionString = "DefaultEndpointsProtocol=https;AccountName=<your-account-name>;AccountKey=<your-account-key>;TableEndpoint=https://<your-account-name>.table.cosmos.azure.com:443/;";
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
        CloudTableClient tableClient = storageAccount.CreateCloudTableClient(new TableClientConfiguration());
        CloudTable table = tableClient.GetTableReference("YourTableName");

        // Create the table if it doesn't exist
        table.CreateIfNotExists();

        // Batch insert entities into the table
        var batchOperation = new TableBatchOperation();
        foreach (var entity in entities)
        {
            batchOperation.InsertOrMerge(entity);
        }
        table.ExecuteBatch(batchOperation);

        Console.WriteLine("Data inserted into Azure Table Storage successfully.");
    }
}

    Replace "your-account-name", "your-account-key", and "YourTableName" with your Azure Storage account credentials and the name of your Azure Table.

    Login or Signup to reply.
  2. 0
    • The Json that you have provided seems to be invalid.

    For your case, you can use cross apply and getarrayelements to get each element of the array into a separate row.

    Below is the similar sample Json, I tried with:

    [
  {
    "device1": [
      {
        "name": "temperature",
        "value": 50
      },
      {
        "name": "voltage",
        "value": 220
      }
    ]
  }
]

    Azure Stream analytics query to flatten this Json array:

    select d1.arrayvalue.name as name, d1.arrayvalue.value as value, device='device1'
from input
cross apply getArrayelements(device1) as d1

    Output:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search