Create new JSON model using the existing JSON dynamically in dotnet core web API

Question

I have been stuck on this for about a week now. I am receiving a json response to my API as follows, it consists of question IDs(32 character long string) and the related answers, for some questions, the answers are predefined hence some answers has IDs as well. Depend on the question there could be multiple answers or one answer.

{
  "b07e33061bd31c8d29095f44d9898826": {
    "answer": "abc"
  },
  "c4edd6e206973168fb15ced212397197": {
    "answer": "def"
  },
  "f35270e30bafb94a30f1b22786f748a6": {
    "selectedAnswers": [
      "b66c043042586e893a147d4b0ba53bdf",
      "85eb345f9ad8035bb8928faa4b2b741d",
      "071475576d1925e595c39e47ea00e45c"
    ]
  },
  "fc6b41df07db039e3903665e0669f8e9": {
    "58ff182f96dd321a24bcd08a982566c6": "11b5da0633d22d584f58f85c21804dbf",
    "6fee5fc87f6515467f870015b84d8975": "5467a55ce17aa356898592a60d06964e",
    "7281d97f90af65eae568320ce3b1e510": "59e7d9c42190c6882d28c8705c2b3cca",
    "a69fe0b53807a01ff8f0c83069c74ecf": "92323316ddacdd7e0db911e12ee9ec20",
    "d4e0e900c9c960a9b8cd994ea984c7d6": "dfe0109763b30f5be4055c1097be6648"
  },
  "60ed2fc37d5207b03ad2a72c6ae56793": {
    "answer": "hij"
  },
  "593dfbd2317debd60e74c321512fe77a": {
    "1c99416b1c016fdf0ce0b7c996e402e8": "0e1c73a2846468eef95313d4e5c394d6",
    "aabd5d7ceebf0ca04970cf836f8aaa41": "4edea9bc2acd426c04b20ad0f656dfda",
    "df9b926b795e8ec31bef4156435c4ab9": "aa17bd8932f47b26caf8bd27aa8b00e9"
  },
  "fcb5de7c3484c88120c92edf399d17a8": {
    "answer": "klm"
  },
  "0f9d2977e66fe7e6bcfb78659f13f9af": {
    "answer": "nop"
  },
  "92de1c7bae914e1213ecc95dd0a7c8a0": {
    "answer": "qrs"
  },
  "74e7f471011fdbf780f25563f4f92a0b": {
    "answer": "tuv"
  },
  "75fa3e245138f7fadc68083aebab55c2": {
    "answer": 5
  },
  "e41bb071c73d64647e65f1474a12604b": {},
  "year": 2019,
  "quarter": 1
}

Some questions has sub questions inside of it. Like in this case.

  "593dfbd2317debd60e74c321512fe77a": {
    "1c99416b1c016fdf0ce0b7c996e402e8": "0e1c73a2846468eef95313d4e5c394d6",
    "aabd5d7ceebf0ca04970cf836f8aaa41": "4edea9bc2acd426c04b20ad0f656dfda",
    "df9b926b795e8ec31bef4156435c4ab9": "aa17bd8932f47b26caf8bd27aa8b00e9"
  }

I need it to be processed and converted in to a key value pair so i can store it in a database having a structure like this.

+----+------+---------+----------------------------------+----------------------------------+
| Id | Year | Quarter |             Question             |              Answer              |
+----+------+---------+----------------------------------+----------------------------------+
|  1 | 2019 |       1 | b07e33061bd31c8d29095f44d9898826 | abc                              |
|  2 | 2019 |       1 | c4edd6e206973168fb15ced212397197 | def                              |
|  3 | 2019 |       1 | f35270e30bafb94a30f1b22786f748a6 | b66c043042586e893a147d4b0ba53bdf |
|  4 | 2019 |       1 | f35270e30bafb94a30f1b22786f748a6 | 85eb345f9ad8035bb8928faa4b2b741d |
|  5 | 2019 |       1 | f35270e30bafb94a30f1b22786f748a6 | 071475576d1925e595c39e47ea00e45c |
|  6 | 2019 |       1 | fc6b41df07db039e3903665e0669f8e9 | null                             |
|  7 | 2019 |       1 | 58ff182f96dd321a24bcd08a982566c6 | 11b5da0633d22d584f58f85c21804dbf |
|  8 | 2019 |       1 | 6fee5fc87f6515467f870015b84d8975 | 5467a55ce17aa356898592a60d06964e |
+----+------+---------+----------------------------------+----------------------------------+

To make things simpler I will summarize the question IDs further.

+----+------+---------+--------------+------------+
| Id | Year | Quarter |   Question   |   Answer   |
+----+------+---------+--------------+------------+
|  1 | 2019 |       1 | questionId 1 | abc        |
|  2 | 2019 |       1 | questionId 2 | def        |
|  3 | 2019 |       1 | questionId 3 | answerId 1 |
|  4 | 2019 |       1 | questionId 3 | answerId 2 |
|  5 | 2019 |       1 | questionId 3 | answerId 3 |
|  6 | 2019 |       1 | questionId 4 | null       |
|  7 | 2019 |       1 | questionId 5 | answerId 4 |
|  8 | 2019 |       1 | questionId 6 | answerId 5 |
+----+------+---------+--------------+------------+

So I decided to create a new json model with the structure of,

"year" : "2019"
"quarter" : "1"
"question 1" : "answer 1"
"question 2" : "answer 2"
"question 3" : "answer 3"

Currently I have tried to loop through the object and get the values but this algorithm is too complex and consumes time. In some cases it do not properly match answers with questions.

public async Task<IActionResult> PostResponses([FromBody] dynamic jsonObject)
    {
        string answerText = null;
        var model = new JObject();
        model.Add("Year", jsonObject["year"].ToString());
        model.Add("Quarter", jsonObject["quarter"].ToString());
        using (var reader = new JsonTextReader(new StringReader("[" + jsonObject + "]")))
        {
            while (reader.Read())
            {
                string questionText = null;
                if (reader.TokenType == JsonToken.PropertyName )
                {
                    string questionId = reader.Value.ToString();
                    if (questionId.Length == 32)
                    {
                        try
                        {
                            // get question corresponding to this question ID
                            var question = await _context.Questions.FirstOrDefaultAsync(s => s.Id == questionId);
                            System.Diagnostics.Debug.WriteLine("Question -" + questionId);
                            questionText = question.Text;
                        }
                        catch (Exception)
                        {

                        }
                    }
                }
                if (reader.TokenType == JsonToken.String || reader.TokenType == JsonToken.Integer)
                {
                    string answerId = reader.Value.ToString();
                    if (answerId.Length == 32)
                    {
                        try
                        {
                            // get answer corresponding to this answer ID
                            var answers = await _context.OfferedAnswers.FirstOrDefaultAsync(s => s.Id == answerId);
                            answerText = answers.Value;
                        }
                        catch (Exception)
                        {

                        }
                    }
                    else
                    {
                        answerText = answerId;
                    }
                }

                if (questionText != null && answerText != null)
                {
                    model.Add(questionText, answerText);  
                }


            }
            System.Diagnostics.Debug.WriteLine(model.ToString());
        }
        return Ok();
    }

If someone can suggest a better way to do this is highly appreciated. I see no other way of doing this. Thank you in advance.

Why don't you create a Model that enables the build in json handler of .net to generate a object representing your response? Then you would not have to parse the whole dynamic object or am I getting something wrong? If you have difficulty with creating a matching model, I do already have an idea. — Manuel Fuchs
– Manuel Fuchs, Commented Oct 13, 2018 at 19:19
To create model to represent this data, the object has to be persistent right? as an example if the data is about a person then we can easily create a model having those data like [age, name, address, DOB] but here only the [year, quarter] are persistent. Other questionIDs get changed overtime, so as per my knowledge we cant create a static model for this. — Nishan
– Nishan, Commented Oct 13, 2018 at 19:30
you could wrap the whole object in a Submission instance that has a List of KeyvaluePair answers. The key is of type string, the value is of a abstract type that has to be implemented by you. You derive the abstract type into the different possibilites your json can represent and your problem should be solved. — Manuel Fuchs
– Manuel Fuchs, Commented Oct 13, 2018 at 19:52
Can you please further elaborate this approach? by providing an answer down below, if possible. I do not know how to implement such scenario. — Nishan
– Nishan, Commented Oct 13, 2018 at 19:58
Hi @nishan-chathuranga , I added my approach to your problem as an Answer. If you have problems implementing it, please comment it, maybe we can resolve the issue together. If my answer solves your problem, please mark it as correct answer in order to show other users, that this worked as solution. — Manuel Fuchs
– Manuel Fuchs, Commented Oct 14, 2018 at 21:43

Brian Rogers · Accepted Answer · 2018-10-16 04:27:27Z

I would not bother with a JsonTextReader here to pick through the JSON; that class can be a bit cumbersome to use. It looks like you already have all the data in a JObject (although you have it declared as a dynamic), so you already have a nice API to work with the data.

First thing to do is change the declaration of the jsonObject parameter so that it is strongly typed and not dynamic. This will allow you to use System.Linq methods on it, give you compile-time type checking and intellisense support, and should improve performance a bit as well.

public async Task<IActionResult> PostResponses([FromBody] JObject jsonObject)

Next, let's make a simple class to represent the data rows you want to get out of the JSON.

class Row
{
    public int Year { get; set; }
    public int Quarter { get; set; }
    public string Question { get; set; }
    public string Answer { get; set; }
}

Now we can extract the data from the jsonObject and create a list of Rows like this:

int year = (int)jsonObject["year"];
int quarter = (int)jsonObject["quarter"];

// Find all the descendant JProperties in the JObject with names having 
// a length of 32-- these represent the questions.
var props = jsonObject.Descendants()
    .OfType<JProperty>()
    .Where(prop => prop.Name.Length == 32);

// Transform the properties into a list of Rows
List<Row> rows = new List<Row>();
foreach (JProperty prop in props)
{
    // Create a list of answers for the question
    var answers = new List<string>();

    // if the property value is a string, this is one of the nested questions
    // and the "answer" is actually an ID
    if (prop.Value.Type == JTokenType.String)
    {
        answers.Add((string)prop.Value);
    }
    // if the property value is an object, we could have 0, 1 or many answers inside
    else if (prop.Value.Type == JTokenType.Object)
    {
        if (prop.Value["answer"] != null)  // single answer
        {
            answers.Add((string)prop.Value["answer"]);
        }
        else if (prop.Value["selectedAnswers"] != null)  // many answers
        {
            answers.AddRange(prop.Value["selectedAnswers"].Values<string>());
        }
        else  // no answers
        {
            answers.Add(null);
        }
    }

    // Now create a Row for each answer for this question and add it to the list of rows
    foreach (string answer in answers)
    {
        rows.Add(new Row
        {
            Year = year,
            Quarter = quarter,
            Question = prop.Name,  // The property name is the question ID
            Answer = answer
        });
    }
}

At this point you now have a List<Row> containing the data in the format you described in your question. You can simply loop through them and insert them into your database if that is what you need to do.

Fiddle: https://dotnetfiddle.net/fyr06g

If you need to retrieve existing question and answer text from the database for these rows, you should avoid doing individual queries for each question and answer as that will perform poorly. Instead, use one query to fetch the target questions and and a second query to fetch the target answers. Put each result set into separate dictionaries for easy lookup, then you can match them up to the rows at the end. Here is what it might look like in code:

// gather the unique question IDs
var questionIds = rows.Select(r => r.Question).ToList();

// query the database for all the questions referenced in the rows list
// and put the question texts in a dictionary by keyed by question id
var questionDict = _context.Questions
                           .Where(q => questionIds.Contains(q.Id))
                           .ToDictionary(q => q.Id, q => q.Text);

// gather the unique answer IDs
var answerIds = rows.Where(r => r.Answer != null && r.Answer.Length == 32)
                    .Select(r => r.Answer)
                    .Distinct()
                    .ToList();

// query the database for all the answers referenced in the rows list
// and put the answer values in a dictionary by keyed by answer id
var answerDict = _context.OfferedAnswers
                         .Where(a => answerIds.Contains(a.Id))
                         .ToDictionary(a => a.Id, a => a.Value);

// now we can loop over the rows and look up the question/answer text in the dictionaries
foreach (var row in rows)
{
    string questionText = null;
    if (!questionDict.TryGetValue(row.Question, out questionText))
        questionText = row.Question;  // if not found, use the question ID instead

    string answerValue = null;
    if (row.Answer != null && !answerDict.TryGetValue(row.Answer, out answerValue))
        answerValue = row.Answer;  // if not found use the answer value from the row instead

    Console.WriteLine(questionText + " -- " + answerValue);
}

Fiddle: https://dotnetfiddle.net/kQGuYn

Just what I needed @Brian Rogers, Thank you very much. I was still struggling with the previous algorithm and you came to save the day. Hats Off to the effort that you had to put through for providing this answer. I really appreciate it.

Manuel Fuchs · Accepted Answer · 2018-10-13 20:54:36Z

You could create models that look like this:

public class Submission
{
   [JsonConverter(typeof(QuestionConverter))]
   List<IQuestion> Questions 
}

public interface IQuestion
{
   public string Id { get; set; }
}

public class SingleChoiceQuestion : IQuestion
{
    public string Id { get; set; }

    public string Answer { get; set; }
}

public class MultipleChoiceQuestion : IQuestion
{
    public string Id { get; set; }
    public List<string> SelectedAnswers { get; set; }
}

// this lets you nest questions inside questions (or just multiple single choice questions)
public class RecursiveQuestion : IQuestion
{
    public string Id { get; set; }
    public List<IQuestion> Question { get; set; }
}

Write your JsonConverter like described here Custom Deserialization using Json.NET.

It is needed to create your own JsonConverter in order to remove the extreme use of KeyValuePairs and therefore allowing the use of the interface as type. (interface has no default constructor (duh) and therefore not usable with the default JsonConverter. Furthermore, the default converter can't decide or know what implementations exist and should be used for what json construct)

Thank you @Manuel Fuchs, I really do appreciate this approach and the effort. I managed to overcome some of the problems using this approach. Thank you again.

Collectives™ on Stack Overflow

Create new JSON model using the existing JSON dynamically in dotnet core web API

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related