Non-Unique Secondary Index for JSON Data Access
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face significant performance challenges when processing semi-structured data, particularly JSON data, due to the need for full scans of large JSON columns, which are costly in terms of time and resources, especially when searching for specific attribute-value pairs.
Innovation Solution
The implementation of an enhanced Non-Unique Secondary Index (NUSI) structure that allows for direct access to specific values within JSON documents by storing offsets or values of immediate parent objects, enabling object-level access instead of row-level access, thereby reducing the need for full table scans and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If full scans of large JSON columns are performed to search for specific attribute-value pairs, then complete data coverage is achieved, but time consumption and resource usage increase significantly
Solution Approach 1:
The patent creates indexes on JSON columns before queries are executed. These indexes pre-process and organize the semi-structured data, allowing the database system to quickly locate specific attribute-value pairs without performing full table scans during query execution. The index creation happens in advance, transforming the costly full-scan operation into a targeted lookup operation.
Solution Approach 2:
The patent introduces an intermediate index structure that sits between the raw JSON data and the query processing mechanism. This index acts as a mediator that translates query requirements into efficient data access patterns, eliminating the need for complete JSON column scans while ensuring accurate retrieval of matching records.
2Productivity
If traditional row-level indexing is used for JSON data, then simple queries can be optimized, but object-level access and complex queries on nested structures remain inefficient
Solution Approach 1:
The patent segments the JSON data structure into hierarchical levels, creating indexes that operate at the object level rather than treating JSON data as monolithic row-level structures. This segmentation allows the index to navigate nested structures and access specific objects within JSON documents efficiently, supporting complex queries on semi-structured data.
Solution Approach 2:
The patent adds a new dimension to the indexing approach by introducing object-level indexing that operates alongside traditional row-level indexes. This dimensional enhancement allows the system to access JSON data at multiple granularities, enabling both simple row-level queries and complex object-level queries to be optimized simultaneously.
3Speed
If indexes are created on JSON columns to improve query performance, then search speed increases, but storage space and index maintenance overhead increase
Solution Approach 1:
The patent applies local quality by creating indexes selectively on specific JSON columns and objects based on query patterns and data characteristics, rather than indexing entire JSON columns uniformly. This targeted approach ensures that index storage is allocated only where it provides performance benefits, reducing overall storage overhead while maintaining search speed for critical query paths.
Data Source
AI summary
Various embodiments herein each include at least one of systems, methods, and software for generating, storing, and using non-unique secondary indexes of semi-structured data in database tables. One method embodiment includes creating and storing a non-unique secondary index (NUSI) for a database table based on column data stored in rows of the database table. The NUSI in some such embodiments identifying an offset to specific data included in the column data of at least some rows of the database table where the specific data is located. Other embodiments include building a NUSI index that includes values embedded therein.