Enabling JSON Document Stores in Relational Systems (2013) [pdf] | Hacker News Reader

Enabling JSON Document Stores in Relational Systems (2013) [pdf]

pages.cs.wisc.edu

27 pointswilsonfiifi19 comments

> ...providing all the advantages of NoSQL JSON document stores, while providing higher performance and richer functionality (such as native joins, and ACID guarantees instead of BASE consistency)

Seems too good to be true.

The performance impact of inserting/fetching multiple rows per object, multiple SQL joins for a single logical join, etc. would probably be significant. And indexing these tables seems to require indexing every name/value pair, not just the interesting fields. And, of course, the overhead of the translation layer itself.

Also, are relational native joins and ACID guarantees still available at scale?

Not sure about this paper, but Postgres' JSONB is quite good.

It's quite fast, you can easily use values like SQL sub-queries and you can index both everything and also specific subsets of the JSON data with expression indexes.

That's native support for the data type; this paper is about a translation layer that sits on top of the DB.

There's an open-source implementation of a different approach to the same problem in ToroDB (http://github.com/torodb/torodb).

ToroDB creates tables dynamically, which match the types (mapped to columns) of every level of the JSON documents, obtaining a fully relational database. This is effectively "partitioning" (automatically) the documents based on their effective "type".

ToroDB actually uses PostgreSQL as its backend to store the data relationally.

Hope this information is interesting.

Disclaimer: I am one of the developers of ToroDB

I find these papers from 1999 by Daniela Florescu more informative with regards to the benefits of the various schema available to map semi-structured data:

http://dsl.serc.iisc.ernet.in/~course/TIDS/papers/daniela.pd...

http://faculty.ksu.edu.sa/mathkour/XML%20DB/A%20Performance%...

http://schemafreedb.com/demo.html not a doc store but allows the storing of semi-structured data with a sql like query language with a json over http api.

http://howfuckedismydatabase.com/nosql/

I do not see how this applies. Below is an example of how you query the data. Map reduce not required

[ {"query": { "_queryId":"office-query", "sfql": "SELECT $phone.oid, $s:phone.name, $s:phone.value WHERE $s:phone.name='office'" }}, {"query": { "_queryId":"mobile-query", "sfql": "SELECT $phone.oid, $s:phone.name, $s:phone.value WHERE $s:phone.name='mobile'" }} ]

and example result is in json format. Note two queries in one request referenced by query ID.

[ { "_queryId": "office-query", "cmdname": "query", "data": [ { "phone_oid": "3", "s:phone_name": "office", "s:phone_value": "111-112-1113" }, { "phone_oid": "7", "s:phone_name": "office", "s:phone_value": "111-112-1113" } ] }, { "_queryId": "mobile-query", "cmdname": "query", "data": [ { "phone_oid": "4", "s:phone_name": "mobile", "s:phone_value": "222:223:2224" }, { "phone_oid": "8", "s:phone_name": "mobile", "s:phone_value": "222:223:2224" } ] } ]

I developed something a little bit like this when developing a profiler for JSON documents but didn't think about general purpose queries, in particular I squashed [3] to [].

This seems cool, but does anybody know about possibility of using mongo query language with Relational Databases?

I have absolutely no idea what I'm talking about, but maybe a Postgres extension could be written for this? There's one for running SQL Server T-SQL in Postgres, which is absolutely nothing like Mongo, but...

This would probably be the right way to go. I even started working on a solution last year (see other comment), but got side-tracked by real-life issues.

My idea was to write a PG extension which would handle all the smarts, and expose a set of sql functions which could be called from the application side. Those functions could then be wrapped in nice client libraries to give a consistent mongo-a-like interface across languages, with the client doing the bare-minimum to shuttle data to and from the database server.

It's probably just a matter of writing some code to map from mongo-style queries to SQL.

The query operations would probably be straight-forward, but I could see issues with the write operators mongo has. For instance, if the relational database doesn't support atomic updates within json documents then it may be hard to match the meaning of mongos $set operation, etc.

EDIT: I tried doing exactly this as a clojure library (https://github.com/ShaneKilkelly/clj-bedquilt)

There's a proof of concept here using postgresql https://github.com/JerrySievert/mongolike

I have commented it above, but have you checked ToroDB? ;) https://github.com/torodb/torodb/

I like where this is going, will be definitely watching this.

For my purposes I'm pretty much sure we will be looking for something on top of Postgres.

Thanks

JSON is fine in relational and NoSQL databases... until you start trying to query fields inside the JSON.

XML database 2.0. Here we go again.

Reply on news.ycombinator.com