Storing and processing JSON documents in a SQL database table
Embodiments for storing a JSON document tree in a single SQL database table by defining a schema to store JSON nodes in the table and converting each JSON document into a corresponding set of rows in the table using the schema to encode a hierarchical relationship of nodes and sub-nodes into a path that specifies a node path to a root node for each node. Embodiments further receiving a SQL query to retrieve a specific sub-document, responding to the query by returning all rows of the table belonging to the sub-document, and inflating the returned rows back into a JSON expression using the path to guide reconstruction of the original JSON document tree.
1. A method comprising:
defining a schema to store JavaScript Object Notation (JSON) nodes in a Structured Query Language (SQL) database table in a memory storage device;
converting each JSON node into a corresponding row of the SQL database table using the schema to encode a hierarchical relationship of nodes and sub-nodes into a path that specifies a node path to a root node for each node;
receiving a SQL query to find a specific sub-node;
responding to the query by returning all rows of the SQL database table belonging to the sub-node; and
inflating the returned rows back into a JSON expression by using the path to guide reconstruction of the original JSON nodes.
2. The method of claim 1 wherein the JSON nodes comprise documents of a JSON document tree.
3. The method of claim 2 wherein the JSON nodes include each of six JSON data types of the group consisting of: object, array, string, number, Boolean, and null.
4. The method of claim 3 wherein the schema comprises an ID, path, type, name, and value; wherein the ID is a unique identification number for a node; the type is a JSON datatype specifier; the name is a user configurable JSON node name if the node is an object, and the value is a JSON node value if the node is not an array.
5. The method of claim 2 wherein the converting comprises:
reading the documents using a JSON database library function; and
automatically generating SQL insert statements into the documents to convert each document into the corresponding row of the SQL database table.
6. The method of claim 5 wherein the SQL database table comprises a single table, the method further comprising saving the entire document tree in the single SQL table.
7. The method of claim 6 wherein the single table is implemented in one of: SQLite3, PostgreSQL, MySQL, and Oracle.
8. The method of claim 2 wherein the inflating comprises using a JSON database library function to back-convert each corresponding row of the SQL database table into a live Javascript data object for a corresponding document or sub-document of the JSON document tree.
9. The method of claim 8 further comprising displaying the live Javascript data object to a user through a graphical user interface.
10. A system comprising:
an application executed in a networked computer to perform tasks using data processed in the system;
an application adapter providing application-level JSON (JavaScript Object Notation) storage handling functions;
a JSON adapter converting JSON documents embodying the data into corresponding rows of a SQL database table and inflates the rows of the table back into corresponding documents in response to a SQL query for the corresponding documents;
a database driver communicating between the JSON adapter and a database engine;
a storage device storing the data in the SQL database table as specified by the database engine.
11. The system of claim 10 wherein the JSON adapter converts JSON documents into SQL insert, update, and delete queries.
12. The system of claim 10 wherein the SQL database table is a single table and is implemented in one of: SQLite3, PostgreSQL, MySQL, and Oracle.
13. The system of claim 12 wherein the data is originally stored in a JSON document tree having nodes and sub-nodes corresponding to each of the JSON documents.
14. The system of claim 13 wherein the JSON nodes include each of six JSON data types of the group consisting of: object, array, string, number, Boolean, and null.
15. The system of claim 14 wherein the schema comprises an ID, path, type, name, and value; wherein the ID is a unique identification number for a node; the type is a JSON datatype specifier; the name is a user configurable JSON node name if the node is an object, and the value is a JSON node value if the node is not an array.
16. The system of claim 15 wherein the JSON adapter reads the documents using a JSON database library function, and automatically generates SQL insert statements into the documents to convert each document into the corresponding row of the SQL database table.
17. The system of claim 16 wherein the JSON adapter inflates by using a JSON database library function to back-convert each corresponding row of the SQL database table into a live Javascript data object for a corresponding document or sub-document of the JSON document tree.
18. A method comprising:
creating a JSON-style table in a SQL database;
writing a JSON document to the database by deflating the JSON document and executing a single insert query to write the entire JSON document to the database in this single query; and
reading the JSON document by executing a select query to request the document by its identifier (id), wherein the select query reads an entire JSON document sub-tree from the database, and inflating the JSON rows returned by the database into the JSON document.
19. The method of claim 18 further comprising defining a schema for the JSON-style table, wherein the schema comprises an ID, path, type, name, and value; wherein the ID is a unique identification number for a node; the type is a JSON datatype specifier; the name is a user configurable JSON node name if the node is an object, and the value is a JSON node value if the node is not an array.