Azure Cosmos DB SQL - how to unescape inner json property

Question

Okay I have spent hours trying to get this to work.

I have an inner property in my json that is a json object. However when a device sends me the data the json in Gateway_Info is delimited. This makes querying that inner object impossible (using dot notation)

Is there a way to remove the \ character from that json string to make it valid json?

SELECT * FROM c

 {
        "Asset_Key": "1",
        "Defrost_Cycles": 0,
        "Freeze_Cycles": 0,
        "Float_Switch_Raw_ADC": 0,
        "Bin_status": 0,
        "Line_Voltage": 0,
        "ADC_Evaporator_Temperature": 0,
        "Mem_Sw": 0,
        "Freeze_Timer": 0,
        "Defrost_Timer": 0,
        "Water_Flow_Switch": 0,
        "ADC_Mid_Temperature": 0,
        "ADC_Water_Temperature": 0,
        "Ambient_Temperature": 1,
        "Mid_Temperature": 1,
        "Water_Temperature": 1,
        "Evaporator_Temperature": 1,
        "Gateway_Info": "{\"temp_sensor\":0.00,\"temp_pcb\":82.00,\"gw_uptime\":123912.00,\"gw_fw_version\":\"0.0.0\",\"gw_fw_version_git\":\"1-dirty\",\"gw_sn\":\"30\",\"heap_free\":10648.00,\"gw_sig_csq\":19.00,\"gw_sig_quality\":1,\"wifi_sig_strength\":0.00,\"wifi_resets\":0.00,\"modem_sim_iccid\":\"1\",\"modem_meid\":\"1\",\"modem_model\":\"1\"}",
        "ADC_Ambient_Temperature": 0
}

Answer 1

PreTrigger in cosmos db need to be defined in the code, since your data is sent by the device, so it won't work through PreTrigger.

So, as you mentioned in your comment ,I suggest you using Azure Function CosmosTrigger to process per document before it is inserted into cosmos db.

My sample document:

My Azure Function CosmosTrigger code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace ProcessJson
{
    public class Class1
    {
        [FunctionName("DocumentUpdates")]
        public static void Run(
        [CosmosDBTrigger("db", "item", ConnectionStringSetting = "myCosmosDB")]
        IReadOnlyList<Document> documents,
        TraceWriter log)
        {
            String endpointUrl = "***";
            String authorizationKey = "***";
            String databaseId = "db";
            String collectionId = "item";

            DocumentClient client = new DocumentClient(new Uri(endpointUrl), authorizationKey); ;

            Document doc = documents[0];

            string gateway = doc.GetPropertyValue<string>("gateway");
            JObject o = JObject.Parse(gateway);

            doc.SetPropertyValue("gateway",o);

            client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(databaseId, collectionId, doc.Id), doc);

            log.Verbose("document Id " + doc.Id);
        }
    }
}

Insert Result: