Storing dynamic JSON data in db and search by key-value

Question

I need store dynamic json in DB and search by key-value

i have json

    [{
      "id": 1,
      "Type": "string",
      "FieldName" : "Test",
      "Value": "Test", 
    }, {
      "id": 2,
      "Type": "bool",
      "FieldName" : "Test2",
      "Value": "true", 
     "id": 2,
      "Type": "dropdown",
      "FieldName" : "dropDownTest",
      "Value": [{"text":"my placeholder", "Value": "myTestValue"}, {"text":"my placeholder2", "Value": "myTestValue2"}]
    }, 
    {
      "id": 3,
      "Type": "int",
      "Value": 133
},
{
      "id": 4,
      "Type": "DateTime",
      "Value": ""
}]

This json generated by admin-site. Admin has a panel where he can add columns, fieldType and the default value (or array of values if this drop-down)

This json transform to form, which user fill on site and store to DB

Data for saving look like this:

   [{
      "QuestionId": 1,
      "Value": "Test", 
      }, 
    {
      "QuestionId": 2,
      "Value": true, 
    },
    {
      "QuestionId": 4,
      "Value": "true", 
      "Value": "2018-09-16T20:03:57.551Z"
    }
    ]

Task:

Store answers for users in DB
Search by Answers in DB (filters on site, one or multiple matches e.g question 1 and 4 matches (value is "Test" and Date More then Yesterday...))
Optimal structure for fast search

My Vision: create an entity

public class QuestionEntity
{
    public int id { get; set; }
    public string Type { get; set; }
    public string FieldName { get; set; }
    public string Value { get; set; }
}

and create an answer entity

public class Answer
{
    public int QuestionId { get; set; }
    public string Value { get; set; }
}

And Add this Answers as user collections

public ICollection<Answer> Answers{get; set;}

I not sure what it's an optimal way for this task, and I will be grateful if someone to share their cases if you have them.

score 1 · Accepted Answer · answered Sep 16 '18 at 20:34

Using the latest SQL Server capabilities (since 2016), you can store JSON as text and do queries on it.

Storing is one thing, querying is another: to do so, you will have to run queries directly against the DB, or run a Stored Procedure (look at this SO answer)

Then, at last, you will have to write specific SQL statements to be able to query items in you JSON (if you have a lot of lines, having indexes on JSON properties could also be a good idea).

As for the query itself, it would look something like that:

SELECT
    tj.Id, QuestionData.questionId, QuestionData.questionValue
FROM
    MyTableWithJson tj
    CROSS APPLY OPENJSON(tj.JsonContent)
    WITH ([questionId] INT '$.QuestionId', [questionValue] NVARCHAR(300) '$.Value') AS QuestionData
WHERE
    QuestionData.type = 'multichoice' // supposing you have a type on this entity

More examples on how to query JSON in Sql Server here

Storing dynamic JSON data in db and search by key-value

1 Answers1