Database intelligent field expansion method and device based on JSON field type

Abstract

This application belongs to the field of computer database technology, specifically to a method and device for intelligent database field expansion based on the JSON field type. JSON provides a flexible, semi-structured storage method. Fields from different sources, especially those unique to only some data sources, can be selectively stored in JSON fields. Only when they are frequently used or business-critical are they solidified as formal data table fields.

Description

Technical Field

[0001] The present application belongs to the field of computer database technology, and specifically relates to a database intelligent field expansion method and device based on JSON field type. Background Art

[0002] During the data fusion process, the structure of source data often changes with business needs or system updates. In particular, when the target database receives data from multiple data sources, it often encounters the problem of newly added fields.

[0003] Traditional solutions require frequent modifications to the target database's table structure and manual field expansion. This not only increases operational costs, but each table structure modification also locks the database table, impacting the system's concurrent query and update performance. Furthermore, newly added fields may only appear in a small amount of data, and these fields may only be useful for data from a specific source and be irrelevant for data from other sources. Directly solidifying data can result in a large number of infrequently used, redundant fields in the database table, wasting storage space and creating additional complexity in queries. In heterogeneous data sources, the fields of each source may not be completely consistent. If all fields of each source data are directly solidified into the table structure, the database table structure will become very complex and redundant. Summary of the Invention

[0004] To address this issue, this paper proposes a method based on JSON semi-structured storage and intelligent field expansion. By combining temporary storage with dynamic parsing, it intelligently manages the addition of new fields, ensuring the efficiency and flexibility of data fusion. The technical solution is as follows:

[0005] A database intelligent field expansion method based on JSON field type, comprising the following steps:

[0006] S1. Before aggregating data from multiple sources, examine the data from each data source to separate the common and non-common fields of each data source;

[0007] S2. Add a new JSON extension field to the aggregation target database. After aggregation, the JSON extension field may contain JSON data of various types and meanings.

[0008] S3. Use the JSON parsing function in SQL to query whether the JSON extension field contains a certain field name and its value.

[0009] S4. When a field name within a JSON extension field, that is, a key name stored within the JSON extension field, reaches a certain percentage in the field, the field stored with that key name is no longer considered redundant.

[0010] S5. By passing the JSON field name to the machine learning model, the model will traverse all key names in the JSON field and calculate the proportion of each key name in the field, query frequency feature data, and based on this data, return a label using a binary classification model to indicate whether the key name needs to be extracted to the database as a new field, thereby dynamically creating database fields.

[0011] Preferably, in step S2, the field of the source table is represented as a key name of a JSON key-value pair in the JSON extension field, and the data value of the field of the source table is represented as a value of the JSON key-value pair in the JSON extension field.

[0012] Preferably, in step S5, the newly added fields are temporarily stored in JSON and the database fields are dynamically created, as follows:

[0013] Data type deduction, define field type deduction function: call function get_field_type to deduce the corresponding database field type based on the data type of the given value;

[0014] Data extraction and field addition, this part mainly includes the following steps:

[0015] Step 1. Connect to the database: Use the `psycopg2.connect` method to establish a connection to the PostgreSQL database;

[0016] Step 2: Read JSON field data: Select the JSON fields of all records in the specified table by constructing an SQL query statement, and use the cursor object to execute the query to obtain all records that meet the conditions;

[0017] Step 3: Parse and extract key values: Traverse the query results, try to parse the JSON data in each record into a Python object, and check whether the target key name exists; if so, collect the value corresponding to the key name for subsequent processing;

[0018] Step 4: Field type derivation and verification: Determine the appropriate database field type based on the first key value extracted, and verify whether the target field already exists in the table by querying the database metadata to avoid duplicate addition.

[0019] Step 5. Dynamically add fields: If the target field does not exist, construct and execute an SQL `ALTER TABLE` statement to dynamically add a new field to the original table and set the correct data type;

[0020] Step 6. Update data: Construct and execute the SQL `UPDATE` statement to fill the previously extracted key values ​​into the newly added fields to ensure data consistency and integrity.

[0021] Preferably, step S5 of machine learning training includes the following steps:

[0022] Data loading and preprocessing: Import external datasets through the read_csv function of the Pandas library;

[0023] Data segmentation: Data segmentation clearly defines the input (X) and expected output (y) during model training. This allows the machine learning algorithm to learn how to best predict the target variable based on the given features, ensuring the effectiveness and accuracy of subsequent steps such as model training, validation, and testing. The order of elements in the X and y arrays is consistent, ensuring that each feature data item is matched to the correct target data item.

[0024] Training set and test set division: Apply the train_test_split function in the scikit-learn library to split the overall data, instantiate a logistic regression classifier, and train it using the training set data by calling the fit method.

[0025] Preferably, the instruction functions included are as follows:

[0026] Function to get all different key names in JSON fields: get_jsonb_keys;

[0027] Function to deduce database field type based on field value type: get_field_type;

[0028] A function to detect the query frequency of a certain json_key in the log: calculate_query_frequency;

[0029] Function to count the number of occurrences and percentage of each key in a JSON field: get_jsonb_key_stats;

[0030] The machine learning binary classification model returns the classification results based on the key name, number of occurrences, and query frequency input by the system.

[0031] The function extract_and_insert_field determines whether to automatically add fields based on the model return results and writes JSON data to the newly added fields.

[0032] Preferably, the characteristic value of each key name in the JSON field is normalized, which mainly includes the following steps:

[0033] The feature values ​​in this article refer to the database field ratio and query frequency of each key name in the JSON field when constructing the dataset. These two data are used as feature data for machine learning to identify and determine whether the JSON key name needs to be extracted and solidified for machine learning model training.

[0034] Step 1: Call the get_jsonb_keys function to get all the key names in the JSON field in the current database;

[0035] Step 2: Calculate the ratio of the key name in the JSON field to the database field;

[0036] Step 3: Calculate the query frequency of each key name:

[0037] Open and read the query log file contents in the specified path. Use regular expressions to match query statements in the log file, especially those containing operators pointing to specific JSON keys (such as `->` or `->>`);

[0038] Step 4: Integrate information and output:

[0039] By combining the key name's proportion and frequency of appearance in the query statement, we construct feature data for each key name. This step provides a comprehensive view of the target key name's usage in the database and related query activities.

[0040] Preferably, the key name proportion is calculated by calling the get_jsonb_key_stats function, which is used to analyze the frequency of occurrence of each key in a specified JSON field in a PostgreSQL table. It constructs an SQL query statement based on the provided database connection, table name, and JSONB column name, calls the jsonb_object_keys function to obtain all the key names in the column, and then counts these keys, returning a Counter object containing each key and its occurrence count, as well as the total number of all keys.

[0041] Preferably, the query frequency of each key name is calculated: all query statements that may involve the key name that the user is interested in are found, and frequency statistics are performed on all the query statements that meet the conditions to understand which key names are most frequently used for queries.

[0042] An electronic device, characterized in that the electronic device comprises: at least one processor; and

[0043] a memory communicatively connected to the at least one processor; wherein,

[0044] The memory stores a computer program that can be executed by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform a database intelligent field expansion method based on a JSON field type.

[0045] Compared with the prior art, this application has the following beneficial effects:

[0046] 1. Temporarily store new fields in JSON to avoid rashly modifying the table structure when you are unsure whether the field will be used long-term. For fields that are used less frequently or are only stored for a short period of time, they can remain in JSON without taking up space in the table structure.

[0047] JSON provides flexible semi-structured storage. Fields from diverse sources, especially those unique to certain data sources, can be selectively stored in JSON fields. Only when they are frequently used or business-critical will they be consolidated into formal data table fields. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] Figure 1 This is the flow chart of this application. DETAILED DESCRIPTION

[0049] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0050] A database intelligent field expansion method based on JSON field type, comprising the following steps:

[0051] S1. Before aggregating data from multiple sources, check the data from each data source and separate the common and non-common fields of each data source.

[0052] When aggregating data from multiple sources, we can't guarantee that the data fields across all sources will remain completely consistent. Each data source will likely have some unique fields. When this data is aggregated, non-shared fields will occupy a significant portion of the aggregated data table, causing field redundancy. Furthermore, these fields may not be highly important or prioritized. Therefore, before aggregating data from multiple sources, manually inspect each data source to separate the shared and non-shared fields.

[0053] S2. Add a column of JSON-type extension fields to the aggregation target database: Add a column of JSON-type extension fields to the aggregation target database. After aggregation is complete, the JSON extension field may contain JSON data of various types and meanings.

[0054] The source table's fields are represented as key names of JSON key-value pairs in the JSON extension field, and the data values ​​of the source table's fields are represented as values ​​of the JSON key-value pairs in the JSON extension field.

[0055] As shown in the following example: Table 1 and Table 2 are user information source tables obtained from two platforms respectively, and Table 3 is the target table that aggregates all the data in Table 1 and Table 2. The data samples of Table 1 and Table 2 are as follows:

[0056] Table 1 is the user information source table obtained in the first platform

[0057] id name age gender 1 Zhang San 18 male 2 Li Si 19 female

[0058] Table 2 is the user information source table obtained from the second platform

[0059] id name company status 1 Wang Wu Google del 2 Zhao Liu Microsoft

[0060] Both Table 1 and Table 2 have the id and name fields, but the age and gender fields are unique to Table 1, and the company and status fields are unique to Table 2. After being aggregated into the target table, Table 3 stores Table 1 and Table 2 in the following format: The id field is the unique ID generated by Table 3 based on the newly inserted data, the platform field is the source table (or platform) for the data entry, and platform_id is the original ID of the data entry in the source table (or platform). Example of the aggregated target table: Table 3:

[0061] Table 3: Sample aggregation target table

[0062] id platform platform_id name json_data 1 table1 1 Zhang San {"age":18, "gender": "male"} 2 table1 2 Li Si {"age":19, "gender":"female"} 3 table2 1 Wang Wu {"company":"Google", "status":"del"} 4 table2 2 Zhao Liu {"company":"Microsoft", "status":""}

[0063] After aggregation, the JSON extension field may contain JSON data of various types and meanings. This allows you to aggregate non-shared fields from multiple data source tables into a single field in the target table, reducing database field redundancy caused by a large number of non-shared fields from multiple sources.

[0064] S3. Use the JSON parsing function in SQL to query whether the JSON extended field contains a certain field name and its value.

[0065] As data aggregation continues, the amount and type of data in JSON extension fields will increase. Furthermore, as business progresses, these fields may be used later. To query whether a JSON extension field contains a specific field name and its value, you can use SQL's native JSON parsing functions, such as JSON_EXTRACT in MySQL and jsonb_each in PostgreSQL.

[0066] S4. When a field name in a JSON extension field, that is, a key name stored in the JSON extension field, reaches a certain proportion in the field, the field stored with the key name is considered no longer a redundant field.

[0067] When a field name within a JSON extension field, that is, a key name stored within the JSON extension field, reaches a certain percentage (e.g., 70%) or is frequently queried in the database table, the field stored with that key name is no longer redundant. A new database field with that key name is created using an SQL statement, populated with the key-value pair, and used as a new field in the target data aggregation table, reducing the storage and query pressure on the JSON extension field.

[0068] S5. By passing the JSON field name to the machine learning model, the model will traverse all key names in the JSON field and calculate the proportion of each key name in the field, query frequency feature data, and based on this data, return a label using a binary classification model to indicate whether the key name needs to be extracted to the database as a new field, thereby dynamically creating database fields.

[0069] The mechanism consists of the following modules:

[0070] (1) Get all the different key names in the JSON field: get_jsonb_keys;

[0071] (2) Function to deduce the database field type based on the type of field value: get_field_type;

[0072] (3) A function to detect the query frequency of a certain json_key in the log: calculate_query_frequency;

[0073] (4) Function to count the number of occurrences and percentage of each key in the JSON field: get_jsonb_key_stats;

[0074] (5) Machine learning binary classification model, which returns classification results based on the key name, number of occurrences, and query frequency input by the system;

[0075] (6) A function that determines whether to automatically add fields based on the model return results and writes JSON data into the newly added fields: extract_and_insert_field.

[0076] Training of machine learning models:

[0077] We implement the judgment operation in step 5 above by training a machine learning model.

[0078] We've already described the working principles and process of this mechanism. Based on this solution, a machine learning model is trained to automatically analyze JSON data and predict which key-value pairs should be converted to database fields, thereby improving the efficiency and accuracy of field expansion. The system's input is the JSON field key names and their feature values, and the system output is a binary classification result, determining whether each field should be converted to a database field.

[0079] It mainly includes the following four steps 1 to 4:

[0080] 1. Based on the preset input and output of the machine learning model and the specifics of the target table's JSON fields, construct the relevant dataset, as shown in Table 4. The dataset format example is as follows: frequency, represented by a float floating-point number, indicates the frequency of queries for the key name in target table data queries; proportion, represented by a float floating-point number, indicates the proportion of the key name in the target table's JSON fields. The label field is the dataset label, where a value of 1 indicates that the field is worth converting to a database field, and a value of 0 indicates that it is not worth converting.

[0081] Table 4 Construction of relevant data sets

[0082]

[0083] 2. Model selection and training. This part mainly includes the following steps (1) to (3):

[0084] (1) Model selection: Since the feature data is structured and the problem can be classified as a binary classification problem, a classification model is used.

[0085] (2) Data preprocessing: Categorical variables are converted into numerical form by using one-hot encoding, and numerical features are normalized to improve the performance of the model.

[0086] (3) Model training: Use the linear_model training in sklearn. This part includes the following six steps ① to ⑥:

[0087] ① Data loading and preprocessing: Import external datasets using the read_csv function of the Pandas library. This process ensures that all necessary feature variables and target labels are correctly read and prepared for subsequent analysis.

[0088] ② Data segmentation: Divide the imported dataset into two parts - the feature matrix X (all columns excluding the target label column) and the target vector y (containing only the target label column).

[0089] ③ Training and test set splitting: Use the train_test_split function in the scikit-learn library to split the data. 80% of the data is allocated to the training set for model learning, and the remaining 20% ​​is used as an independent test set for performance evaluation. A random seed is set to ensure repeatability of the experiment.

[0090] ④Model training: Instantiate a logistic regression classifier and train it using the training set data by calling the fit method. In this example, `max_iter=1000` is set to ensure algorithm convergence.

[0091] ⑤Model evaluation: Use the trained model to predict the test set and calculate a series of key performance indicators, including Accuracy, F1-Score, and ROC-AUC.

[0092] ⑥ Feature Importance Analysis: Extract and output the coefficient values ​​corresponding to each feature in the logistic regression model. This information helps identify which features have a significant impact on model decisions. The absolute value of the feature indicates the importance of the corresponding feature, providing a basis for understanding model behavior and optimizing feature selection.

[0093] 3. Model prediction:

[0094] Online prediction service: Deploy the trained model, allowing the system to input key names and feature data to obtain prediction results. If the prediction result is 1, it means that the key-value pair is converted into a database field.

[0095] 4. Continuously optimize the model:

[0096] Regularly collect and build new datasets and retrain the model to improve the precision and accuracy of predictions.

[0097] The examples are as follows:

[0098] The main program logic includes the following steps (1) to (4):

[0099] (1) User interaction and input acquisition: prompt the user to enter the database table name to be processed and the field name containing JSON data.

[0100] (2) Model decision simulation: Assume that a machine learning model has already made a decision on whether to extract the key name in the field and returns True or False (or 1 or 0) as the basis for the decision. In actual applications, this step should be completed by a real running model.

[0101] (3) Conditional execution of field extraction and insertion operations: Determine whether to perform field extraction and insertion operations based on the model's prediction results. If the model predicts that the key-value pair should be extracted, the `extract_and_insert_field` function is called to process it; otherwise, a prompt message is output to inform the user that no action is taken.

[0102] (4) Close the database connection: After completing all operations, make sure to close the connection to the database to release resources and ensure system stability and performance.

[0103] The specific implementation process mainly includes the following steps 1 to 3:

[0104] 1. Obtain system input: The user enters the JSON field name json_data of the data table into the system.

[0105] 2. Collect the features of each key-value pair, mainly including the key name, the proportion of the key name in the field, and the frequency of key name queries within the time range. And perform normalization on the feature values. This part mainly includes the following five steps (1) to (4):

[0106] Call the function get_jsonb_keys to obtain all key names in the JSON field in the current database. The core logic is shown in Table 5:

[0107] Table 5 Core logic of calling function get_jsonb_keys

[0108]

[0109] (1) Calculate the proportion of the key name in the JSON field in the database field:

[0110] The key name percentage is calculated by calling the get_jsonb_key_stats function. This function analyzes the frequency of occurrence of each key within a specified JSON field in a PostgreSQL table. It constructs a SQL query based on the provided database connection, table name, and JSONB column name, and calls the jsonb_object_keys function to retrieve all key names in that column, as shown in Table 6. It then counts these keys and returns a Counter object containing each key, its occurrence count, and the total number of keys.

[0111] Table 6 Calling the jsonb_object_keys function to obtain all existing key names in the column

[0112] def get_jsonb_key_stats(conn, table_name, json_data): cursor = conn.cursor() # Query JSONB keys for all rows query = f""" SELECT jsonb_object_keys({json_data}) AS key_name FROM {table_name} WHERE {json_data} IS NOT NULL; """ cursor.execute(query) rows = cursor.fetchall() cursor.close() # Count the number of times each key appears key_counter = Counter(row[0] for row in rows) total = sum(key_counter.values()) return key_counter, total

[0113] (2) Calculate the query frequency of each key name.

[0114] (3) Open and read the query log file contents at the specified path. Use regular expressions to match the query statements in the log file, especially those that contain operators pointing to specific JSON keys (such as `->` or `->>`). This step aims to find all query statements that may involve the key names that the user is interested in. Perform frequency statistics on all qualified query statements found in the previous step to understand which key names are most frequently used in queries. Integrate the information and output it, combining the results of the above two parts (i.e., the proportion of key names and the frequency of occurrence in query statements).

[0115] (4) Constructing feature data for each key name. This step provides a comprehensive view of the usage of the target key name in the database and related query activities, as shown in Table 7.

[0116] Table 7 Constructing the characteristic data of each key name

[0117] key_name frequency proportion key1 0.27 0.48 key2 0.75 0.84 ... ... ...

[0118] 3. When the model predicts that a key-value pair should be converted into a field, the extract_and_insert_field function is called to dynamically create a database field using SQL statements. This part mainly includes the following two steps (1) to (2):

[0119] (1) Model prediction and online service deployment:

[0120] Online prediction service deployment: Deploy the trained logistic regression model as an online prediction service. This service receives JSON-formatted data from the database system and returns prediction results. If the model predicts a value of 1, the corresponding key-value pair is converted into an independent database field.

[0121] (2) The process of dynamically creating database fields includes the following steps:

[0122] First, data type derivation:

[0123] Define a field type inference function: Call the get_field_type function to infer the corresponding database field type based on the data type of a given value. This function supports the recognition of common data types such as integers, floating-point numbers, Booleans, strings, dictionaries (stored in JSON format), and lists (stored in array format), and defaults to using the VARCHAR type for other unknown types.

[0124] By traversing each key element in the JSON field, you can get the type of the field value corresponding to each key name.

[0125] Second, data extraction and field addition, this part mainly includes the following steps 1) to 6):

[0126] 1) Connect to the database: Use the `psycopg2.connect` method to establish a connection to the PostgreSQL database to ensure that subsequent operations can be performed smoothly.

[0127] 2) Read JSON field data: Select the JSON fields of all records in the specified table by constructing an SQL query statement, and use the cursor object to execute the query to obtain all records that meet the conditions.

[0128] 3) Parsing and extracting key values: Iterate through the query results, attempt to parse the JSON data in each record into a Python object, and check whether the target key name exists. If so, collect the value corresponding to the key name for subsequent processing.

[0129] 4) Field type inference and verification: Determine the appropriate database field type based on the first key value extracted, and verify whether the target field already exists in the table by querying the database metadata to avoid duplicate addition.

[0130] 5) Dynamically add fields: If the target field does not exist, construct and execute an SQL `ALTER TABLE` statement to dynamically add a new field to the original table and set the correct data type.

[0131] 6) Update data: Construct and execute the SQL `UPDATE` statement to fill the previously extracted key values ​​into the newly added fields to ensure data consistency and integrity.

[0132] Third, the implementation plan for the key steps given above needs to be modified and optimized according to the specific situation of the database fields to achieve better implementation results.

[0133] Through the above technical solution, this mechanism can achieve the following effects:

[0134] 1) Optimized performance for adding new fields: Temporarily storing new fields in JSON avoids frequent modifications to the database table structure, improving system stability and flexibility. When adding fields to the original database, traditional methods require at least one schema change to the target table. However, using JSON storage reduces the number of schema changes to zero.

[0135] 2) By eliminating the need to modify the database schema for every new or changed data field, this mechanism significantly reduces the workload and complexity of database maintenance, lowering long-term operating costs. Traditional methods require manual creation of new fields in the target database table and matching of source and target table fields when adding fields to the original database. This mechanism eliminates this step, leaving it entirely to the model.

[0136] 3) Intelligent field expansion: The system intelligently expands the table structure based on the frequency of field usage and business needs, optimizing data query and management efficiency.

[0137] 4) This approach is not only suitable for current application scenarios but also offers excellent adaptability and scalability to future changes in data types, structures, or business logic. Combining the strengths of traditional relational and NoSQL storage, this approach uses JSON to store unstable, rapidly changing data schemas, while converting stable and frequently queried fields into independent fields. This hybrid model strikes a balance between flexibility and performance, enabling the system to meet future challenges without major modifications.

[0138] 5) By uniformly storing non-common fields in JSON fields, the need for customized processing of different data sources is reduced, thereby simplifying the process of data aggregation and integration.

[0139] In addition, it should be noted that: the embodiment of the present application also provides a computer-readable storage medium, the computer-readable storage medium stores a computer program, the computer program is suitable for being loaded and executed by the processor Figure 1 For details on the methods provided in each step, please refer to the Figure 1 The implementation methods provided by each step will not be described in detail here. In addition, the description of the beneficial effects of adopting the same method will not be described in detail. For technical details not disclosed in the computer-readable storage medium embodiment involved in this application, please refer to the description of the method embodiment of this application. As an example, the computer program can be deployed to execute on one computer device, or on multiple computer devices located in one place, or on multiple computer devices distributed in multiple places and interconnected by a communication network.

[0140] The computer-readable storage medium may be the device provided in any of the aforementioned embodiments or the internal storage unit of the computer device, such as the hard disk or memory of the computer device. The computer-readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the computer device. Furthermore, the computer-readable storage medium may also include both the internal storage unit of the computer device and an external storage device. The computer-readable storage medium is used to store the computer program and other programs and data required by the computer device. The computer-readable storage medium may also be used to temporarily store data that has been output or is to be output.

[0141] The above disclosure is only a preferred embodiment of the present application, and certainly cannot be used to limit the scope of rights of the present application. Therefore, equivalent changes made according to the claims of the present application are still within the scope covered by the present application.

Claims

1. A database intelligent field expansion method based on JSON field type, characterized in that: The following steps are involved: S1. Before aggregating data from multiple sources, examine the data from each data source to separate the common and non-common fields of each data source; S2. Add a new JSON extension field to the aggregation target database. After aggregation is complete, the JSON extension field will contain JSON data of various types and meanings. S3. Use the JSON parsing function in SQL to query whether the JSON extension field contains a certain field name and its value. S4. When a field name within a JSON extension field, that is, a key name stored within the JSON extension field, reaches a certain percentage in the field, the field stored with that key name is no longer considered redundant. S5. By passing the JSON field name to the machine learning model, the model will iterate over all key names in the JSON field, calculate the proportion of each key name in the field, query frequency feature data, and based on this data, use a binary classification model to return a label indicating whether the key name should be extracted into the database as a new field, thereby dynamically creating database fields. Use JSON to temporarily store new fields and dynamically create database fields, as follows: Data type deduction, define field type deduction function: call function get_field_type to deduce the corresponding database field type based on the data type of the given value; Data extraction and field addition, this part mainly includes the following steps: Step 1. Connect to the database: Use the `psycopg2.connect` method to establish a connection to the PostgreSQL database; Step 2: Read JSON field data: Select the JSON fields of all records in the specified table by constructing an SQL query statement, and use the cursor object to execute the query to obtain all records that meet the conditions; Step 3: Parse and extract key values: Traverse the query results, try to parse the JSON data in each record into a Python object, and check whether the target key name exists; if so, collect the value corresponding to the key name for subsequent processing; Step 4: Field type derivation and verification: Determine the appropriate database field type based on the first key value extracted, and verify whether the target field already exists in the table by querying the database metadata to avoid duplicate additions. Step 5. Dynamically add fields: If the target field does not exist, construct and execute an SQL `ALTER TABLE` statement to dynamically add a new field to the original table and set the correct data type; Step 6. Update data: Construct and execute the SQL `UPDATE` statement to fill the previously extracted key values ​​into the newly added fields to ensure data consistency and integrity.

2. The database intelligent field expansion method based on JSON field type according to claim 1, characterized in that: In step S2, the fields of the source table are represented as key names of JSON key-value pairs in the JSON extension field, and the data values ​​of the fields of the source table are represented as values ​​of the JSON key-value pairs in the JSON extension field.

3. The database intelligent field expansion method based on JSON field type according to claim 1, characterized in that: Step S5: Training the machine learning model, including the following steps: Data loading and preprocessing: Import external datasets through the read_csv function of the Pandas library; Data segmentation: Data segmentation clearly defines the input (X) and expected output (Y) during model training, allowing the machine learning algorithm to learn how to best predict the target variable based on the given features. This ensures the effectiveness and accuracy of subsequent steps such as model training, validation, and testing. The order of elements in the X and y arrays is consistent, ensuring that each feature data item is matched to the correct target data item. Training set and test set division: Apply the train_test_split function in the scikit-learn library to split the overall data, instantiate a logistic regression classifier, and train it using the training set data by calling the fit method.

4. The database intelligent field expansion method based on JSON field type according to claim 1, characterized in that: The included instruction functions are as follows: Function to get all different key names in JSON fields: get_jsonb_keys; Function to deduce database field type based on field value type: get_field_type; Function to detect the query frequency of a certain json_key in the log: calculate_query_frequency; Function to count the number of occurrences and percentage of each key in a JSON field: get_jsonb_key_stats; The machine learning binary classification model returns the classification results based on the key name, number of occurrences, and query frequency input by the system. The function extract_and_insert_field determines whether to automatically add fields based on the model return results and writes JSON data to the newly added fields.

5. The database intelligent field expansion method based on JSON field type according to claim 1, characterized in that: The characteristic values ​​of each key name in the JSON field refer to the database field ratio and query frequency of each key name in the JSON field when building the dataset. These two data are used as feature data for machine learning to identify and determine whether the JSON key name needs to be extracted and solidified for machine learning model training. The specific steps are as follows: Step 1: Call the get_jsonb_keys function to get all the key names in the JSON field in the current database; Step 2: Calculate the ratio of the key name in the JSON field to the database field; Step 3: Calculate the query frequency of each key name: Open and read the query log file contents at the specified path, and use regular expressions to match the query statements in the log file, especially those that contain operators pointing to specific JSON keys; Step 4: Integrate information and output: By combining the key name's proportion and frequency of appearance in query statements, we construct feature data for each key name, providing a comprehensive view of the target key name's usage in the database and related query activities.

6. The database intelligent field expansion method based on JSON field type according to claim 5, characterized in that: The key name percentage is calculated by calling the get_jsonb_key_stats function. This function is used to analyze the frequency of occurrence of each key in a specified JSON field in a PostgreSQL table. It constructs an SQL query statement based on the provided database connection, table name, and JSONB column name, calls the jsonb_object_keys function to obtain all the key names in the column, and then counts these keys, returning a Counter object containing each key, its occurrence count, and the total number of keys.

7. The database intelligent field expansion method based on JSON field type according to claim 5, characterized in that: Calculate the query frequency of each key name: Find all query statements involving the key names of interest to the user, and perform frequency statistics on all qualified query statements to understand which key names are most frequently used in queries.

8. An electronic device, characterized in that: The electronic device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the database intelligent field expansion method based on the JSON field type as described in any one of claims 1 to 7.

Images