Storing dynamic JSON data in db and search by key-value c# entity-framework entity-framework-core sql-server


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"


  1. Store answers for users in DB
  2. 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...))
  3. 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.

9/16/2018 8:19:35 PM

Accepted Answer

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:

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

More examples on how to query JSON in Sql Server here

9/16/2018 8:34:10 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow