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:
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.
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