On Fri, Mar 30, 2012 at 9:57 PM, Merlin Moncure
<mmoncure@gmail.com> wrote:
On Thu, Mar 29, 2012 at 10:04 AM, Andrew Dunstan <
andrew@dunslane.net> wrote:
> 1. I've been in discussion with some people about adding simple JSON extract
> functions. We already have some (i.e. xpath()) for XML.
features with a relational backend. My take is that storing rich data
inside the database in json format, while tempting, is generally a
mistake. Unless the document is black box it should be decomposed and
stored relationally and marked back up into a document as it goes out
the door.
I found storing documents (with no schema) in CouchDB very sexy. For analytics purposes it's very handy to throw whatever data you've got into a document and save it to the database and sort out the schema later, when you have a need for it. It could be you want to allow hundreds of different fields in the document, and the document has a hierarchal structure, and so on ... and yet there's no interest in spending time figuring out how to map all that effectively into a SQL schema. Some things might start out a black box but become interesting for reporting purposes later.
While I was doing this I always thought this would have been a better approach for my previous project, an accounting application. If I could just have stored entities like invoice & customer as a single document that is inserted, updated, etc. atomically it would be a lot simpler and faster than having to break things out into columns and rows spread over various tables. Some fields are just "information" that isn't searched for or used relationally but ends up getting a field (or a whole bunch of fields, like your address) in the DB schema anyway. Adding, removing, and changing columns is always scary to me in the SQL database - when there's no schema you can fix these things as part of the definition of your "view" instead.
So I kind of think the document database kind of bridges the gap between an OODBMS and the RDBMS because the document is like a little cluster of objects and then you populate your relations by mapping a view of those documents and use that for analysis and search.
What I'm saying is that jsonpath probably isn't the whole story:
another way of bulk moving json into native backend structures without
parsing would also be very helpful. For example, being able to cast a
json document into a record or a record array would be just amazing.
Hmm whatever that exactly is, it does sound cool!