Re: HTTP Frontend? (and a brief thought on materialized views)

On Fri, Mar 30, 2012 at 9:57 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Mar 29, 2012 at 10:04 AM, Andrew Dunstan <andrew(at)dunslane(dot)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.
>
> Your json work is a great start in marrying document level database
> 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!