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

Abstract

The invention belongs to the technical field of computer databases, and particularly relates to a database intelligent field extension method and device based on a JSON field type, and JSON provides a flexible semi-structured storage mode. For fields of different sources, especially those fields only having partial data sources unique, the fields can be selectively stored in JSON fields. Only under the condition of frequent use or business key, the data table field can be solidified into a formal data table field.

Description

Technical Field

[0001] This application belongs to the technical field of computer databases, and particularly relates to a database intelligent field extension method and device based on JSON field types. Background Art

[0002] During the data fusion process, the structure of the source data often changes with business requirements or system updates. Especially when the target database receives data from multiple data sources, the problem of new fields often occurs.

[0003] Traditional solutions require frequent modification of the table structure of the target database and manual field extension. This not only increases the operation and maintenance costs, but also locks the database table every time the table structure is modified, which also affects the performance of concurrent queries and updates of the system. Moreover, the newly added fields may only appear in a small amount of data, and these fields may only be useful for data from specific sources and are irrelevant to data from other sources. If directly solidified, it may lead to a large number of unused redundant fields in the database table, wasting storage space and causing 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 table structure of the database will become very complex and redundant. Summary of the Invention

[0004] To address this problem, the present invention proposes a method based on JSON semi-structured storage and intelligent field extension. Through a method combining temporary storage and dynamic parsing, it intelligently manages the addition of fields to ensure the efficiency and flexibility of data fusion. Its technical solution is as follows:

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

[0006] S1. Before converging multi-party data, check the data of each data source and separate the common fields and non-common fields of each data source;

[0007] S2. Add a new column of JSON type extension field in the converged target database; after convergence, the JSON extension field may contain JSON data of various types and meanings;

[0008] S3. Use the built-in JSON parsing function in SQL to query whether a certain field name and its value are contained in the JSON extension field;

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

[0010] S5. By passing the JSON field names to the machine learning model, the model traverses all the key names in the JSON field, calculates the proportion of each key name in the field and the query frequency feature data, and based on this data, returns a label indicating whether the key name needs to be extracted into the database as a new field through a binary classification model, thus realizing the dynamic creation of database fields.

[0011] Preferably, the fields of the source table in step S2 are represented as the key names of the JSON key-value pairs in the JSON extended field, and the data values of the fields of the source table are represented as the values of the JSON key-value pairs in the JSON extended field.

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

[0013] Data type derivation, define a field type derivation function: call the function get_field_type to derive the corresponding database field type according to 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: Construct an SQL query statement to select the JSON fields of all records in the specified table, and use the cursor object to execute the query to obtain all eligible records;

[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 if the target key name exists; if it exists, 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 at the same time set the correct data type;

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

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

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

[0023] Data splitting: Through data splitting, the input (X) and expected output (y) during model training can be clearly defined, enabling the machine learning algorithm to learn how to best predict the target variable based on the given features, and 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 the same, which can ensure that each feature data matches the correct target data;

[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 included instruction functions are as follows:

[0026] Function to obtain all different key names in the JSON field: get_jsonb_keys;

[0027] Function to deduce the database field type according to the type of field value: get_field_type;

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

[0029] Function to count the occurrence times and percentages of each key in the JSON field: get_jsonb_key_stats;

[0030] Machine learning binary classification model, which returns the classification result according to the key name, occurrence times, and query frequency input by the system,

[0031] Function to determine whether to automatically add a field according to the model return result and write the JSON data into the new field: extract_and_insert_field.

[0032] Preferably, perform normalization processing on the feature values of each key name in the JSON field. This part mainly includes the following steps:

[0033] The eigenvalue in the text refers to the proportion of the database fields corresponding to each key name in the JSON fields and the query frequency when constructing the data set. These two pieces of data are used as the feature data for machine learning to identify and determine whether the JSON key name needs to be extracted and solidified for training the machine learning model;

[0034] Step 1: Call the function get_jsonb_keys to obtain all the key names in the JSON fields within the current database;

[0035] Step 2: Calculate the proportion of the key names in the JSON fields in the database fields;

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

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

[0038] Step 4: Integrate the information and output:

[0039] Combine the proportion of the key names and the frequency of their occurrences in the query statements to construct the feature data for each key name. This step provides a comprehensive view of the usage of the target key names in the database and related query activities.

[0040] Preferably, the calculation of the proportion of key names is achieved by calling the get_jsonb_key_stats function. This function is used to analyze the occurrence frequency of each key in the specified JSON field of 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 existing key names in this column, and then counts these keys, returning a Counter object containing each key and its occurrence times, as well as the total number of all keys.

[0041] Preferably, to calculate the query frequency of each key name: find all the query statements that may involve the key names of interest to the user, and perform frequency statistics on all the found qualified query statements to understand which key names are most frequently used in queries.

[0042] An electronic device, characterized in that the electronic device includes: 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, and 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, the present invention has the following beneficial effects:

[0046] 1. Use JSON to temporarily store new fields to avoid rashly modifying the table structure when you are not sure whether the field will be used for a long time. For fields that are used less frequently or are only stored for a short period of time, they can always exist in JSON without occupying table structure space.

[0047] 2.JSON provides a flexible semi-structured storage method. Fields from different sources, especially those that are unique to only some data sources, can be selectively stored in JSON fields. Only when they are frequently used or business-critical will they be solidified 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 the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work 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 to separate the common fields and non-common fields of each data source.

[0052] When aggregating data from multiple sources, we cannot guarantee that the data fields of each data source can remain completely consistent. Each data source will have more or less fields that are unique to that data source. When we aggregate this data together, non-common fields will occupy a large number of fields in the aggregated data table, causing field redundancy, and the importance or usage priority of these fields may not be high. Therefore, before aggregating data from multiple sources, manually check the data of each data source to separate the common fields and non-common fields of each data source.

[0053] S2. Add a new extended field of JSON type in the aggregation target database: Add a new extended field of JSON type in the aggregation target database; after the aggregation is completed, the JSON extended field may contain JSON data of various types and meanings.

[0054] Among them, the fields of the source table are shown as the key names of the JSON key-value pairs in the JSON extended field, and the data values of the fields in the source table are shown as the values of the JSON key-value pairs in the JSON extended field.

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

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

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

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

[0059] Among them, both Table 1 and Table 2 have id and name fields, but the age and gender fields are unique fields in Table 1, and the company and status fields are unique fields in Table 2. After aggregating to the target table, the storage format samples of Table 1 and Table 2 in Table 3 are as follows. Among them, the id field is the unique id newly generated by Table 3 according to the newly inserted data, the platform field is the source table (or source platform) of this piece of data, and the platform_id is the original id of this piece of data in the source table (or source platform). Aggregation target table sample Table 3:

[0060] Table 3 Aggregation target table sample

[0061] 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”:"”}

[0062] After the aggregation is completed, the JSON extended field may contain JSON data of various types and meanings. In this way, the non-common fields of multiple data source tables are aggregated into the same field of the target table, reducing the phenomenon of database field redundancy caused by a large number of multi-source non-common fields.

[0063] S3. Use the built-in JSON parsing function in SQL to query whether a certain field name and its field value are contained in the JSON extended field.

[0064] As data aggregation progresses, the amount and types of data within the JSON extended fields will increase. Also, as the business advances, these fields may be used later. At this time, to query whether a certain field name and its value exist within the JSON extended fields, the built-in JSON parsing functions in SQL can be used, such as JSON_EXTRACT in MySQL and jsonb_each in PostgreSQL.

[0065] S4. When a certain field name within the JSON extended fields, that is, the key name stored within the JSON extended fields, reaches a certain proportion within the field, it is considered that the field stored under this key name no longer belongs to redundant fields.

[0066] When a certain field name within the JSON extended fields, that is, the key name stored within the JSON extended fields, reaches a certain proportion (such as 70%) within the field or has a high query frequency in the database table, it can be considered that the field stored under this key name no longer belongs to redundant fields. A new database field is created with this key name through an SQL statement, and the value of this key-value pair is used to populate this field, serving as a new field in the data aggregation target table, thereby reducing the storage and query pressure of the JSON extended fields.

[0067] S5. By passing the JSON field name into the machine learning model, the model will traverse all the key names in the JSON field and calculate the proportion and query frequency feature data of each key name in the field, and based on this data, return a label indicating whether the key name needs to be extracted to the database as a new field through a binary classification model, achieving the dynamic creation of database fields.

[0068] This mechanism includes the following several modules:

[0069] (1) Function to obtain all different key names in the JSON field: get_jsonb_keys;

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

[0071] (3) Function to detect the query frequency of a certain json_key in the log: calculate_query_frequency;

[0072] (4) Function to count the occurrence times and percentages of each key in the JSON field: get_jsonb_key_stats;

[0073] (5) Machine learning binary classification model, which returns a classification result based on the key name, occurrence times, and query frequency input by the system;

[0074] (6) Function to determine whether to automatically add fields based on the model return result and write JSON data to the newly added fields: extract_and_insert_field.

[0075] Training of the machine learning model:

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

[0077] We have introduced the working principle and process of this mechanism above. According to the above solution, train a machine learning model to automatically analyze JSON data and predict which key-value pairs should be converted into database fields, thereby improving the efficiency and accuracy of field expansion. The input of the system is the key names and their feature values of the JSON fields, and the output of the system is the binary classification result of whether each field should be converted into a database field.

[0078] It mainly includes the following four steps 1-4:

[0079] 1. According to the preset input and output of the machine learning model and the specific situation of the JSON fields in the target table, construct a relevant data set, as shown in Table 4. The format example of the data set is as follows. frequency is represented by a float number, indicating the query frequency of the key name in the target table data query; proportion is represented by a float number, representing the proportion of the key name in the JSON fields of the target table. The lable field is the data set label, where `1` indicates that the field is worth converting into a database field, and `0` indicates that it is not worth converting.

[0080] Table 4 Construction of relevant data sets

[0081]

[0082] 2. Model selection and training, this part mainly includes the following steps (1)-(3):

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

[0084] (2) Data preprocessing: By using one-hot encoding, convert categorical variables into numerical forms, and normalize numerical features at the same time to improve the performance of the model.

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

[0086] ① Data loading and preprocessing: Import the external dataset through the read_csv function of the Pandas library. This process aims to ensure that all necessary feature variables and target labels can be correctly read and ready for subsequent analysis.

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

[0088] ③ Training set and test set splitting: Apply the train_test_split function in the scikit-learn library to split the overall data, where 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. Set the random seed to ensure the reproducibility of the experiment.

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

[0090] ⑤ 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, etc.

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

[0092] 3. Model prediction:

[0093] Online prediction service: Deploy the trained model, allowing the system to pass in the key name and its feature data to obtain the prediction result. If the prediction result is 1, it means that the key-value pair is worth converting into a database field.

[0094] 4. Continuously optimize the model:

[0095] Regularly collect and build a new dataset, and retrain the model to improve the prediction accuracy and precision.

[0096] The embodiments are as follows:

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

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

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

[0100] (3) Conditional execution of field extraction and insertion operations: Decide whether to perform field extraction and insertion operations based on the model's prediction results. If the model predicts that this key-value pair should be extracted, call the `extract_and_insert_field` function for processing; otherwise, output a prompt message to inform the user that no action will be taken.

[0101] (4) Close the database connection: After completing all operations, ensure that the connection to the database is closed to release resources and ensure system stability and performance.

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

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

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

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

[0106] Table 5 Core logic of calling the function get_jsonb_keys

[0107]

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

[0109] Implement the calculation of the key name proportion by calling the get_jsonb_key_stats function. This function is used to analyze the occurrence frequency of each key in the specified JSON field of the PostgreSQL table. It constructs an SQL query statement based on the provided database connection, table name, and JSONB column name, and calls the jsonb_object_keys function to obtain all the existing key names in this column, as shown in Table 6. Then, it counts these keys and returns a Counter object containing each key and its occurrence times, as well as the total number of all keys.

[0110] Table 6 Obtaining all existing key names in the column by calling the jsonb_object_keys function

[0111] def get_jsonb_key_stats(conn, table_name, json_data): cursor = conn.cursor() # Query all JSONB keys in 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 occurrences of each key key_counter = Counter(row[0] for row in rows) total = sum(key_counter.values()) return key_counter, total

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

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

[0114] (4) Construct the feature data of 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.

[0115] Table 7 Constructing the feature data of each key name

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

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

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

[0119] Online prediction service deployment: Deploy the trained logistic regression model as an online prediction service that can receive JSON-formatted data from the database system and return prediction results. If the model prediction result is 1, it means that the corresponding key-value pair is worth converting into an independent database field.

[0120] (2) The process of dynamically creating a database field, which includes the following steps:

[0121] First, data type derivation:

[0122] Define the field type derivation function: Call the function get_field_type to derive the corresponding database field type according to the data type of the given value. This function supports the recognition of common data types such as integers, floating-point numbers, booleans, strings, dictionaries (stored in JSON format), lists (stored in array form), etc., and by default uses the VARCHAR type to handle other unknown types.

[0123] By traversing each key name element in the JSON field, the type of the field value corresponding to each key name can be obtained.

[0124] Second, data extraction and field addition. This part mainly includes the following steps 1) - 6):

[0125] 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 executed smoothly.

[0126] 2) Read JSON field data: Construct an SQL query statement to select the JSON fields of all records in the specified table, and use the cursor object to execute the query to obtain all eligible records.

[0127] 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 if the target key name exists. If it exists, collect the value corresponding to the key name for subsequent processing.

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

[0129] 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 at the same time set the correct data type.

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

[0131] Third, the above gives the implementation plan for the key steps. The process also needs to be specifically modified and optimized according to the specific situation of the database fields to achieve better implementation effects.

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

[0133] 1) Performance optimization for field addition: By temporarily storing the added fields in JSON, frequent modification of the database table structure is avoided, improving the stability and flexibility of the system. When a field is added to the original database, the traditional method requires at least one schema change to the target table. After adopting JSON storage, the number of schema changes is reduced to 0 times.

[0134] 2) Since it avoids the need for database schema changes for each newly added or changed data field, this mechanism can significantly reduce the workload and complexity of database maintenance, and lower the long-term operation cost. When a field is added to the original database, the traditional method requires manually creating a new field in the target database table and matching the source table fields and target table fields. After using this mechanism, this step can be omitted and completely handed over to the model for processing.

[0135] 3) Intelligent field expansion: The system intelligently expands the table structure according to the usage frequency of fields and business requirements, optimizing data query and management efficiency.

[0136] 4) This method is not only applicable to the current application scenario, but also has good adaptability and scalability to new data types, structures, or business logic changes that may occur in the future. Combining the advantages of traditional relational storage and NoSQL storage, JSON form is used to store unstable and fast-changing data patterns, while stable and frequently queried fields are converted into independent fields for storage. This hybrid model can find a balance between flexibility and performance, enabling the system to cope with future challenges without major modifications.

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

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

[0139] The computer-readable storage medium may be the device provided in any of the foregoing embodiments or an 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 media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the computer device. Further, the computer-readable storage medium may also include both the internal storage unit and the external storage device of the computer 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 the data that has been output or is to be output.

[0140] The foregoing disclosure is only for the preferred embodiments of the present application, and of course 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 still fall within the scope covered by the present application.

Claims

1. A database intelligent field extension method based on JSON field types, characterized in that, It includes the following steps: S1. Before aggregating multi-party data, check the data of each data source, and separate the common fields and non-common fields of each data source; S2. Add a new column of JSON type in the aggregation target database; after the aggregation is completed, the JSON extension field may contain JSON data of various types and meanings; S3. Use the built-in JSON parsing function in SQL to query whether a certain field name and its value are contained in the JSON extension field; S4. When a certain field name in the JSON extension field, that is, the key name stored in the JSON extension field reaches a certain proportion in the field, it is considered that the field stored with this key name no longer belongs to the redundant field; S5. By passing the JSON field name to the machine learning model, the model will traverse all the key names in the JSON field, calculate the proportion and query frequency feature data of each key name in the field, and return a label of whether the key name needs to be extracted to the database as a new field through a binary classification model, so as to dynamically create database fields.

2. The method for intelligent field extension of a database based on JSON field types according to claim 1, wherein In step S2, the fields of the source table are represented as the key names of the JSON key-value pairs in the JSON extension field, and the data values of the fields of the source table are represented as the values of the JSON key-value pairs in the JSON extension field.

3. The database intelligent field extension method based on JSON field types according to claim 1, characterized in that In step S5, new fields are dynamically created by temporarily storing them in JSON, specifically as follows: Data type derivation, define a field type derivation function: call the function get_field_type to derive the corresponding database field type according to 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 with the PostgreSQL database; Step 2. Read JSON field data: construct an SQL query statement to select the JSON fields of all records in the specified table, and use the cursor object to execute the query to obtain all qualified records; 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 it exists, 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 addition; 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 at the same time; Step 6. Update data: construct and execute an SQL `UPDATE` statement to fill the previously extracted key values into the newly added field to ensure data consistency and integrity.

4. The database intelligent field extension method based on JSON field types according to claim 1, characterized in that The training of machine learning in step S5 includes the following steps: Data loading and preprocessing: import the external data set through the read_csv function of the Pandas library; Data splitting: Through data splitting, the input (X) and expected output (y) during model training can be clearly defined, enabling machine learning algorithms to learn how to best predict the target variable based on the given features, and 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, which can ensure that each feature data matches the correct target data. Training set and test set division: Use 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.

5. The database intelligent field extension method based on JSON field types according to claim 1, wherein The included instruction functions are as follows: Function to obtain all different key names in a JSON field: get_jsonb_keys; Function to infer the database field type based on the type of field value: get_field_type; Function to detect the query frequency of a certain json_key in the log: calculate_query_frequency; Function to count the occurrence times and percentages of each key in a JSON field: get_jsonb_key_stats; Machine learning binary classification model that returns a classification result based on the key name, occurrence times, and query frequency input by the system. Function to determine whether to automatically add a field based on the model return result and write the JSON data to the new field: extract_and_insert_field.

6. The database intelligent field extension method based on JSON field types according to claim 1, wherein, Regarding the feature values of each key name in the JSON field, the feature values refer to the database field proportion and query frequency of each key name in the JSON field when constructing the dataset. These two pieces of 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 training the machine learning model. The specific steps are as follows: Step 1: Call the function get_jsonb_keys to obtain all the key names in the JSON field of the current database. Step 2: Calculate the proportion of the key names in the JSON field in the database fields. Step 3: Calculate the query frequency of each key name: Open and read the content of the query log file at the specified path, use regular expressions to match the query statements in the log file, especially those containing operators pointing to specific JSON keys. Step 4: Integrate information and output: Combine the proportion of the key name and its frequency of occurrence in the query statements to construct the feature data of each key name, providing a comprehensive view of the usage of the target key name in the database and related query activities.

7. The method for intelligent field extension of a database based on JSON field types according to claim 5, characterized in that The calculation of the proportion of key names is achieved by calling the get_jsonb_key_stats function, which is used to analyze the occurrence frequency of each key within 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 existing key names in this column, then counts these keys, and returns a Counter object containing each key and its occurrence count, as well as the total number of all keys.

8. The database intelligent field extension method based on JSON field types according to claim 5, characterized in that Calculate the query frequency of each key name: Identify all query statements that may involve key names of interest to the user, and perform frequency statistics on all found qualifying query statements to understand which key names are most frequently used in queries.

9. 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 executable 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 extension method based on the JSON field type as described in any one of claims 1 to 8.

Images