I'm developing a form generator, and wondering if it would be bad mojo to store JSON in an SQL database?
我正在开发一个表单生成器，我想知道在SQL数据库中存储JSON会不会是个坏习惯?
I want to keep my database & tables simple, so I was going to have
我想让我的数据库和表保持简单，所以我要
`pKey, formTitle, formJSON`
on a table, and then store
放在桌子上，然后储存
{["firstName":{"required":"true","type":"text"},"lastName":{"required":"true","type":"text"}}
in formJSON.
在formJSON。
Any input is appreciated.
任何输入都是感激。
7 个解决方案
#1
29
I use JSON extensively in my CMS (which hosts about 110 sites) and I find the speed of access data to be very fast. I was surprised that there wasn't more speed degradation. Every object in the CMS (Page, Layout, List, Topic, etc) has an NVARCHAR(MAX) column called JSONConfiguration. My ORM tool knows to look for that column and reconstitute it as an object if needed. Or, depending on the situation, I will just pass it to the client for jQuery or Ext JS to process.
我在CMS(大约有110个站点)中大量使用JSON，我发现访问数据的速度非常快。我很惊讶速度没有进一步下降。CMS中的每个对象(页面、布局、列表、主题等)都有一个名为JSONConfiguration的NVARCHAR(MAX)列。我的ORM工具知道要查找该列，并在需要时将其重构为对象。或者，根据具体情况，我将把它传递给客户端以便jQuery或Ext JS处理。
As for readability / maintainability of my code, you might say it's improved because I now have classes that represent a lot of the JSON objects stored in the DB.
至于我的代码的可读性/可维护性，您可能会说它得到了改进，因为我现在有了表示存储在DB中的许多JSON对象的类。
I used JSON.net for all serialization / deserialization. http://james.newtonking.com/default.aspx
我使用JSON.net进行所有的序列化/反序列化。http://james.newtonking.com/default.aspx
I also use a single query to return meta-JSON with the actual data. As in the case of Ext JS, I have queries that return both the structure of the Ext JS object as well as the data the object will need. This cuts out one post back / SQL round trip.
我还使用一个查询返回元json和实际数据。与Ext JS的情况一样，我的查询返回Ext JS对象的结构和对象需要的数据。这减少了一个post back / SQL往返。
I was also surprised at how fast the code was to parse a list of JSON objects and map them into a DataTable object that I then handed to a GridView.
我还对这段代码解析JSON对象列表并将它们映射到一个DataTable对象的速度感到惊讶，然后我将这个对象交给了GridView。
The only downside I've seen to using JSON is indexing. If you have a property of the JSON you need to search, then you have to store it as a separate column.
使用JSON的唯一缺点是索引。如果需要搜索JSON属性，则必须将其存储为单独的列。
There are JSON DB's out there that might server your needs better: CouchDB, MongoDB, and Cassandra.
有JSON DB可以更好地满足您的需求:CouchDB、MongoDB和Cassandra。
#2
7
A brilliant way to make an object database from sql server. I do this for all config objects and everything else that doesn't need any specific querying. extending your object - easy, just create a new property in your class and init with default value. Don't need a property any more? Just delete it in the class. Easy roll out, easy upgrade. Not suitable for all objects, but if you extract any prop you need to index on - keep using it. Very modern way of using sql server.
从sql server创建对象数据库的一种绝妙方法。我对所有配置对象和其他不需要任何特定查询的东西都这样做。扩展你的对象——很简单，在你的类中创建一个新的属性，用默认值初始化。不再需要财产了吗?在类中删除它。容易推出，容易升级。不适合所有对象，但是如果您提取了任何需要索引的道具，请继续使用它。非常现代的使用sql server的方式。
#3
3
It will be slower than having the form defined in code, but one extra query shouldn't cause you much harm. (Just don't let 1 extra query become 10 extra queries!)
这比在代码中定义表单要慢，但是一个额外的查询应该不会对您造成太大的伤害。(不要让一个额外的查询变成10个额外的查询!)
Edit: If you are selecting the row by formTitle instead of pKey (I would, because then your code will be more readable), put an index on formTitle
编辑:如果您使用formTitle而不是pKey来选择行(我会这样做，因为这样您的代码会更容易阅读)，那么在formTitle上放置一个索引
#4
3
We have used a modified version of XML for exactly the purpose you decribe for seven or eight years and it works great. Our customers' form needs are so diverse that we could never keep up with a table/column approach. We are too far down the XML road to change very easily but I think JSON would work as well and maybe evan better.
我们已经使用了一个修改后的XML版本，目的正是您所描述的七八年，并且它工作得很好。我们的客户的表单需求是如此的多样化，以至于我们无法与表/列方法保持一致。我们在XML的道路上走得太远，不太容易改变，但我认为JSON也可以，evan更好。
Reporting is no problem with a couple of good parsing functions and I would defy anyone to find a significant difference in performance between our reporting/analytics and a table/column solution to this need.
报告对于两个优秀的解析函数来说没有问题，而且我敢说，我们的报告/分析和表/列解决方案之间的性能差异是显而易见的。
#5
#6
1
I wouldn't recommend it.
我不推荐它。
If you ever want to do any reporting or query based on these values in the future it's going to make your life a lot harder than having a few extra tables/columns.
如果您将来想要基于这些值进行任何报告或查询，这将使您的生活比拥有一些额外的表/列要困难得多。
Why are you avoiding making new tables? I say if your application requires them go ahead and add them in... Also if someone has to go through your code/db later it's probably going to be harder for them to figure out what you had going on (depending on what kind of documentation you have).
你为什么不做新桌子呢?我的意思是，如果你的应用程序需要它们，那就把它们添加到……另外，如果稍后有人需要检查您的代码/db，那么他们可能会更难弄明白您所做的事情(这取决于您有什么样的文档)。
#7
1
I think it not an optimal idea to store object data in a string in SQL. You have to do transformation outside of SQL in order to parse it. That presents a performance issue and you lose the leverage of using SQL native data parsing capability. A better way would be to store JSON as an XML datatype in SQL. This way, you kill two birds with one stone: You don't have to create shit load of tables and still get all the native querying benefits of SQL.
我认为用SQL将对象数据存储在字符串中不是一个最佳的想法。必须在SQL之外进行转换才能解析它。这会带来性能问题，您将失去使用SQL本机数据解析功能的优势。更好的方法是将JSON存储为SQL中的XML数据类型。通过这种方式，您可以一箭双雕:您不必创建大量的表，也可以获得SQL的所有本地查询优势。
XML in SQL Server 2005? Better than JSON in Varchar?
SQL Server 2005中的XML ?在Varchar中优于JSON ?
#1
29
I use JSON extensively in my CMS (which hosts about 110 sites) and I find the speed of access data to be very fast. I was surprised that there wasn't more speed degradation. Every object in the CMS (Page, Layout, List, Topic, etc) has an NVARCHAR(MAX) column called JSONConfiguration. My ORM tool knows to look for that column and reconstitute it as an object if needed. Or, depending on the situation, I will just pass it to the client for jQuery or Ext JS to process.
我在CMS(大约有110个站点)中大量使用JSON，我发现访问数据的速度非常快。我很惊讶速度没有进一步下降。CMS中的每个对象(页面、布局、列表、主题等)都有一个名为JSONConfiguration的NVARCHAR(MAX)列。我的ORM工具知道要查找该列，并在需要时将其重构为对象。或者，根据具体情况，我将把它传递给客户端以便jQuery或Ext JS处理。
As for readability / maintainability of my code, you might say it's improved because I now have classes that represent a lot of the JSON objects stored in the DB.
至于我的代码的可读性/可维护性，您可能会说它得到了改进，因为我现在有了表示存储在DB中的许多JSON对象的类。
I used JSON.net for all serialization / deserialization. http://james.newtonking.com/default.aspx
我使用JSON.net进行所有的序列化/反序列化。http://james.newtonking.com/default.aspx
I also use a single query to return meta-JSON with the actual data. As in the case of Ext JS, I have queries that return both the structure of the Ext JS object as well as the data the object will need. This cuts out one post back / SQL round trip.
我还使用一个查询返回元json和实际数据。与Ext JS的情况一样，我的查询返回Ext JS对象的结构和对象需要的数据。这减少了一个post back / SQL往返。
I was also surprised at how fast the code was to parse a list of JSON objects and map them into a DataTable object that I then handed to a GridView.
我还对这段代码解析JSON对象列表并将它们映射到一个DataTable对象的速度感到惊讶，然后我将这个对象交给了GridView。
The only downside I've seen to using JSON is indexing. If you have a property of the JSON you need to search, then you have to store it as a separate column.
使用JSON的唯一缺点是索引。如果需要搜索JSON属性，则必须将其存储为单独的列。
There are JSON DB's out there that might server your needs better: CouchDB, MongoDB, and Cassandra.
有JSON DB可以更好地满足您的需求:CouchDB、MongoDB和Cassandra。
#2
7
A brilliant way to make an object database from sql server. I do this for all config objects and everything else that doesn't need any specific querying. extending your object - easy, just create a new property in your class and init with default value. Don't need a property any more? Just delete it in the class. Easy roll out, easy upgrade. Not suitable for all objects, but if you extract any prop you need to index on - keep using it. Very modern way of using sql server.
从sql server创建对象数据库的一种绝妙方法。我对所有配置对象和其他不需要任何特定查询的东西都这样做。扩展你的对象——很简单，在你的类中创建一个新的属性，用默认值初始化。不再需要财产了吗?在类中删除它。容易推出，容易升级。不适合所有对象，但是如果您提取了任何需要索引的道具，请继续使用它。非常现代的使用sql server的方式。
#3
3
It will be slower than having the form defined in code, but one extra query shouldn't cause you much harm. (Just don't let 1 extra query become 10 extra queries!)
这比在代码中定义表单要慢，但是一个额外的查询应该不会对您造成太大的伤害。(不要让一个额外的查询变成10个额外的查询!)
Edit: If you are selecting the row by formTitle instead of pKey (I would, because then your code will be more readable), put an index on formTitle
编辑:如果您使用formTitle而不是pKey来选择行(我会这样做，因为这样您的代码会更容易阅读)，那么在formTitle上放置一个索引
#4
3
We have used a modified version of XML for exactly the purpose you decribe for seven or eight years and it works great. Our customers' form needs are so diverse that we could never keep up with a table/column approach. We are too far down the XML road to change very easily but I think JSON would work as well and maybe evan better.
我们已经使用了一个修改后的XML版本，目的正是您所描述的七八年，并且它工作得很好。我们的客户的表单需求是如此的多样化，以至于我们无法与表/列方法保持一致。我们在XML的道路上走得太远，不太容易改变，但我认为JSON也可以，evan更好。
Reporting is no problem with a couple of good parsing functions and I would defy anyone to find a significant difference in performance between our reporting/analytics and a table/column solution to this need.
报告对于两个优秀的解析函数来说没有问题，而且我敢说，我们的报告/分析和表/列解决方案之间的性能差异是显而易见的。
#5
#6
1
I wouldn't recommend it.
我不推荐它。
If you ever want to do any reporting or query based on these values in the future it's going to make your life a lot harder than having a few extra tables/columns.
如果您将来想要基于这些值进行任何报告或查询，这将使您的生活比拥有一些额外的表/列要困难得多。
Why are you avoiding making new tables? I say if your application requires them go ahead and add them in... Also if someone has to go through your code/db later it's probably going to be harder for them to figure out what you had going on (depending on what kind of documentation you have).
你为什么不做新桌子呢?我的意思是，如果你的应用程序需要它们，那就把它们添加到……另外，如果稍后有人需要检查您的代码/db，那么他们可能会更难弄明白您所做的事情(这取决于您有什么样的文档)。
#7
1
I think it not an optimal idea to store object data in a string in SQL. You have to do transformation outside of SQL in order to parse it. That presents a performance issue and you lose the leverage of using SQL native data parsing capability. A better way would be to store JSON as an XML datatype in SQL. This way, you kill two birds with one stone: You don't have to create shit load of tables and still get all the native querying benefits of SQL.
我认为用SQL将对象数据存储在字符串中不是一个最佳的想法。必须在SQL之外进行转换才能解析它。这会带来性能问题，您将失去使用SQL本机数据解析功能的优势。更好的方法是将JSON存储为SQL中的XML数据类型。通过这种方式，您可以一箭双雕:您不必创建大量的表，也可以获得SQL的所有本地查询优势。
XML in SQL Server 2005? Better than JSON in Varchar?
SQL Server 2005中的XML ?在Varchar中优于JSON ?