将JSON存储在msSQL数据库中？ [英] Storing JSON in an msSQL database?

问题描述

我正在开发一个表单生成器，并且想知道将JSON存储在SQL数据库中是否会导致错误的mojo？

我想保留数据库&表格很简单，所以我将要有

 `pKey，formTitle，formJSON` 
  

放在一张桌子上，然后储存

  {[firstName：{required：true，type：text}，lastName：{required：true，type：text}} 
  

在formJSON中。

我在我的CMS（主机大约有110个站点）中广泛使用JSON，并且我发现访问数据的速度非常快。我很惊讶没有更多的速度退化。 CMS中的每个对象（页面，布局，列表，主题等）都有一个名为JSONConfiguration的NVARCHAR（MAX）列。如果需要，我的ORM工具知道要查找该列并将其重新组合为对象。或者，根据具体情况，我会将它传递给客户端，以便jQuery或Ext JS处理。

至于代码的可读性/可维护性，您可能会说它得到了改进，因为我现在有类表示存储在数据库中的很多JSON对象。

我使用JSON.net进行所有序列化/反序列化。 http://james.newtonking.com/default.aspx

我也使用单个查询来返回meta-JSON和实际数据。和Ext JS的情况一样，我有查询返回Ext JS对象的结构以及对象需要的数据。这削减了一个回发/ SQL往返。

我也惊讶于代码解析JSON对象列表的速度有多快，并将它们映射到一个DataTable对象然后我交给了一个GridView。

使用JSON唯一的缺点是索引。如果你有一个JSON属性你需要搜索，那么你必须将它作为一个单独的列存储。

有JSON DB可以满足您的需求更好：CouchDB，MongoDB和Cassandra。

I'm developing a form generator, and wondering if it would be bad mojo to store JSON in an SQL database?

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.

Any input is appreciated.

解决方案

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.

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.

I used JSON.net for all serialization / deserialization. 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.

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.

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.

There are JSON DB's out there that might server your needs better: CouchDB, MongoDB, and Cassandra.

这篇关于将JSON存储在msSQL数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！