We evaluated our proposed method on MIMICSQL [23], which is the first large-scale medical dataset for text-to-SQL generation task in the health care domain. The medical information in MIMICSQL is derived from MIMIC III. All of the medical information was first anonymized to protect patient privacy and then stored in 5 tables in the medical database (Figure 1), including demographic (Demo), laboratory tests (Lab), diagnosis (Diag), procedures (Pro), and prescriptions (Pres). The questions and corresponding SQL queries in MIMICSQL were automatically generated based on fixed templates [28]. Next, 8 freelancers with medical domain knowledge were recruited from a crowd-sourcing platform to validate the question as realistic and reasonable or rephrase the generated question. The information of the MIMICSQL dataset is summarized in Table 1. We adopted the same data partition as in the TREQS [23], in which all the question-SQL pairs were randomly split into training, validation, and test sets in the ratio of 0.8:0.1:0.1, respectively.

Given a textual question X={x₁,x₂,x₃,...} and the database schema, the goal of this work was to transform the textual question into a SQL query, while ensuring the SQL query retained the same semantic meaning as the textual question.

An overview of MedTS is shown in Figure 2. In the first step, schema linking recognized the database schema information and added corresponding linking marks into the question. Second, the textual question along with linking marks and the database schema information were fed into the pretrained encoder and grammar-based decoder to generate an intermediate representation. Third, the final SQL query was generated based on the intermediate representation.

Similar to the method in IRNet [19], the purpose of schema linking was to recognize the mentioned entities in the medical question and assign a linking mark, which referred to recognizing the column names and table names in the medical database. We enumerated all the n-grams (n∈[1,5]) in a question and arranged them in descending order based on the length. If an n-gram exactly matched a column name or was a subset of a column name, we marked this n-gram as a column. The recognition of a table followed the same way. If an n-gram was recognized as both a column and a table, we marked it as a column because the column mark has higher priority than the table mark. Once an n-gram was identified, we removed other n-grams that overlapped with it. By doing this, we obtained all the entities mentioned in the question. Once an entity was recognized and linked with a mark, it became a span and was encoded into one vector in the encoding process, such as the insurance recognized as a column in Figure 2.

After schema linking, we identified the entities and assigned linking marks. The given medical question X was transformed to [(x₁,τ₁),...,(x_m,τ_m)] where x_i was the i^th span and τ_i was the mark of x_i assigned during schema linking. If x_i was not an entity, τ_i was None. Let C={c₁,c₂,...} and T={t₁,t₂,...} denote the set of all column names and table names. In order to enhance the relationship between the question and database schema, we concatenated the question X and database schema [C,T] with special tokens, where one classification token [CLS] was used as the first token and several separation tokens [SEP] were used as separators of different information, as follows:

In this work, we first used a pretrained BERT as the encoder. The purpose was to convert the textual question with marks assigned by schema linking and database schema into hidden representations via the multihead attention mechanism [29]. Then, for each span, we took the average of the hidden representations of word and mark as the span representation. Last, through a fully connected pooling layer, we obtained the final hidden representations of question H_X, column names H_C, and table names H_T. The nonlinear transformation of [CLS] representation tanh(WH_CLS+b) was used to initialize the decoder, where W and b were trainable parameters.

Tree-structured intermediate representation refers to a syntax tree that bridges the question and the SQL query. It contains SQL information implicated in the textual question and could be transformed to a SQL query more intuitively due to the nature of the tree structure of SQL. Figure 3 demonstrates an example of generating an intermediate tree from an input question. Different from the previous works that used the grammar to assist the SQL generation [19,22], we designed the grammatical rules based on the MIMICSQL dataset. Specifically, we kept only the necessary rules occurring in the dataset to make the prediction more accurately and added a new rule for predicting the condition value.

To construct the intermediate tree from a SQL query, we first defined a set of grammar rules, as shown in Figure 3a. The intermediate tree starts from a root node Z. Since there are no complicated SQL components such as Union in this task, a single node R was directly attached to Z. Then, we attached a node Select or Filter under R, which was determined by the Select clause or Where clause, respectively. For the subtree of node Select, according to the number of columns in the Select clause, the same number of nodes A were attached to node Select. Each node A comprised an aggregation function node, a node C, and a node T. The aggregation function could be either of none, max, min, count, etc, while node C denoted the column name and node T denoted the table name. For the subtree of node Filter, it was determined by the conditions in the Where clause. If there was more than one condition, the corresponding number of Filter nodes would be attached. Next, for each Filter node, it attached a relational operator, a node A, and a node V. Relational operators include more than (mt), less than (lt), equal (e), etc. Node V denotes the condition value. The intermediate tree in Figure 3c was generated by the action sequence in Figure 3b following the grammar rules defined in Figure 3a. The generation process was in the depth-first, left-to-right order.

The generation process of the intermediate tree was formalized into sequential applications of actions. The actions either applied a production rule on the derivation tree or produced a terminal node. According to the grammar rules, we defined 4 types of actions (ie, ApplyRule, SelectColumn, SelectTable, and SelectValue) and adopted the grammar-based decoding strategy [30,31]. ApplyRule(r) applied a production rule r to construct the skeleton of the intermediate tree, and the other 3 types of actions were designed to produce the terminal tokens. Thus, the goal of the decoder was to generate an action sequence A based on the outputs of the encoder. Formally, the decoding process was formalized as follows:

where a_i was the action taken at time step i, a_<i was the sequence of actions before i, and n was the number of total time steps of the whole action sequence.

The probability of selecting a rule r as the current action a_i was calculated as follows:

where denoted the current hidden state of LSTM, v_i and u_i denoted the context vectors that were obtained by performing attention over H_X and [H_C;H_T], e(r) was the one-hot vector for rule r.

The SelectColumn action was implemented via a memory-enhanced pointer network to select a column c, in which the memory was used to record the selected columns [32]. Once a column was selected, it was removed from the schema and recorded in the memory. The probability of selecting a column c was calculated as follows:

where SCH denoted selecting from the schema, MEM denoted selecting from memory, and and denoted the corresponding hidden representations of columns.

For the SelectTable action, we leveraged the relationship between columns and tables to prune irrelevant tables. Thus, the decoder predicted the table t that the selected columns belong to. The probability of choosing a table t was calculated as follows:

As for SelectValue, since the value was always mentioned in the textual question, the decoder extracted a condition value v by finding a start position and an end position from the question via 2 different pointer networks, respectively, as follows:

where p_start and p_end denoted the probabilities of the start and end positions.

Afterward, in order to keep the extracted value consistent with the value in the database, we also adopted the condition-value-recover technique proposed in TREQS [23] to find the most similar value in the look-up table content by computing the ROUGE-L (Recall-Oriented Understudy for Gisting Evaluation based on the Longest Common Subsequence) [33] score between them.

According to the grammar rules in Figure 3, when inferring a SQL query from an intermediate tree, we traversed the whole intermediate tree and mapped each node to the corresponding SQL component. The production rule applied on node Z denoted that it was just a single SQL query. The node R represented the start point. Following the child nodes of node R, we generated the skeleton of a SQL query, such as whether the SQL query had a Select clause or Where clause corresponding to the node Select and Filter, respectively. The node Select indicated how many columns the Select clause had. The node Filter indicated how many different conditions were in the Where clause. Based on the subtree of node Select or Filter, we filled in the details (ie, the aggregation function, relational operator, column name, table name, and condition value). The From clause was generated from the nodes of selected tables by identifying the shortest path that connected these tables in the schema.

We adopted the pretrained uncased base BERT as our encoder, and the hidden size was set as 768. For the decoder module, the hidden size of LSTM was set as 300. The maximum length of the action sequence was set as 128. The size of the attention vector was set as 300. The coarse-to-fine framework [34-36] was used to model the generation process. The Adam optimizer [37] was adopted to train the model parameters for 100 epochs. The learning rate was set as 1e-06, and gradient clipping was used with a maximum gradient norm of 5.0. During training, we set the batch size as 8. The numbers of ApplyRule, SelectColumn, and SelectTable candidate actions were 24, 39, and 5 respectively. The size of the SelectValue candidate action was based on the length of the input textual question. We selected the model which achieved the best performance on the validation set. The MedTS was implemented with PyTorch [38] and trained on a Tesla V100 GPU (NVIDIA Corp). Our code has been shared on GitHub to facilitate other researchers [39].

We compared our proposed MedTS with 5 competitor methods. Seq2Seq [14] is an LSTM-based model with the attention mechanism, in which the SQL query is regarded as an ordinary word sequence. PtrGen [15] is a Seq2Seq-based pointer-generator network, which can directly copy the word from the input question to alleviate the repetition and out-of-vocabulary (OOV) phenomenon. SQLNet [18] is a sketch-based text-to-SQL model to avoid the order problems that occurred in the Seq2Seq model. Coarse2Fine [36] is a 2-stage neural architecture for text-to-SQL. A classifier is first used to obtain a rough sketch of the SQL query and then the details of SQL are filled in based on the input and the sketch individually. TREQS [23] is also a 2-stage text-to-SQL model, including an attentive-copying mechanism and condition value recovery mechanism.

All text-to-SQL methods were evaluated with 2 popular metrics [5], execution accuracy (Acc_EX) and logic form accuracy (Acc_LF), which are complementary to evaluate the quality of the generation of SQL queries.

Table 2 provides the quantitative results on the validation and test sets. Seq2Seq achieved 0.103 Acc_LF and 0.173 Acc_EX on the test set. SQLNet performed better than Seq2Seq, since it considered the dependencies between the components of SQL query based on a graph derived from the sketch. But it was not easy to cover all the queries. PtrGen performed much better than SQLNet with 0.180 Acc_LF and 0.292 Acc_EX on the test set because it directly extracted words from textual questions to reduce the OOV words, especially when most values occurred in the original question. Coarse2Fine achieved decent performance since it incorporated the schema information into question encoding, but it was limited by the number of sketches and had difficulty handling more complex SQL. TREQS further improved the performance via several effective mechanisms, such as controlled generation and placeholder replacement. But it is just based on the Seq2Seq framework and did not consider the intrinsic structure information of SQL itself. Compared to all the methods mentioned above, MedTS achieved the best performance with 0.681 Acc_LF and 0.880 Acc_EX on the validation set and 0.784 Acc_LF and 0.899 Acc_EX on the test set, which outperformed the best competitor method by at least 29% and 27% in terms of Acc_LF and Acc_EX, respectively, on the test set.

In order to further analyze the generation result, we broke down the SQL queries into 5 components according to the SQL grammar structure, including aggregation operation, aggregation column, table, condition column along with its operation, and condition value. The experimental results are shown in Table 3. Since Coarse2Fine cannot handle multitable questions and is limited by table-aware assumption, its performance cannot be compared to other methods. Aggregation operation refers to the operations in the Select clause used to aggregate all the values of a column and return a single value, such as Count, Sum, Avg, etc. All methods except for Coarse2Fine achieved a very high accuracy of more than 97%. Aggregation column was the target column in the Select clause for the aggregation operation. MedTS outperformed other methods significantly by at least 5% and 12% on validation and test sets, respectively. Table was the target table in the From clause. Except for the Coarse2Fine, the other methods achieved similar accuracy. MedTS achieved the best performance. Condition column along with its operation represented the column and operation in the Where clause. Compared to the other competitor methods, MedTS achieved a large improvement by at least 8% on the test set. Condition value refers to the condition value in the Where clause. It was observable that the performance on condition value primarily played a vital role in the overall SQL generation performance. MedTS achieved improvement by at least 11% on the test set. In summary, the experimental results of MedTS on each component of SQL were relatively balanced and better, especially the performance on aggregation column and condition value.

We also conducted an ablation study to analyze the impact of schema linking as well as the use of different types of pretrained representations on question encoding and show the results in Table 4. When the schema linking was not used, the performance of MedTS dropped by 1.4% on Acc_LF and 1.3% on Acc_EX on the test set, which demonstrated the effectiveness of schema linking. The tested pretrained representations included a recurrent neural network (RNN)-based encoder (ie, BioWord2Vec [40]) and two BERT-based encoders (ie, ClinicalBERT [41] and BioBERT [42]). As shown in Table 4, the RNN-based encoder with pretrained BioWord2Vec performed far worse than the BERT-based encoder by at least 21.0% on Acc_LF and 17.9% on Acc_EX on the test set. We argue that the main reason is that the LSTM encoder cannot model the interaction of the entire sequence itself. As for the BERT-based encoders, we observed that the performance of ClinicalBERT was inferior to the others since it specializes in clinical notes that are obviously different from the natural language text. Compared to MedTS (with uncased base BERT), BioBERT achieved slightly better performance since it uses the medical literature for pretraining which is more beneficial to the representations of medical questions.

Our proposed model MedTS achieved the best Acc_LF and Acc_EX on the validation and test sets, with pretrained encoder and grammar-based decoder. The abstract syntax tree was introduced as the intermediate representation to bridge the gap between medical text and the SQL query. The primary outcomes of this study were (1) a new state-of-the-art model for medical text-to-SQL generation task was proposed and validated and (2) an online demonstration system with the capabilities of transforming the medical text to SQL query and further returning the query results was provided. Experimental results demonstrated that MedTS has great potential to help medical experts facilitate clinical information retrieval and improve the efficiency of decision-making for medical diagnosis and treatment.

MedTS has the ability to capture the semantic relationship between words within textual questions and the dependency relationship between the text and database schema, benefitting from the multihead attention mechanism adopted by the pretrained encoder. It is difficult for competitor methods to obtain information as rich using the RNN-based encoder. Meanwhile, MedTS can effectively reduce the search space via the grammar-based decoding strategy, which predefines grammatical rules and introduces the tree-structured intermediate representation. Although several mechanisms were designed in the competitor methods to make the generated SQL query more accurate, they still view the SQL query as an ordinary word sequence and ignore the intrinsic structure characteristic of SQL itself, which makes them perform worse than MedTS.

In addition to quantitative evaluations, we conducted an extensive set of qualitative case studies on the test data to analyze the generated SQL query. We manually analyzed all 1000 text-SQL pairs in the test set. Among them, 784 generated SQL queries that were entirely consistent with the ground truth, and 115 generated SQL queries that were not identical to the ground truth in the logical form but also achieved accurate execution results. Most of them were caused by the different positions of the 2 tables connected by the join operation (eg, example 1 in Table 5). This phenomenon also explains why the quantitative evaluation results of Acc_EX are higher than Acc_LF in Table 2. In addition, 5 generated SQL queries were correct but considered wrong by the Acc_EX because of the various orders of column in the select clause (eg, example 2 in Table 5). The remaining 96 pairs generated incorrect SQL queries. We grouped their errors into different categories from 2 perspectives: clause and element. The clauses included select, join, and where, and the elements included operator, table, column, value, and others. The statistical results are shown in Table 6. Note that there was no operator in the join clause. Similarly, since the value only presented in the where clause, the value error of select and join clauses was none. When there was a table error in the where clause, it was usually due to the wrong decision in the select or join clauses, so we did not count these types of errors again. The rest of the errors, such as more or less conditions, are grouped into other categories.

From the element’s perspective, we observed that the prediction errors of column and value account for the majority. From the perspective of the clause, more than 50% of clause errors were in where clauses, while most where clause errors were due to incorrect values or columns. Example 3 in Table 5 is a representative case of where clause error due to the incorrect value. The value of expire_flag is a numeric type in SQL but a text description in the question. Example 4 in Table 5 shows a case of where clause error due to the wrong column, in which the admityear and dob_year are semantically close, leading to the wrong choice. It was challenging to achieve high accuracy in these cases, since MedTS is based on the pointer network that selects terms from textual questions to generate SQL queries. The operation error means that the condition column and value in the where clause are correct but the operator is wrong, which may return completely opposite results, as shown by example 5 in Table 5.

In the medical field, a few studies have focused on the text-to-SQL task, but most of them either proposed rule-based methods [20,21] or validated on the small-scale datasets [22]. Wang et al [23] constructed the first large-scale medical text–to-SQL dataset and proposed a neural model TREQS to undertake this task. However, TREQS focused on solving the OOV problem and condition value generation. Compared with the rule-based methods, our proposed model has better applicability and can be extended to other datasets. Compared with the previous neural models, our model adapts more advanced deep learning methods to this task and achieves the optimal experimental performance on a large-scale dataset.

As discussed above, several problems are still to be solved, such as improving the accuracy of the condition column and value in the where clause, especially the gap between natural language description and the value stored in the database. In future work, we will continue to improve the accuracy and robustness of the model (eg, introducing more schema information such as the data type of column to achieve the goal of practical deployment). In addition, the form of question and SQL in MIMICSQL is relatively simple, which is not enough to cover various situations in the practical applications. Therefore, we plan to keep exploring different data forms for more practical scenarios, such as generating SQL queries containing more complex clauses.

In this work, we proposed a medical text–to-SQL method named MedTS, which incorporates a BERT-based attention encoder to obtain schema-enhanced text representation and a grammar-based LSTM decoder to generate the intermediate action sequence before generating a SQL query. By introducing the intermediate representation, MedTS can reduce the search space during decoding and mitigate the mismatch problem between the medical question and the SQL query. Experiments on the MIMICSQL dataset demonstrate that MedTS substantially outperforms the state-of-the-art methods. Further analyses on each component of SQL query and the case study confirm MedTS’s effectiveness and robustness, demonstrating its strong potential.