I am storing a JSON string in the database that represents a set of properties. In the code behind, I export it and use it for some custom logic. Essentially, I am using it only as a storage mechanism. I understand XML is better suited for this, but I read that JSON is faster and preferred.
Is it a good practice to use JSON if the intention is not to use the string on the client side?
I am storing a JSON string in the database that represents a set of properties. In the code behind, I export it and use it for some custom logic. Essentially, I am using it only as a storage mechanism. I understand XML is better suited for this, but I read that JSON is faster and preferred.
Is it a good practice to use JSON if the intention is not to use the string on the client side?
Share Improve this question edited Dec 30, 2010 at 18:15 Peter Mortensen 31.6k22 gold badges109 silver badges133 bronze badges asked Feb 2, 2009 at 23:35 Bob SmithBob Smith 8412 gold badges8 silver badges11 bronze badges8 Answers
Reset to default 9JSON is a perfectly valid way of storing structured data and simpler and more concise than XML. I don't think it is a "bad practice" to use it for the same reason someone would use XML as long as you understand and are OK with its limitations.
Whether it's good practice I can't say, but it strikes me as odd. Having XML fields in your SQL database are at least queryable (SQL Server 2000 or later, MySQL and others) but more often than not is a last resort for metadata.
JSON is usually the carrier between JavaScript and your back end, not the storage itself unless you have a JSON back end document orientated database such as CouchDB or SOLR as JSON lends itself perfectly for storing documents.
Not to say I don't agree with using JSON as a simple (that is, not serializing references) data serializer over XML, but I won't go into a JSON vs XML rant just for the sake of it :).
If you're not using JSON for its portability between 2 languages, and you're positive you will never query the data from SQL you will be better off with the default serialization from .NET.
You're not the only one using JSON for data storage. CouchDB is a document-oriented database which uses JSON for storing information. They initially stored that info as XML, then switched to JSON. Query-ing is done via good old HTTP.
By the way, CouchDB received some attention lately and is backed by IBM and the Apache Software Foundation. It is written in Erlang and promises a lot (IMHO).
Yeah I think JSON is great. It's an open standard and can be used by any programming language. Most programming languages have libraries to parse and encode JSON as well.
JSON is just a markup language like XML, and due to its more restrictive specification is is smaller in size and faster to generate and parse. So from that aspect it's perfectly acceptable to use in the database if you need ad-hoc structured markup (and you're absolutely sure that using tables isn't possible and/or the best solution).
However, you don't say what database you're using. If it is SQL Server 2005 or later then the database itself has extensive capabilities when dealing with XML, including an XML data type, XQuery support, and the ability to optimise XQuery expressions as part of an execution plan. So if this is the case I'd be much more inclined to use XML and take advantage of the database's facilities.
Just remember that JSON has all of the same problems that XML has as a back-end data store; namely, it in no way replaces a relational database or even a fixed-size binary format. If you have millions of rows and need random access, you will run into all the same fundamental performance issues with JSON that you would with XML. I doubt you're intending to use it for this kind of data store scenario, but you never know... stranger things have been tried :)
JSON is shorter, so it will use less space in your database. I'd probably use it instead of XML or writing my own format.
On the other hand, searching for matches will suck - you're going to have to use "where json like '% somevalue %'" which will be painfully slow. This is the same limitation with any other text storage strategy (xml included).
You shouldn't use either JSON or XML for storing data in a relational database. JSON and XML are serialization formats which are useful for storing data in files or sending over the wire.
If you want to store a set of properties, just create a table for it, with each row beeing a property. That way you can query the data using ordinary SQL.