0

After getting some data from the azure consumption api, there is a field called 'tags' which contains some predefined tags and their values. I am trying to come up with a way to model these in a SQL Server db to allow for the inclusion of tags in reports.

However, an issue I am having, is the difficulty caused by a significant difference in syntax. I need to extrapolate each tag from the tags field to show them individually.

A usual 'Tags' field will contain several tags with their headers and their values something like this:

{  
     "SA": "BudgetForecastYear", 

     "SL": "NUMBER #####",  

     "CC": "Collection",  

     "BU": "BusinessName"
}

However, in some other cases the tag is an object of objects. As below:

{ 
      "impact": "{\
           "DeploymentZone\":\"tbc\"
       }",  

      "contact": "[
                   {
                    \"Service\":\"emailaddress\"
                    }, 

                   {
                    \"Technical\":\"emailaddress\"
                    }
      ]",  

      "organisation": "{
                    \"SA\":\"BudgetForecastYear\",
                    \"SL\":\"NUMBER ###\",
                    \"CC\":\"Collection\",
                    \"BU\":\"BusinessName\"
                     }"
}

Given that the rows of data are in the millions and there is no way for me to predict whether the 'tags' field will contain one object or an object of objects (it can also be empty), I am concerned that devising a reliable model to extract each tag may be impossible.

My question is this: If there is no consistent value to the tags, what is the best course of action for me to take?

FYI: Every record's 'tags' field follows one of these two basic structures. I'm happy to provide more information if needed.

  • Your issue is trying to store document data in a relational database. You might help us by expanding on how the tags will be useful in reports with some examples. A simple approach might be to flatten the JSON data, but I don't know if that would provide value to the person viewing the report. – Dan Wilson May 7 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.