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
}

Oh I plan on using an insert trigger to update the values following an insert. (There are only Inserts into this table. — RSH, May 10 '18 at 12:03
Why not filter out the `\` characters as your payload arrives? Is there a reason you're storing the raw content as-is? — David Makogon, May 10 '18 at 12:26
How would I do that? I tried inserting a Azure Function (this is the applicable part): metarequest.on('row', function(columns) { columns.forEach(function(column) { if(column.metadata.colName == "Gateway_Info") { streamInput.Gateway_Info_Formatted = JSON.Parse(column.value); } }); asset = mfg+mdl; conditionVariables(asset); }); connection.execSql(metarequest); — RSH, May 10 '18 at 12:40
That seemed to work when I logged it to the context.log, but it still appears escaped when querying Cosmos — RSH, May 10 '18 at 12:45

score 2 · Accepted Answer · answered May 16 '18 at 07:00

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:

Azure Cosmos DB SQL - how to unescape inner json property

1 Answers1

Linked