A time series database writing method and system for heterogeneous JSON data based on an expression engine

Abstract

This invention discloses a method and system for writing heterogeneous JSON data to a time-series database based on an expression engine. Multiple access rules are defined through a TOML configuration file to register independent HTTP endpoints for different data sources. The JSONata functional expression engine declaratively flattens multi-level nested JSON data of arbitrary formats into a standard record array. Flexible metadata extraction is achieved through dynamic key mapping and static value mutual exclusion configuration. Adaptive SQL column name generation for optional fields is supported. A four-level sorting and length-aware automatic sharding strategy are used to generate efficient batch INSERT statements. A trial run mode is provided for expression debugging and verification. This invention solves the problems of format adaptation difficulties, hard-coded conversion logic, and lack of declarative configuration capabilities when accessing heterogeneous JSON data to a time-series database. It has advantages such as high flexibility, zero-code access, high-throughput batch writing, and security and debuggability.

Description

Technical Field

[0001] This invention relates to the field of IoT data acquisition and time-series database access technology, and in particular to a method for writing heterogeneous JSON data to a time-series database based on an expression engine. The method and system receive JSON data of any format through a RESTful HTTP interface, use the JSONata expression engine to convert it into the structured record format required by the time-series database in real time, and automatically generate batch writing SQL statements. Background Technology

[0002] With the rapid development of the Internet of Things (IoT) and the Industrial Internet, a large number of devices and sensors continuously generate time-series data. This data is typically reported by different manufacturers and systems in their own defined JSON formats via HTTP interfaces. Writing this heterogeneous JSON data into a time-series database (such as TDengine) presents the following technical challenges:

[0003] Data format heterogeneity issue: JSON data generated by different data sources has various formats. Some use a flat structure, some use a multi-level nested structure, and some use dynamic key names to express dimensional information such as device and group. Existing time-series data writing interfaces usually require data to conform to a fixed format (such as InfluxDB Line Protocol, OpenTSDB JSON format), and cannot directly accept JSON data with arbitrary structures.

[0004] Hard-coded data conversion logic: In traditional solutions, the logic of converting heterogeneous JSON into time-series database records usually requires writing dedicated middleware or adapter programs. New conversion code needs to be developed and deployed for each new data format, resulting in high maintenance costs and poor flexibility.

[0005] Lack of declarative transformation capabilities: The existing HTTP JSON write interface lacks declarative data transformation capabilities, and users cannot declare how to extract the table names, timestamps, tags, and data fields required by the time series database from any JSON through simple configuration.

[0006] Batch write efficiency issues: Simple one-by-one conversion and writing methods cannot meet the requirements of high-throughput scenarios. Multiple records need to be merged into batch SQL statements. At the same time, practical engineering constraints such as SQL length limits and automatic table creation also need to be considered.

[0007] Optional field handling is difficult: In real-world scenarios, different records of the same batch of data may contain different sets of fields (some sensors do not support certain indicators), requiring the system to be able to adaptively handle missing fields rather than simply reporting an error.

[0008] Debugging and verification are difficult: Users lack debugging tools when configuring conversion expressions, making it difficult to easily verify whether the conversion results are correct and whether the generated SQL meets expectations.

[0009] Therefore, the industry urgently needs a time-series data access solution that supports declarative configuration, can handle JSON data of any format, has adaptive field mapping, and efficient batch writing capabilities. Summary of the Invention

[0010] To address the shortcomings of the existing technologies, this invention provides a method and system for real-time conversion of heterogeneous JSON data and writing to a time-series database based on an expression engine.

[0011] According to a first aspect of the present invention, a method for writing heterogeneous JSON data to a time-series database based on an expression engine includes:

[0012] Step S1 of the HTTP endpoint dynamic registration based on configuration rules is as follows: according to the HTTP endpoint identifier configuration item in the configuration file, the corresponding POST request processing path is dynamically registered on the HTTP router so that different data sources can submit JSON data through their own independent HTTP endpoints.

[0013] Step S2 of the data structure transformation based on the JSONata expression engine is as follows: After receiving the HTTP request containing JSON data submitted by the data source, the JSONata expression engine is called to perform evaluation operations on the original JSON data according to the transformation expression configuration items in the configuration file, and the heterogeneous JSON structure with arbitrary nesting levels is converted into a flat record array.

[0014] Step S3 of dynamic field mapping and record parsing is as follows: Parse each record in the converted record array and dynamically extract the database name, super table name, sub-table name and timestamp value from each record according to the key mapping configuration item in the configuration file;

[0015] Step S4 of the optional field awareness is as follows: According to the field list configuration items in the configuration file, extract the corresponding data field from each record. The data field supports two modes: required field and optional field. When the optional field is missing in the record, automatically generate a column name statement fragment containing only the existing field to realize that different records in the same batch are written to different column sets.

[0016] Step S5 of sorting, merging, and batch writing of shards is as follows: All parsed records are sorted in four levels according to the super table name, sub-table name, column name set, and timestamp to generate SQL, so that records with the same table structure are arranged adjacently; then INSERT statements are generated in sorting order, and when the statement length is close to the preset maximum SQL length, sharding is performed, splitting the data in a single HTTP request into multiple SQL statements for sequential execution, and each statement realizes the integrated operation of automatic table creation and data writing through the super table automatic table creation syntax.

[0017] The time-series database writing method of the present invention further includes a dual-mode execution step S6 of trial run mode and actual writing mode, specifically: in trial run mode, the converted JSON data and generated SQL statements are returned but no actual writing operation is performed, for debugging and verifying the correctness of the conversion expression; in normal mode, a database connection is obtained through the connection pool, all SQL statements are executed sequentially and the number of affected rows is summarized, and the writing result is returned.

[0018] Preferably, in step S2, when the JSONata expression engine evaluates the original JSON, if the result is a single-element array, it automatically extracts the values ​​in the array; when writing conversion expressions, users need to use the [] operator to explicitly generate the array to ensure the correct generation of batch records.

[0019] Preferably, in step S3, the mapping of database name, super table name, and sub-table name adopts a method of extracting mutually exclusive configurations using static values ​​and dynamic keys. Only one of each pair of parameters can be set, and the system forcibly checks this mutual exclusion constraint during the configuration verification phase.

[0020] Preferably, in step S3, timestamp parsing supports 12 predefined formats, including unix, unix_ms, unix_us, unix_ns, ansic, rubydate, rfc822z, rfc1123z, rfc3339, rfc3339nano, stamp, stampmilli, datetime, and any strftime custom format string, and supports specifying the time zone used for time parsing through time zone configuration (Timezone).

[0021] Preferably, in step S4, when the adaptive column name generation mechanism detects that the first optional field is missing, it switches to the field-by-field column name accumulation mode. When a field that exists is encountered subsequently, it is appended to the column name statement, so that different records in the same batch can be written to different column sets, without all records needing to have the exact same field structure.

[0022] Preferably, in step S5, the SQL statement uses the TDengine super table automatic table creation syntax, with the format INSERT INTO. <db> . <stb> ( <tbname> , <ts> , <field1> , <field2>,...) VALUES ('<sub_table>',' <timestamp> ', <val1> , <val2>(,...), reuse the supertable declaration part between consecutive records in the same supertable with the same column set, only add the VALUES clause, reducing the size of the SQL statement.

[0023] According to a second aspect of the present invention, a time-series database writing system for heterogeneous JSON data based on an expression engine includes:

[0024] The rule configuration module is used to parse and validate TOML format configuration files. The configuration file contains global switches and a list of rules. Each rule contains configuration items such as endpoint identifier, database mapping, super table mapping, sub-table mapping, time configuration, transformation expression, and field list.

[0025] The expression compilation module is used to precompile the JSONata transformation expression in each rule into an executable expression object during the system startup phase, and at the same time precompile the strftime format time template into a Go language time layout string, and preload the time zone string into a time zone object;

[0026] The HTTP access module is used to implement RESTful interfaces based on the Gin Web framework. It registers a POST processor with authentication middleware for each configured HTTP endpoint and assigns or parses a request identifier for distributed tracing for each request.

[0027] The data conversion module receives raw JSON data from HTTP requests and performs structural conversion using pre-compiled JSONata expressions, converting JSON of any format into a flat record array format acceptable to time-series databases.

[0028] The record parsing module is used to parse the converted JSON byte stream into Go language map slices using a JSON decoder that supports precise numerical parsing. Then, according to the mapping configuration in the rules, it extracts the database name, super table name, sub-table name, timestamp and each data field one by one, and escapes the backticks in the identifiers to prevent SQL injection.

[0029] The SQL generation module is responsible for sorting the parsed records and generating batch INSERT SQL statements. It adopts a four-level sorting strategy of super table → sub-table → column set → timestamp to make data with the same structure adjacent. It generates INSERT statements using the TDengine super table automatic table creation syntax and automatically shards INSERT statements when they are too long. It also escapes special characters in string values, including newline characters, carriage return characters, tab characters, single quotes, double quotes, backslashes, and null characters.

[0030] The monitoring metrics module is used to maintain an independent metric collector for each endpoint, and to count the total number of rows, the number of successful rows, the number of failed rows, the number of rows in transit, and the number of affected rows in real time.

[0031] Preferably, the rule configuration module performs validity checks on endpoint identifiers, allowing only character combinations containing English letters, numbers, underscores, and hyphens; it also performs security checks on identifiers such as database names and super table names, prohibiting the inclusion of backticks to prevent SQL injection attacks.

[0032] Preferably, the SQL generation module returns an error when the SQL value of a single record exceeds the maximum SQL length limit, and automatically performs sharding when multiple records exceed the limit. Each shard independently contains a complete INSERT statement header and a super table declaration.

[0033] Preferably, the parsing module uses json.Decoder in conjunction with the UseNumber() option to decode JSON, keeping the numeric type as a json.Number string representation, thus avoiding precision loss of large integers during JSON decoding.

[0034] This invention solves the problems of format adaptation difficulties, hard-coded conversion logic, and lack of declarative configuration capabilities when integrating heterogeneous JSON data into time-series databases. It also has the advantages of high flexibility, zero-code integration, high-throughput batch writing, and security and debuggability. Attached Figure Description

[0035] Figure 1 This is a schematic diagram of a data processing pipeline for writing heterogeneous JSON data to a time-series database based on an expression engine, according to the present invention.

[0036] Figure 2 This is a flowchart of the timestamp parsing process of the present invention;

[0037] Figure 3 This is a logic diagram for processing optional fields in this invention;

[0038] Figure 4 This is a schematic diagram of a time-series database writing system for heterogeneous JSON data based on an expression engine, according to the present invention. Detailed Implementation

[0039] like Figure 1 As shown, the data processing pipeline of this invention is as follows: HTTP request reception → endpoint route matching → JSONAta expression conversion → record parsing → SQL generation and sharding → batch execution and writing.

[0040] This invention provides a method for real-time conversion and time-series database writing of heterogeneous JSON data based on an expression engine, applicable to scenarios where JSON data of arbitrary structure is accessed into a time-series database. The method comprises the following steps:

[0041] S1. Dynamic registration of HTTP endpoints based on configuration rules: When the system starts, it parses multiple access rules in the configuration file. Each rule defines a unique HTTP endpoint identifier and dynamically registers the corresponding POST request processing path on the HTTP router, forming a RESTful interface in the format / input_json / v1 / {endpoint}, so that different data sources can submit JSON data through their own independent endpoints;

[0042] S2. Data structure transformation based on the JSONata expression engine: Upon receiving an HTTP request, if a transformation expression is defined in the rule, the JSONata expression engine is called to perform an evaluation (Eval) operation on the original JSON data. The JSONata expression supports operations such as object traversal ($each), array expansion ([]), sorting ($sort), string splitting ($split), and type conversion ($number), flattening heterogeneous JSON structures with arbitrary nesting levels into a unified record array. Each record contains metadata required by the time-series database, such as the database name, super table name, sub-table name, timestamp, and data fields.

[0043] S3. Dynamic Field Mapping and Record Parsing: The converted record array is parsed record by record. Based on the key mapping configuration (DBKey / SuperTableKey / SubTableKey / TimeKey) in the rules, the database name, super table name, sub table name, and timestamp value are dynamically extracted from each record. It supports mutually exclusive configuration of static value and dynamic extraction modes. The timestamp parsing supports multiple time formats such as Unix timestamps (seconds / milliseconds / microseconds / nanoseconds), RFC3339, and custom strftime format, and supports time zone configuration.

[0044] S4. Optional Field Aware Adaptive SQL Generation: Based on the field list defined in the rules, extract the corresponding data field values ​​from each record. It supports two modes: required fields and optional fields. When an optional field is missing in a record, it automatically generates a statement fragment containing only the column names of the existing fields, realizing the adaptive capability of writing different records in the same batch to different column sets.

[0045] S5. Sorting, Merging, and Sharding Batch Write: All parsed records are sorted in four levels: super table name, sub-table name, column name set, and timestamp, ensuring records with the same table structure are arranged adjacently. Then, INSERT statements are generated in sorted order. When the statement length approaches the preset maximum SQL length (default 1MB), sharding is performed, splitting the data from a single HTTP request into multiple SQL statements executed sequentially. Each statement uses the super table's automatic table creation syntax, INSERT INTO. <stb> ( <tbname> , <columns>The VALUES(...) function enables integrated automatic table creation and data writing.

[0046] S6. Dual-mode execution of trial run mode and actual write mode: The system supports enabling trial run mode via the URL parameter dry_run=true. In this mode, the converted JSON data and generated SQL statements are returned, but no actual write operation is performed. This is used to debug and verify the correctness of the conversion expression. In normal mode, a database connection is obtained through the connection pool, all SQL statements are executed sequentially, the number of affected rows is summarized, and the write result is returned.

[0047] In step S2 above, when the JSONata expression engine evaluates the original JSON, if the result is a singleton array, it automatically extracts the values ​​within the array. Therefore, when writing conversion expressions, users need to explicitly generate the array using the [] operator, for example, using $each($, function($v,$k) { ...})[] instead of $each($, function($v,$k) { ...}) to ensure the correct generation of batch records.

[0048] In step S3 above, the mapping of database name, supertable name, and subtable name adopts a mutually exclusive configuration method of static value (DB / SuperTable / SubTable) and dynamic key extraction (DBKey / SuperTableKey / SubTableKey). Only one of each pair of parameters can be set, and the system forcibly checks this mutual exclusion constraint during the configuration verification phase.

[0049] In step S3 above, timestamp parsing supports 12 predefined formats, including unix, unix_ms, unix_us, unix_ns, ansic, rubydate, rfc822z, rfc1123z, rfc3339, rfc3339nano, stamp, stampmilli, datetime, and any strftime custom format string. It also supports specifying the time zone used for time parsing through time zone configuration (Timezone).

[0050] In step S4 above, when the first optional field is missing, the adaptive column name generation mechanism switches to the field-by-field column name accumulation mode. When an existing field is encountered, it is appended to the column name statement, so that different records in the same batch can be written to different column sets, without all records needing to have the exact same field structure.

[0051] In step S5 above, the SQL statement uses the TDengine super table automatic table creation syntax, with the format INSERT INTO. <db> . <stb> ( <tbname> , <ts> , <field1> , <field2>,...) VALUES ('<sub_table>',' <timestamp> ', <val1> , <val2>(,...), reuse the supertable declaration part between consecutive records in the same supertable with the same column set, only add the VALUES clause, reducing the size of the SQL statement.

[0052] This invention provides a heterogeneous JSON data time-series database writing system based on an expression engine. Figure 4 As shown, it includes:

[0053] Rule configuration module: Responsible for parsing and validating TOML format configuration files. The configuration file contains global switches (Enable) and rule lists (Rules). Each rule contains configuration items such as endpoint identifier, database mapping, super table mapping, sub-table mapping, time configuration, transformation expression and field list. The module performs strict validation of the configuration when the system starts, including checks on endpoint validity, mapping method mutual exclusion, identifier security, etc.

[0054] Expression compilation module: During system startup, the JSONata transformation expression in each rule is pre-compiled into an executable expression object (Expr). This expression object can be reused in each subsequent request processing to avoid the overhead of repeated compilation at runtime. At the same time, the strftime format time template is pre-compiled into a Go language time layout string (Layout), and the time zone string is preloaded into a time zone object (Location).

[0055] HTTP Access Module: Implements RESTful interfaces based on the Gin Web framework, registers a POST processor with authentication middleware for each configured endpoint, supports both Basic Auth and Token authentication methods, and assigns or parses a request identifier (ReqID) for each request for distributed tracing.

[0056] Data Conversion Module: Receives raw JSON data from the HTTP request body, performs structure conversion using pre-compiled JSONata expressions, and converts JSON of any format (including multi-level nesting, dynamic key names, and heterogeneous structures) into a flat record array format acceptable to time-series databases;

[0057] Record parsing module: Uses a JSON decoder that supports precise numerical parsing (configure the UseNumber option to avoid loss of precision for large integers) to parse the converted JSON byte stream into Go language map slices, and then extracts the database name, super table name, sub-table name, timestamp and each data field one by one according to the mapping configuration in the rules, and escapes the backticks in the identifiers to prevent SQL injection;

[0058] The SQL generation module is responsible for sorting the parsed records and generating batch INSERT SQL statements. It uses a four-level sorting strategy (super table → sub-table → column set → timestamp) to make data with the same structure adjacent. It generates INSERT statements using the TDengine super table automatic table creation syntax and automatically shards statements when they are too long. It also escapes special characters (newline, carriage return, tab, single quote, double quote, backslash, null) in string values.

[0059] Monitoring Metrics Module: Maintains an independent metric collector for each endpoint to collect real-time statistics on the total number of rows, successful rows, failed rows, inflight rows, and affected rows, which is used for building system observability.

[0060] The rule configuration module performs validity checks on endpoint identifiers, allowing only character combinations containing English letters, numbers, underscores, and hyphens; it also performs security checks on identifiers such as database names and super table names, prohibiting the inclusion of backticks to prevent SQL injection attacks.

[0061] The SQL generation module returns an error when the SQL value of a single record exceeds the maximum SQL length limit. When multiple records exceed the limit, it automatically performs sharding, with each shard independently containing a complete INSERT statement header and a super table declaration.

[0062] The record parsing module uses json.Decoder with the UseNumber() option to decode JSON, keeping the numeric type as a json.Number string representation instead of float64, thus avoiding precision loss of large integers (such as 64-bit integers) during JSON decoding.

[0063] The key technologies of this invention will be described in detail below.

[0064] This invention defines access rules using a TOML format configuration file, and its configuration hierarchy is as follows:

[0065]

[0066] • Endpoint identifiers are only allowed to contain letters, numbers, underscores, and hyphens;

[0067] • The parameters db / dbKey, superTable / superTableKey, and subTable / subTableKey are mutually exclusive;

[0068] • Identifiers must not contain backticks;

[0069] • The field list cannot be empty, and field keys cannot be duplicated;

[0070] JSONata is a lightweight, functional JSON query and transformation language whose core features make it particularly suitable for transforming the structure of time-series data.

[0071] Transformation example: Assume the original JSON is organized in a three-level nested manner: "Region → Group → Device":

[0072]

[0073] The following JSONata expression can be used to flatten it into an array of records:

[0074]

[0075] The conversion result is a flat array of records, each containing all the metadata required for writing to the time-series database.

[0076] Expression pre-compilation mechanism: During the startup phase, the system compiles JSONata expressions into jsonata.Expr objects and caches them in the ParsedRule structure. Subsequent requests directly call the EvalBytes() method to perform evaluation, avoiding repeated compilation.

[0077] The record parser maps each record in the JSON array to an internal record structure:

[0078]

[0079] Figure 2 The timestamp parsing process is shown. Step 1: Input time string + format configuration + location configuration; Step 2: Determine if it is a predefined format; Step 3: If yes, parse according to the predefined format, then proceed to Step 5; Step 4: If no, parse according to the strftime custom format, then proceed to Step 5; Step 5: Apply location configuration; Step 6: Return time.

[0080] Figure 3 The optional field processing logic is shown. Step 1: Iterate through the field list; Step 2: Retrieve the next field; Step 3: Determine if the field exists in the record; Step 4: If it exists, append it to the value buffer; Step 5: Determine if incomplete mode has been entered; Step 6: If yes, append the field name to the column name buffer, then proceed to Step 7; Step 7: Determine if there are more fields. If yes, return to Step 2; otherwise, end the process. Note that if Step 5 determines that incomplete mode has not been entered, then proceed to Step 7.

[0081] If, in step 3, it is determined that the field does not exist in the record, then in step 8, it is determined whether the field marker is optional. If so, in step 9, it is determined whether this is the first time a missing field has been encountered. If so, in step 10, the process switches to incomplete mode to rebuild the column name buffer, and then skips the field to proceed to step 7. If, in step 9, it is determined that this is not the first time a missing field has been encountered, then the field is skipped to proceed to step 7. If, in step 8, it is determined that the field marker is not optional, then an error (required field missing) is returned.

[0082] The SQL generator of this invention employs the following strategy to achieve efficient batch writing:

[0083] Level 4 sorting:

[0084] Level 1: Sort by the full path of the super table (db.stb);

[0085] Second level: Sort by sub-table name;

[0086] Level 3: Sort by column name set (different combinations of optional fields produce different column sets);

[0087] Level 4: Sort by timestamp.

[0088]

[0089] SQL generation format: When consecutive records with the same column set appear in the same super table, only the VALUES clause is appended without repeating the super table declaration, thus reducing the size of the SQL statement.

[0090] Automatic fragmentation rules:

[0091] An error is returned if the SQL value portion of a single record exceeds the maximum length limit.

[0092] If the cumulative SQL length exceeds the maximum limit (default 1MB), the currently generated SQL will be output as an independent shard, and a new shard will be started.

[0093] If the first record of the new shard belongs to the same supertable as the last record of the previous shard, the supertable declaration will be automatically added to the new shard.

[0094] The system protects against security risks at multiple levels:

[0095] Authentication control: Each endpoint is configured with authentication middleware, supporting both HTTP Basic Auth and Token authentication methods.

[0096] SQL Injection Protection:

[0097] Identifiers (database names, table names, etc.) are enclosed in backticks, and the backticks themselves are escaped by double-writing (` → ````).

[0098] String values ​​are enclosed in single quotes, and special characters inside are escaped. (Filtering characters such as \r, \t, '→', "→\", \→\\, and empty characters).

[0099] During the configuration verification phase, backticks are prohibited in identifiers.

[0100] Request tracing: Assign a unique ReqID to each request and log it throughout the entire processing chain.

[0101] This invention provides a trial operation mode for debugging and verification:

[0102] Request method: Add the parameter ?dry_run=true to the URL.

[0103] Returned content:

[0104]

[0105] In trial mode, after completing JSONata conversion, record parsing, and SQL generation, no actual database write operations are performed. Instead, the converted JSON string and the generated SQL statement array are directly returned to the client. This allows users to:

[0106] • Verify that the conversion result of the JSONata expression meets expectations;

[0107] • Check if the generated SQL statement is formatted correctly;

[0108] • Confirm that the field mapping and time parsing are accurate;

[0109] • Identify and correct configuration errors before formal deployment.

[0110] In summary, the core innovations of this invention include:

[0111] JSONata (a lightweight query and transformation language for JSON) expression-driven declarative data transformation: Introducing the JSONata functional query and transformation language as the data transformation engine, users can declaratively define the transformation logic from arbitrary JSON structures to flat record arrays simply by writing JSONata expressions. No code needs to be written, thus adapting to various heterogeneous data formats. JSONata expressions are pre-compiled at system startup and executed directly at runtime, balancing flexibility and performance.

[0112] Multi-endpoint routing based on configuration rules: Multiple access rules are defined through TOML configuration files. Each rule registers an HTTP endpoint independently. Different data sources can submit data through their own dedicated endpoints. The rules are completely isolated from each other and support different databases, super tables, transformation logic, and field mapping configurations.

[0113] Mutually exclusive configuration of dynamic key mapping and static values: Supports two methods for metadata such as database name, super table name, and sub-table name: static configuration values ​​and dynamic extraction from JSON records. The two methods are mutually exclusive, taking into account the needs of both fixed and flexible scenarios.

[0114] Optional field awareness: Each field in the field list can be marked as optional. When a record lacks optional fields, the system automatically adjusts the column name list and value list for that record, allowing different records in the same batch to be written to different column sets.

[0115] Four-level sorting and length-aware automatic sharding: After sorting records in four levels (super table, sub-table, column set, and timestamp) to generate SQL, records with the same structure are adjacent to reuse the SQL header, and automatic sharding is performed when the statement is too long to ensure that each SQL statement does not exceed the length limit of the database engine.

[0116] Dry Run debug mode: Provides a trial run mode that returns the converted JSON and generated SQL but does not execute them, allowing users to verify the correctness of the conversion expressions and field mappings before production deployment.

[0117] This invention solves the problems of format adaptation difficulties, hard-coded conversion logic, and lack of declarative configuration capabilities when integrating heterogeneous JSON data into time-series databases. It has the advantages of high flexibility, zero-code integration, high-throughput batch writing, and security and debuggability.

[0118] Although the present invention has been described in detail above, it is not limited thereto, and those skilled in the art can make various modifications based on the principles of the present invention. Therefore, all modifications made in accordance with the principles of the present invention should be understood to fall within the protection scope of the present invention. < / val1> < / timestamp> < / field1> < / ts> < / tbname> < / stb> < / db> < / columns> < / tbname> < / stb> < / val1> < / timestamp> < / field1> < / ts> < / tbname> < / stb> < / db>

Claims

1. A method for writing heterogeneous JSON data to a time-series database based on an expression engine, characterized in that... include: Step S1 of the HTTP endpoint dynamic registration based on configuration rules is to dynamically register the corresponding POST request processing path on the HTTP router according to the HTTP endpoint identifier configuration item in the configuration file, so that different data sources can submit JSON data through their own independent HTTP endpoints. Step S2, based on the JSONata expression engine, involves receiving an HTTP request containing JSON data from the data source and, according to the conversion expression configuration items in the configuration file, calling the JSONata expression engine to perform evaluation operations on the original JSON data, converting the heterogeneous JSON structure with arbitrary nesting levels into a flat record array. Step S3 of dynamic field mapping and record parsing involves parsing each record in the converted record array and dynamically extracting the database name, super table name, sub-table name, and timestamp value from each record according to the key mapping configuration items in the configuration file. Step S4 of optional field awareness involves extracting the corresponding data fields from each record based on the field list configuration items in the configuration file. The data fields support two modes: required fields and optional fields. When an optional field is missing in a record, a column name statement fragment containing only the existing fields is automatically generated, enabling different records in the same batch to be written to different column sets. Step S5, which involves sorting, merging, and batch writing of data in shards, sorts all parsed records by super table name, sub-table name, column name set, and timestamp in four levels to generate SQL statements, arranging records with the same table structure adjacent to each other. Then, INSERT statements are generated in sorting order. When the statement length approaches the preset maximum SQL length, sharding is performed, splitting the data in a single HTTP request into multiple SQL statements for sequential execution. Each statement uses the super table automatic table creation syntax to achieve an integrated operation of automatic table creation and data writing.

2. The time-series database writing method according to claim 1, characterized in that, It also includes step S6, which is a dual-mode execution of trial run mode and actual write mode. In trial run mode, the converted JSON data and the generated SQL statement are returned but no actual write operation is performed. This is used to debug and verify the correctness of the conversion expression. In normal mode, database connections are obtained through a connection pool, all SQL statements are executed sequentially, the number of affected rows is summarized, and the write results are returned.

3. The time-series database writing method according to claim 1, characterized in that, In step S2, when the JSONata expression engine evaluates the original JSON, if the result is a single-element array, it automatically extracts the values ​​from the array. Users need to explicitly generate the array using the [] operator when writing conversion expressions to ensure the correct generation of batch records.

4. The time-series database writing method according to claim 1, characterized in that, In step S3, the mapping of database name, super table name, and sub-table name adopts a mutual exclusion configuration method of extracting static values ​​and dynamic keys. Only one of each pair of parameters can be set, and the system forcibly checks this mutual exclusion constraint during the configuration verification phase.

5. The time-series database writing method according to claim 1, characterized in that, In step S3, timestamp parsing supports 12 predefined formats, including unix, unix_ms, unix_us, unix_ns, ansic, rubydate, rfc822z, rfc1123z, rfc3339, rfc3339nano, stamp, stampmilli, datetime, and any strftime custom format string. It also supports specifying the time zone used for time parsing through time zone configuration (Timezone).

6. The time-series database writing method according to claim 1, characterized in that, In step S4, when the adaptive column name generation mechanism detects that the first optional field is missing, it switches to the field-by-field column name accumulation mode. When a field that exists is encountered later, it is appended to the column name statement, so that different records in the same batch can be written to different column sets, without all records needing to have the exact same field structure.

7. The time-series database writing method according to claim 1, characterized in that, In step S5, the SQL statement uses the TDengine Super Table automatic table creation syntax, with the format INSERT INTO. <db> . <stb> ( <tbname> , <ts> , <field1> , <field2>,...) VALUES ('<sub_table>',' <timestamp> ', <val1> , <val2> (,...), reuse the supertable declaration part between consecutive records with the same column set in the same supertable, only add the VALUES clause, and reduce the size of the SQL statement. < / val1> < / timestamp> < / field1> < / ts> < / tbname> < / stb> < / db> 8. A time-series database writing system for heterogeneous JSON data based on an expression engine, characterized in that, include: The rule configuration module is used to parse and validate TOML format configuration files. The configuration file contains global switches and a list of rules. Each rule contains configuration items such as endpoint identifier, database mapping, super table mapping, sub-table mapping, time configuration, transformation expression, and field list. The expression compilation module is used to precompile the JSONata transformation expression in each rule into an executable expression object during the system startup phase, and at the same time precompile the strftime format time template into a Go language time layout string, and preload the time zone string into a time zone object; The HTTP access module is used to implement RESTful interfaces based on the Gin Web framework. It registers a POST processor with authentication middleware for each configured HTTP endpoint and assigns or parses a request identifier for distributed tracing for each request. The data conversion module receives raw JSON data from HTTP requests and performs structural conversion using pre-compiled JSONata expressions, converting JSON of any format into a flat record array format acceptable to time-series databases. The record parsing module is used to parse the converted JSON byte stream into Go language map slices using a JSON decoder that supports precise numerical parsing. Then, according to the mapping configuration in the rules, it extracts the database name, super table name, sub-table name, timestamp and each data field one by one, and escapes the backticks in the identifiers to prevent SQL injection. The SQL generation module is responsible for sorting the parsed records and generating batch INSERT SQL statements. It adopts a four-level sorting strategy of super table → sub-table → column set → timestamp to make data with the same structure adjacent. It generates INSERT statements using the TDengine super table automatic table creation syntax and automatically shards INSERT statements when they are too long. It also escapes special characters in string values, including newline characters, carriage return characters, tab characters, single quotes, double quotes, backslashes, and null characters. The monitoring metrics module is used to maintain an independent metric collector for each endpoint, and to count the total number of rows, the number of successful rows, the number of failed rows, the number of rows in transit, and the number of affected rows in real time.

9. The system according to claim 8, characterized in that, The rule configuration module performs validity checks on endpoint identifiers, allowing only character combinations containing English letters, numbers, underscores, and hyphens; it also performs security checks on identifiers for database names and super table names, prohibiting the inclusion of backticks to prevent SQL injection attacks.

10. The system according to claim 8, characterized in that, The SQL generation module returns an error when the SQL value of a single record exceeds the maximum SQL length limit. When multiple records exceed the limit, it automatically performs sharding, with each shard independently containing a complete INSERT statement header and a super table declaration.

11. The system according to claim 8, characterized in that, The record parsing module uses json.Decoder in conjunction with the UseNumber() option to decode JSON, keeping the numeric type as a json.Number string representation, thus avoiding precision loss of large integers during JSON decoding.

Images