Adaptive table structure generation and index optimization method for JSON data driven by real-time mode

Abstract

The invention discloses a self-adaptive table structure generation and index optimization method for JSON data driven by a real-time mode, and relates to the field of computer information management application. According to the self-adaptive table structure generation and index optimization method for the JSON data driven by the real-time mode, efficient and automatic persistence from the JSON data to a relational database is achieved through a four-layer technical architecture including dynamic analysis, intelligent mapping, self-healing expansion and index optimization. Comprising a dynamic mode analysis engine which analyzes a JSON nested structure in real time and generates a mode feature tree with weight; the self-adaptive storage engine is used for dynamically generating or expanding a table structure according to the mode feature tree and executing data type mapping; the index optimizer is used for automatically creating / cleaning indexes based on query log analysis; and the uniform interface layer is compatible with multi-database dialects and shields bottom layer differences. Dynamic table structure generation, intelligent type inference and storage optimization, self-healing Schema extension, query-driven index self-optimization and cross-database compatibility are realized.

Description

Technical Field

[0001] This invention relates to the field of computer information management applications, specifically to a method for adaptive table structure generation and index optimization of JSON data driven by real-time mode. Background Technology

[0002] With the development of the Internet of Things, microservice architecture, and real-time data acquisition technologies, JSON (JavaScript Object Notation) has become the mainstream format for heterogeneous data exchange. However, traditional technologies face the following problems when persisting dynamically changing JSON data to relational databases:

[0003] (1) Schema rigidity:

[0004] Existing ETL tools (such as Apache NiFi) require predefined database table structures (schemas), which cannot adapt to dynamic additions, deletions, or nested changes in JSON data fields. This leads to frequent manual modifications to the table structure or data loss. For example, when a new, undefined field appears in the JSON, system downtime is required to maintain the schema, severely impacting system availability.

[0005] (2) Low storage efficiency:

[0006] General-purpose solutions (such as storing the entire JSON as a CLOB or BLOB type) fail to leverage the strong typing and indexing advantages of relational databases, leading to wasted storage space (e.g., storing numbers as strings) and poor query performance. Some databases (such as PostgreSQL) support the JSONB type, but lack the ability to deeply parse and relationally decompose complex nested structures, making it difficult to support efficient join queries.

[0007] (3) Lack of index management:

[0008] Current technology relies on manual analysis of query patterns to create indexes, which cannot dynamically optimize indexing strategies based on the actual usage frequency of JSON fields. For example, if frequently queried fields are not indexed, a full table scan is required, causing response time to increase exponentially with the amount of data.

[0009] (4) Insufficient compatibility:

[0010] Existing dynamic schema solutions (such as MongoDB's document database) sacrifice the transaction support and complex query capabilities of relational databases, and cannot meet the strong demand for ACID properties and multi-table join operations in fields such as finance and industry. Summary of the Invention

[0011] The purpose of this invention is to address the aforementioned shortcomings by proposing a real-time mode-driven adaptive storage and indexing optimization method for JSON data. This method utilizes a four-layer architecture—dynamic parsing, intelligent mapping, self-healing expansion, and index optimization—to achieve efficient and automated persistence of JSON data to a relational database.

[0012] The present invention specifically adopts the following technical solution:

[0013] A real-time driven adaptive table structure generation and index optimization method for JSON data employs a four-layer architecture: dynamic parsing → intelligent mapping → self-healing expansion → index optimization, including:

[0014] A dynamic pattern parsing engine is used to parse nested JSON structures in real time and generate a weighted pattern feature tree.

[0015] An adaptive storage engine dynamically generates or expands the table structure based on the schema feature tree and performs data type mapping.

[0016] The index optimizer automatically creates / cleans indexes based on query log analysis.

[0017] A unified interface layer is provided, which is compatible with multiple database dialects and shields underlying differences.

[0018] Specifically:

[0019] (1) Real-time parsing and feature extraction of JSON pattern; Depth-first traversal (DFS) is used to parse JSON key-value pairs, identify nested objects {} and arrays [], and generate a pattern feature tree. The tree nodes record the field path, data type candidate set, frequency of occurrence and nesting depth.

[0020] (2) Adaptive table structure generation and storage optimization, dynamic table splitting strategy is as follows:

[0021] Main table generation: Store the root node fields in the main table. If the field value is globally unique and stable, set it as the primary key.

[0022] Related table generation: For nested objects or arrays, create related sub-tables and link them to the main table with foreign keys, supporting 1:1 or 1:N relationships;

[0023] Hybrid storage mode: If the nesting level exceeds the threshold, the deep structure will be stored as a JSONB field to retain flexibility;

[0024] Intelligent type mapping: Selects the final field type based on the feature tree weights;

[0025] (3) Self-healing schema extension and version management

[0026] Field change detection: Real-time comparison of field paths in new JSON data with the current schema;

[0027] Add a new field: If the field is not defined, the column will be automatically added using ALTER TABLE or by executing the SQL statement ALTER TABLE addcolumn.

[0028] Field deprecation: If a field has not appeared for a long time, mark it as "archiveable";

[0029] Versioned migration:

[0030] Metadata version table: Records the version number, change time, and field change details for each schema change;

[0031] Data compatibility: When writing data, a version number is appended, and the table structure is dynamically adapted based on the version number during querying to ensure that historical data is accessible;

[0032] (4) Query-driven index self-optimization

[0033] High-frequency field identification: Analyze database query logs, count the frequency of field usage in WHERE, JOIN, and ORDER BY clauses, and generate a hot field ranking list;

[0034] The workflow for automated index management is as follows:

[0035] Index creation: Automatically create indexes for the top-N frequently used fields; if the field is a JSONB path, create a GIN index.

[0036] Index eviction: Periodically check unused indexes and automatically execute DROP INDEX to release storage space;

[0037] Composite Index Recommendation: For fields frequently used in join queries, composite index suggestions are generated based on association rule mining;

[0038] (5) Unified interface layer across databases

[0039] Implement a dialect translator that converts JSON schema feature trees into schema definition statements for the target database, transforms nested arrays into sub-tables, and specifies the InnoDB engine; PostgreSQL: use the JSONB type for deeply nested fields and leverage GIN indexes to accelerate queries.

[0040] The present invention has the following beneficial effects:

[0041] Significantly improved storage efficiency: Based on intelligent type inference and dynamic table structure splitting, it automatically selects the optimal storage format, reduces redundant data occupation, and improves storage resource utilization; it supports flexible storage of nested data (relational fields and JSON mixed mode), balancing data standardization and scalability.

[0042] Comprehensive optimization of query performance: Through automated indexing strategies, frequently queried fields are dynamically identified and adapted indexes are generated, accelerating data retrieval efficiency; reducing the computational overhead caused by complex nested parsing, and improving the response speed of multi-table join queries and range queries.

[0043] Significantly reduced operation and maintenance costs: The self-healing table structure expansion and version management mechanism automates the entire schema change process, reducing the need for manual intervention; the adaptive index cleanup strategy avoids the impact of redundant indexes on storage and write performance.

[0044] (Enhanced compatibility and scalability: Supports dynamic adaptation across database engines, reducing the difficulty of migrating heterogeneous systems; can flexibly respond to changes in JSON data structure, suitable for highly dynamic business scenarios such as IoT, real-time logs, and financial transactions.)

[0045] This invention breaks through the limitations of traditional static schemas, realizing intelligent optimization of the entire JSON data chain from storage to query. While ensuring data consistency and query efficiency, it significantly reduces system maintenance complexity and provides a general solution for highly dynamic data scenarios. Attached Figure Description

[0046] Figure 1 This is a schematic diagram of a real-time mode-driven adaptive storage and indexing optimization method for JSON data. Figure 2 Example diagram of real-time parsing and feature extraction of JSON pattern; Figure 3 This is a diagram illustrating an example of JSON data reported by an IoT device. Detailed Implementation

[0047] The specific embodiments of the present invention will be further described below with reference to the accompanying drawings and specific examples:

[0048] Combination Figure 1 A real-time driven adaptive table structure generation and index optimization method for JSON data employs a four-layer architecture: dynamic parsing → intelligent mapping → self-healing expansion → index optimization, including:

[0049] A dynamic pattern parsing engine is used to parse nested JSON structures in real time and generate a weighted pattern feature tree.

[0050] The adaptive storage engine dynamically generates or expands the table structure based on the schema feature tree and performs data type mapping.

[0051] The index optimizer automatically creates / cleans indexes based on query log analysis.

[0052] A unified interface layer is used to ensure compatibility with multiple database dialects and shield underlying differences.

[0053] Specifically:

[0054] (1) Real-time parsing and feature extraction of JSON patterns: Depth-first traversal (DFS) is used to parse JSON key-value pairs, identify nested objects ({}) and arrays ([]), generate pattern feature trees, and record field paths (such as user.address.city), data type candidate sets, frequency of occurrence and nesting depth in tree nodes.

[0055] like Figure 2 As shown in the example: { "user": { "id": 1001, "tags": ["A", "B"]}}

[0056] After parsing, a feature tree is generated:

[0057] Root node: user (object type, frequency = 1)

[0058] Child node 1: user.id (numeric type, value range = 1001 → candidate type SMALLINT)

[0059] Child node 2: user.tags(array type, element type = string, maximum length = 1)

[0060] Weight calculation: Based on the frequency of field occurrence, the range of numerical values ​​(e.g., -128~127 → TINYINT), and the string pattern matching results (e.g., ^\d{4}-\d{2}$ → DATE), the confidence weight of each candidate type is calculated for the storage engine to make decisions.

[0061] (2) Adaptive table structure generation and storage optimization, dynamic table splitting strategy is as follows:

[0062] Main table generation: Store the root node field (such as user.id) into the main table. If the field value is globally unique and stable, set it as the primary key.

[0063] Related table generation: For nested objects or arrays (such as user.tags), create related sub-tables and foreign keys to the main table, supporting 1:1 or 1:N relationships.

[0064] Hybrid storage mode: If the nesting level exceeds a threshold (e.g., ≥3 levels), this threshold can be set to be configurable. It mainly depends on actual business needs; JSON will require slightly more storage space than varchar. This is determined by the user's assessment of the data volume and server resources, storing deep structures as JSONB fields or other field types similar to JSONB in ​​the database.

[0065] Retain flexibility.

[0066] Intelligent type mapping: Selects the final field type based on feature tree weights; two simple examples are as follows:

[0067] Numeric type: If the value is in the INT range and has no decimal, choose INT instead of BIGINT; String type: Use regular expression matching to identify special formats (such as IP address, email), and choose VARCHAR (precise length) if the match is successful.

[0068] Fault tolerance mechanism: If subsequent data conflicts with the initial type (such as a number appearing in a string), a type upgrade is triggered (such as VARCHAR→TEXT) and an exception log is recorded.

[0069] (3) Self-healing schema extension and version management

[0070] Field change detection: Real-time comparison of field paths in new JSON data with the current schema.

[0071] Adding a new field: If the field is not defined, the column will be automatically added using ALTER TABLE or by executing the SQL statement ALTER TABLE add column.

[0072] Field deprecation: If a field has not appeared for a long period of time (e.g., no data for 30 days), it is marked as "archiveable".

[0073] Versioned migration:

[0074] Metadata version table: Records the version number, change time, and field change details for each schema change.

[0075] Data compatibility: When writing data, a version number is appended, and the table structure is dynamically adapted based on the version number during queries to ensure that historical data is accessible.

[0076] (4) Query-driven index self-optimization

[0077] High-frequency field identification: Analyze database query logs to count the frequency of field usage in WHERE, JOIN, and ORDER BY clauses, and generate a hot field ranking list.

[0078] The workflow for automated index management is as follows:

[0079] Index creation: Automatically create indexes (such as B-Tree indexes) for Top-N frequently used fields. If the field is a JSONB path (such as data->>'email'), create a GIN index.

[0080] Index eviction: Periodically check unused indexes (via pg_stat_user_indexes) and automatically execute DROPINDEX to release storage space.

[0081] Composite index recommendation: For fields frequently used in join queries (such as user_id + timestamp), generate composite index suggestions based on association rule mining.

[0082] (5) Unified interface layer across databases

[0083] Implement a dialect translator that converts JSON schema feature trees into schema definition statements for the target database, transforms nested arrays into sub-tables, and specifies the InnoDB engine. For PostgreSQL, use the JSONB type for deeply nested fields and leverage GIN indexes to accelerate queries. The key is to determine the field type of each field in the JSON when converting JSON to table creation statements, as the definition of field types differs for each data source type. This unified interface layer primarily handles this.

[0084] like Figure 3 As shown, taking the JSON data reported by an IoT device as a practical example: { "device_id": "D001", "temp": 25.3, "location": { "lat": 39.9, "lng": 116.4}}, after the first JSON data is reported, the table will be dynamically created. According to the example above, the main table structure is device: device_id (VARCHAR(4)), temp (DECIMAL(3,1)); the sub-table structure is location: device_id (foreign key), lat (DECIMAL(3,1)), lng (DECIMAL(4,1)). Suppose that the JSON data adds a field "voltage": 22, then the schema will be automatically expanded, that is, the voltage (SMALLINT) column will be automatically added to the device table. Index optimization: If frequent queries WHERE temp > 30 are detected, a B-Tree index will be automatically created for the temp column.

[0085] The above optimization method achieves the following:

[0086] Dynamic table structure generation: By parsing the nested structure and field characteristics of JSON data in real time, it automatically generates optimized multi-level relational tables (main table - sub-table) or hybrid storage models (relational fields + JSONB fields), without the need for manual pre-definition of schema, and supports lossless persistence of any JSON format.

[0087] Intelligent type inference and storage optimization: Based on numerical range statistics, string pattern matching (such as date and email regular expressions) and nesting depth analysis, it automatically selects the most accurate data type (such as INT / BIGINT / VARCHAR(256)), reducing storage space usage by 30%-60% while improving range query performance.

[0088] Self-healing schema expansion: When a new field is detected in the JSON data, the table structure expansion is automatically triggered (such as ALTER TABLE to add columns or create extended related tables), and the schema version history is recorded to ensure the continuity of data writing and compatibility with historical versions, reducing the complexity of operation and maintenance.

[0089] Query-driven index self-optimization: By monitoring SQL query logs, frequently accessed fields and query conditions are identified, and indexes (such as B-Tree indexes and GIN indexes) are automatically created for dynamically added fields, reducing the response time of typical queries from seconds to milliseconds. At the same time, invalid indexes are automatically cleaned up through index usage analysis.

[0090] Cross-database compatibility: Provides a unified intermediate abstraction layer, supporting the dynamic adaptation of the same JSON data to mainstream relational databases such as MySQL, PostgreSQL, and Oracle, reducing the migration cost of heterogeneous systems and improving the universality of technical solutions.

[0091] It breaks through the limitations of traditional static schemas, realizes intelligent optimization of the entire chain of JSON data from storage to query, and significantly reduces the system maintenance complexity while ensuring data consistency and query efficiency, providing a general solution for highly dynamic data scenarios.

[0092] Of course, the above description is not intended to limit the present invention, and the present invention is not limited to the examples given above. Any changes, modifications, additions or substitutions made by those skilled in the art within the scope of the present invention should also fall within the protection scope of the present invention.

Claims

1. A method for adaptive table structure generation and index optimization of JSON data in real-time schema driven mode, characterized in that, Adopting four-layer architecture of dynamic analysis→intelligent mapping→self-healing extension→index optimization, including: Dynamic schema analysis engine, used for real-time analysis of JSON nested structure, generating schema feature tree with weight; Adaptive storage engine, dynamically generating or extending table structure according to schema feature tree, and performing data type mapping; Index optimizer, automatically creating / cleaning up indexes based on query log analysis; Unified interface layer, compatible with multiple database dialects, shielding underlying differences; Specifically: (1) Real-time JSON schema analysis and feature extraction; DFS is used to parse JSON key-value pairs, identify nested objects {} and arrays [], generate schema feature tree, and tree nodes record field path, data type candidate set, frequency and nesting depth; (2) Adaptive table structure generation and storage optimization, dynamic table splitting strategy as follows: Main table generation: store root node fields into main table, if field value is globally unique and stable, set as primary key; Association table generation: for nested objects or arrays, create associated sub-table and foreign key association main table, support 1:1 or 1:N relationship; Mixed storage mode: if the nesting level exceeds the threshold, store deep structure as JSONB field, retain flexibility; Intelligent type mapping: select the final field type according to the weight of the feature tree; (3) Self-healing schema extension and version management Field change detection: real-time comparison of new JSON data field path and current schema; New field: if the field is not defined, automatically add column ALTER TABLE or execute ALter table add column sql statement; Field deprecation: if the field has not appeared for a long time, mark it as "archivable"; Version migration: Metadata version table: record version number, change time and field change details of each schema change; Data compatibility: append version number when writing data, dynamically adapt table structure according to version number when querying, ensure that historical data is accessible; (4) Query-driven index self-optimization High-frequency field identification: analyze database query logs, count the frequency of fields in WHERE, JOIN and ORDER BY clauses, and generate a hot field ranking list; Automatic index management process as follows: Index creation: automatically create indexes for Top-N high-frequency fields, if the field is a JSONB path, create GIN index; Index elimination: periodically detect unused indexes, automatically execute DROP INDEX to release storage space; Composite index recommendation: for frequently joined fields, generate composite index suggestions based on association rule mining; (5) Cross-database unified interface layer Implement a dialect translator to convert JSON schema feature tree into target database schema definition statements, convert nested arrays into sub-tables and specify InnoDB engine; PostgreSQL: use JSONB type for deep nested fields, use GIN index to speed up query.

Images