Store D2C messages in Azure SQL database

Question

I am trying to store sensor readings received from Cloudrail.Box as a JSON payload into Azure SQL database. The edge function I'm currently using outputs the message(shown below) in batches based on the set frequency. { "Temp_V_Raw": 0.789, "Pressure_V_Raw": 0.006, "Temp_C": 23.67, "Pressure_Bar": 1.8450718792014, "Timestamp": 1617990070392, "Date": "Fri Apr 09 2021 19:41:10 GMT+0200 (CEST)" }

HOw do I store this in a tabular format in Azure SQL database?

Answer 1

I would suggest you create a stored procedure to deal with the json data, then you pass the JSON data into the Azure SQL database by a stored procedure.

Ref the stored procedure example:

CREATE TABLE dbo.SystemRecord(
    RecordedDateTime        datetime2(0) NOT NULL,
    RecordedDateTimeLocal   datetime2(0) NOT NULL,
    CpuPctProcessorTime     smallint     NOT NULL,
    MemAvailGbytes          smallint     NOT NULL
)

CREATE PROCEDURE dbo.InsertSystemRecordData
 
@json NVARCHAR(max)

AS
BEGIN
 
INSERT INTO dbo.SystemRecord (
  [RecordedDateTime]
, [RecordedDateTimeLocal]
, [CpuPctProcessorTime]
, [MemAvailGbytes])
 
    SELECT
        RecordedDateTime
       ,RecordedDateTimeLocal
       ,CpuPctProcessorTime
       ,MemAvailGbytes
    FROM OPENJSON(@json)
    WITH (
      RecordedDateTime      DATETIME2(0) '$.dateTime'
    , RecordedDateTimeLocal DATETIME2(0) '$.dateTimeLocal'
    , CpuPctProcessorTime   SMALLINT     '$.cpuPctProcessorTime'
    , MemAvailGbytes        SMALLINT     '$.memAvailGbytes'
    ) AS jsonValues
 
END

EXEC dbo.InsertSystemRecordData @json ='{"dateTime":"2018-03-19T15:15:40.222Z","dateTimeLocal":"2018-03-19T11:15:40.222Z","cpuPctProcessorTime":"0","memAvailGbytes":"28"}'

You could ref these links:

1. Pass json_value into stored procedure
2. How to Insert JSON Data into SQL Server Database

HTH.