5 ms·

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

27 points by wilsonfiifi 12y ago

mkrn 12y ago
This seems cool, but does anybody know about possibility of using mongo query language with Relational Databases?
- Jgrubb 12y ago
  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...
  - s_kilk 12y ago
    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.
- s_kilk 12y ago
  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 https://github.com/ShaneKilkelly/clj-bedquilt)
- wilsonfiifi 12y ago
  There's a proof of concept here using postgresql https://github.com/JerrySievert/mongolike https://github.com/JerrySievert/mongolike
- ahachete 12y ago
  I have commented it above, but have you checked ToroDB? ;) https://github.com/torodb/torodb/ https://github.com/torodb/torodb/
  - mkrn 12y ago
    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
buro9 12y ago
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.pdf http://dsl.serc.iisc.ernet.in/~course/TIDS/papers/daniela.pd... http://faculty.ksu.edu.sa/mathkour/XML%20DB/A%20Performance%20Evaluation%20of%20Alternative%20Mapping%20Schemes%20%20%281%29.pdf http://faculty.ksu.edu.sa/mathkour/XML%20DB/A%20Performance%...
PaulHoule 12y ago
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 [].
leereeves 12y ago
> ...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?
- lucian1900 12y ago
  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.
  - leereeves 12y ago
    That's native support for the data type; this paper is about a translation layer that sits on top of the DB.
dfragnito 12y ago
http://schemafreedb.com/demo.html 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.
- andyzweb 12y ago
  http://howfuckedismydatabase.com/nosql/ http://howfuckedismydatabase.com/nosql/
  - dfragnito 12y ago
    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'" }} ]
    - dfragnito 12y ago
      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" } ] } ]
  - deleted 12y ago
    [deleted]
wallflower 12y ago
JSON is fine in relational and NoSQL databases... until you start trying to query fields inside the JSON.
ExpiredLink 12y ago
XML database 2.0. Here we go again.
ahachete 12y ago
There's an open-source implementation of a different approach to the same problem in ToroDB (http://github.com/torodb/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

27 points by wilsonfiifi 12y ago

mkrn 12y ago

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

Jgrubb 12y ago
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...
- s_kilk 12y ago
  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.
s_kilk 12y ago
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 https://github.com/ShaneKilkelly/clj-bedquilt)
wilsonfiifi 12y ago
There's a proof of concept here using postgresql https://github.com/JerrySievert/mongolike https://github.com/JerrySievert/mongolike
ahachete 12y ago
I have commented it above, but have you checked ToroDB? ;) https://github.com/torodb/torodb/ https://github.com/torodb/torodb/
- mkrn 12y ago
  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

buro9 12y ago

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.pdf http://dsl.serc.iisc.ernet.in/~course/TIDS/papers/daniela.pd... http://faculty.ksu.edu.sa/mathkour/XML%20DB/A%20Performance%20Evaluation%20of%20Alternative%20Mapping%20Schemes%20%20%281%29.pdf http://faculty.ksu.edu.sa/mathkour/XML%20DB/A%20Performance%...

PaulHoule 12y ago

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 [].

leereeves 12y ago

> ...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?

lucian1900 12y ago
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.
- leereeves 12y ago
  That's native support for the data type; this paper is about a translation layer that sits on top of the DB.

dfragnito 12y ago

http://schemafreedb.com/demo.html 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.

andyzweb 12y ago
http://howfuckedismydatabase.com/nosql/ http://howfuckedismydatabase.com/nosql/
- dfragnito 12y ago
  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'" }} ]
  - dfragnito 12y ago
    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" } ] } ]
- deleted 12y ago
  [deleted]

wallflower 12y ago

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

ExpiredLink 12y ago

XML database 2.0. Here we go again.

ahachete 12y ago

There's an open-source implementation of a different approach to the same problem in ToroDB (http://github.com/torodb/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