1

I have data saved in a database, Microsoft SQL Server 2014.

What I need to do is to download specific data - query written and is working - to analyze these data. One of the columns is in json format, with following structure

{"name":{"meta_id","actualValues":{"item1":{"name":name,"value":value},
    "item_2":{"name":name,"value":value_2},
    "item_3":{"name":name,"value":value_3},
    "item_n":{"name":name,"value":value_n},},

    "setValues:{"item1":{"name":name,"value":value},
    "item_2":{"name":name,"value":value_2},
    "item_3":{"name":name,"value":value_3},
    "item_n":{"name":name,"value":value_n},}
}

The nested json file includes several hundreds of parameter and corresponding values

I managed to connect my database with python and control the query inside python. But if I want to analyze my data I need to convert the structure from the nested json to relational data. Doing this in python takes a very long time for few thousand rows. So I tried to convert the json inside the Database. The expected output shall look like this

enter image description here

The blue columns shall represent "actualValues" and the yellow columns the "setValues". The name_X shall represent the name for item_x I found the following link which gives some help about json_value and json_query.

However this functions only work in SQL Server higher than 2016. Unfortunately I just have the 2014 version.

Is there any way to convert a nested json into relational data, i.e. into a nice table, without a time consuming process?

For any kind of help I would be thankful

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SMS
  • 348
  • 2
  • 13
  • When you say relational data, do you mean a dataframe? like a spreadsheet etc? – Buckeye14Guy Oct 22 '19 at 13:36
  • yes :), exactly – SMS Oct 22 '19 at 13:37
  • so is this example one row with many columns or multiple rows? can you provide the expected output? – Buckeye14Guy Oct 22 '19 at 13:41
  • thanks for feedback: I updated my question Cheers – SMS Oct 22 '19 at 14:05
  • It's probably best to not have two columns of the same name. Plus you just care about value? what happens to the name part of your items? – Buckeye14Guy Oct 22 '19 at 14:13
  • oh you are right. I did a mistake. What I want is a column with name as header and value as value – SMS Oct 22 '19 at 14:26
  • Sorry I guess I misunderstood the question. But ultimately if you can only do it with a higher version you may have to do just that. In any case, you will have to do some processing in python if you want it as a dataframe – Buckeye14Guy Oct 22 '19 at 14:42
  • 1
    Ye. I did the pre-processing which takes much time. I tried to solve the issue for sql version 2014 by myself but I cannot manage that and need to upgrade to 2016. Thanks anyway. At least I know now that I am not that stupid :) – SMS Oct 22 '19 at 14:52

0 Answers0