SQL Blob Querying via JSON Document Node Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional SQL database systems face limitations in storing and querying non-relational data, such as document and semi-structured data, which are often stored as 'blobs' and are inaccessible for analysis, leading to complex and performance-penalty-ridden data retrieval processes.
Innovation Solution
The use of JSON document data type as an intermediate step to convert General Data Type Documents (GDTD) into a format that can be natively stored and queried in SQL relational databases, allowing for the creation of GDT-document-node records and relational schemas that enable precise analysis and storage of complex data structures.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If non-relational data (document, graph, key-value data) is stored as blob fields in relational table columns, then the data can be stored in the SQL database, but the data inside the blob becomes hidden or inaccessible to SQL queries and cannot be analyzed
Solution Approach 1:
The patent segments non-relational data into structured components that can be stored in separate relational table columns. Instead of storing entire documents as single blob entities, the data is divided into extractable fields and elements that can be individually accessed and queried through SQL, while preserving the original data structure through hierarchical path tracking.
Solution Approach 2:
The patent introduces an intermediary layer between blob storage and SQL queries. This layer includes metadata tables that store hierarchical path information, data type information, and extraction rules, enabling SQL queries to access and analyze blob contents without directly parsing the binary data.
2Reliability
If traditional SQL methods are used to store non-relational data as blobs, then storage is achieved, but data retrieval and analysis require complex processes with huge performance penalties
Solution Approach 1:
The patent performs preliminary actions by pre-extracting and pre-storing metadata about blob contents during the data insertion phase. Hierarchical paths, data types, and key field information are extracted and stored in separate tables before SQL queries are executed, eliminating the need for complex runtime parsing and significantly improving query performance.
Solution Approach 2:
The patent replaces the mechanical process of runtime blob parsing and analysis with a more efficient system based on pre-computed metadata and structured query operations. Instead of mechanically parsing binary data during queries, the system uses prepared metadata tables and optimized SQL joins to achieve the same analytical goals with much higher efficiency.
3Device complexity
If all non-relational data is stored as single blob entities, then storage simplicity is maintained, but the data cannot be decomposed and stored as multiple entities distributed over regular database fields
Solution Approach 1:
The patent creates a dynamic storage system where data can exist in multiple representations simultaneously. The same non-relational data can be stored as a complete blob for simplicity, while also being decomposed into structured components across multiple fields for flexibility. The system dynamically selects the appropriate representation based on query requirements, allowing both simplicity and versatility to coexist.
Data Source
AI summary
Many types of data files can only be stored in SQL databases as Blobs—Binary Large Objects, that the database can only handle as a whole. This limits searches within the Blob. Here improved methods are taught for automatically analyzing potential Blob files, and assigning at least certain classes of potential Blob files to General Data Type documents. In some cases, the potential Blob may be automatically analyzed and mapped to a JSON document type, and this JSON document type then automatically mapped into appropriate lists of JSON-document-nodes and tables that can be stored in the SQL database, and subsequently queried. This enables much data within the (to prior art) Blob to be queried at more detailed granularity. This data can then be retrieved and used to reconstruct some or all of the original General Data Type document, which would otherwise be a Blob to prior art methods.