Schema discovery is finding the structure of data. It helps users understand the meaning of data and write queries to manipulate it. This is typically easy for relational databases, but complex for non-relational (NoSQL) databases with JavaScript Object Notation (JSON) documents. JSON is a representation of documents that contain objects stored in the form of nested key-value pairs. For relational databases, the schema is predefined because the data they contain is structured, but for NoSQL databases, data is usually unstructured or semi-structured. In a collection of JSON documents, the structure of one document can be completely diferent from another. Several algorithms were developed to discover schemas from JSON documents, but they provide the physical structure and semantic information that is insuficient for data understanding and analysis. In this paper, we enumerate the major techniques used to extract a schema from JSON documents and present the next challenge: uncovering hidden data disguised as metadata. This challenge needs to be addressed within the field of JSON schema discovery to enhance the quality of the discovered schemas.
NoSQL is an approach to database design that can accommodate a
wide variety of data models, including key-value, document, and
graph formats [
In this challenge, we aim to uncover hidden data disguised as
metadata by separating data from metadata. In relational databases,
there is a clear line that separates data from metadata, but in JSON
documents, that distinction is not evident. Within some sets of
JSON documents, a portion of the keys is data, but is classified as
metadata during schema extraction because the algorithms do not
take the possibility of keys being part of the data into consideration.
We call this misclassified data dynamic keys. We distinguish them
from the other category of keys called static. Static keys are the
metadata of the documents. Figure 1 shows two sampled JSON
documents adapted from a dataset that contains Amazon products [
The distinction between these two categories of keys can be made by analyzing features that we extract from the JSON key-value pairs. We parse the JSON documents, extract numerous features, and use a classification algorithm to separate static from dynamic keys. Overall, making this distinction within nested JSON documents will enhance the quality of the discovered schemas by clearly identifying which keys are data or metadata, as in relational databases.
The rest of the paper is organized as follows. Section 2 summarizes related work. Section 3 describes our approach to uncover the hidden data. Section 4 presents the results. Section 6 concludes the paper and enumerates potential future works. 2
A JSON schema is a structural representation of a collection of JSON documents that consists of nested key-value pairs in which the keys represent the metadata and the values represent the data.
Several researchers developed diferent algorithms to extract the
JSON schema. Their algorithms take a set of JSON documents as
input, but provide diferent outputs. Wang et al. [
Klettke et al. [
Frozza et al. generate a single schema from JSON documents [
The algorithm parses JSON documents, applies aggregation techniques to group and order documents with the same keys and 1 { " asin " : " 0 3 0 9 0 6 9 9 6 3 " , " c a t e g o r i e s " : [ [ " Boo ks " ] ] , "salesRank": { " B ook s " : 2 1 7 4 2 6 8 } , "related": { " a l s o _ b o u g h t " : [ " 0 4 6 5 0 2 2 2 2 7 " ] , " b u y _ a f t e r _ v i e w i n g " : [ " 0 4 6 5 0 2 2 2 2 7 " ] , " a l s o _ v i e w e d " : [ " 0 4 6 5 0 2 2 2 2 7 " , " 0 3 0 9 0 6 9 9 6 3 " ] , " b o u g h t _ t o g e t h e r " : [ " 0 3 0 9 0 6 9 9 6 3 " ] } } 2 3 4 5 6 { " asin " : " B 0 0 7 M 6 I M Q O " , " t itl e " : " A d r i e n n e V i t t a d i n i F o o t w e a r Women ' s Vida Flat ... " , 7 "salesRank": { " S hoe s " : 1 3 9 9 6 1 , " C l o t h i n g " : 5 9 6 2 7 8 } , 8 "related": { " a l s o _ b o u g h t " : [ " B 0 0 6 W V E S E K " , " B 0 0 7 V M C F L C " ] , " b u y _ a f t e r _ v i e w i n g " : [ " B 0 0 6 W V E S E K " ] , 9 " a l s o _ v i e w e d " : [ " B 0 0 6 W V E S E K " , " B 0 0 8 8 0 C L H E " ] , " b o u g h t _ t o g e t h e r " : [ " B 0 0 6 W V E S E K " ] } }
Spoth et al. [
In this section, we describe the methods we analyzed and will use to address the challenge mentioned above. 3.1
remove duplicates, and stores all the information about the JSON documents in a tree-based data structure called Reduced Schema Unified Structure (RSUS). Here information refers to the objects’ attributes, their datatypes, the elements within and arrays and their datatypes, and the count of attributes frequencies. From RSUS, a JSON schema can be generated for each attribute.
Cánovas Izquierdo and Cabot [
Overall, these existing algorithms and tools provide the structure of times a key appears in all documents relative to the number
of the JSON documents, but the semantic information they provide of documents in the dataset. For example, looking at Figure 3, we
is insuficient to understand and analyze those documents. Figure 2 count the number of times the key minimum, belonging to the
shows the result obtained when using the JSONDiscoverer over the JSON path requirements→minimum appears. However, some
two documents in Figure 1. There is no distinction made between documents may not have this specific key. Nesting level is the depth
attributes that are data (Books, Shoes, Clothing) or metadata of each JSON key within a document. We call these features
intrin(also_bought, also_viewed, bought_together, buy_after_viewing). sic because they only communicate each unique key’s information
Based on our knowledge, there are no existing algorithms that can
distinguish static keys from dynamic ones, in other words,
accurately delineate data from metadata in nested JSON documents. A
JSON key is a field name and a JSON path is the route to a JSON
key. For example, in Figure 3, a modified sample of a dataset
containing metadata of games available on the Steam platform [
We count the number of keys nested under each particular key.
We notice that dynamic keys have more nested keys than static keys.
For our data, the mean represents the average of the total number of keys nested under a particular key across all the documents of a dataset. In Figure 3, we can see that the dynamic key minimum has three nested keys (windows, linux, macOS) whereas the static key windows has two nested keys (processor and memory). 3.1.3 Statistical Dispersion Domain. In this domain, we examine the variation among the numbers of keys nested under each particular key. In Figure 1, across the two documents, the number of nested keys under the dynamic key salesRank varies, while the number of nested keys under the static key related remains constant. We examine this stability by calculating range, standard deviation, and entropy, the most common measures of dispersion.
The range represents the diference between the largest number of keys and the smallest number of keys nested under a particular key.
The standard deviation represents the variation within the number of keys nested under a particular key. Entropy is the amount of uncertainty the frequency of the number of keys nested under a particular key produces. 3.1.4 Distribution Shape Domain. In this domain, we examine the distribution shape of the number of keys nested under dynamic keys and static keys across the documents and notice that static keys have a more normal distribution than dynamic keys. For example, in Figure 1, the static key related has four nested key in each of the two documents, while the dynamic key salesRank has one nested key in the first document and two in the second one. To examine the shape of this distribution, we calculate skewness and kurtosis. Whereas skewness measures the asymmetry between the frequency of the number of keys nested under a particular key, kurtosis measures the weight of the minimum and maximum of the frequency of the number of nested keys, relative to the mean. 3.1.5 Semantic and Contextual Similarity Domain. To expand the distinction between of static and dynamic keys, we calculate three more features: distinct sub-keys, distinct sub-keys data types, and average sub-key contextual similarity. These three features are calculated across all the JSON documents. We choose them because our preliminary analysis shows that the keys nested under dynamic keys are usually more unique and related either structurally, semantically, or contextually than the keys nested under static keys.
The distinct sub-keys feature shows whether dynamic keys have more or fewer unique nested keys than static keys. The distinct subkeys data types feature reveals whether the data types of the keys’ values, nested under dynamic keys, are generally the same or not, compared to the data types of the keys’ values nested under static keys. The average sub-key contextual similarity feature indicates whether the keys nested under dynamic keys are more or less contextually related than the keys nested under static keys.
Distinct sub-keys represent the number of unique keys nested
under a particular key. We also set an upper bound of 100 for this
1 {"pegi": {
" p e g i _ u r l " : " https :// store . c l o u d f l a r e " ,
" p e g i _ t a g s " : [ " Blood " , " and " , " Gore " ] } ,
"requirements": {
"minimum": {
"windows": {
" p r o c e s s o r " : " 1 GHz Intel ... " ,
" m e m o r y " : " 1024 MB RAM " ,
} ,
"linux": {
" p r o c e s s o r " : " 1 GHz Intel ... " ,
" m e m o r y " : " 1024 MB RAM " ,
} ,
"macOS": {
" p r o c e s s o r " : " SSE2 inst ... " ,
" m e m o r y " : " " } } } }
feature. A key with over 100 distinct sub-keys gives no further
important information and significantly skews the distribution of
the number of nested keys. Distinct sub-keys data types represent
the number of unique keys’ values data types under a particular
key. The last feature we calculate in this domain is the average
pairwise distance of embeddings. A key word embedding is a row
of real-valued numbers in which each point represents a
dimension of the key’s linguistic meaning. We use fastText [
After transforming the nested keys of a particular key into vectors,
we compare each unique pair of vectors to measure their cosine
distance and compute the average of these distances. We assume
that nested keys part of the same contextual domain will have a
smaller distance and be under a dynamic key. This phenomenon can
be seen in Figure 3, a sample of Steam game dataset [
Given a set of sets of all the nested keys, the algorithm compares the sets among each other to find which sets have similarities (keys in common) greater than or equal to a user-defined threshold of 0.7.
For example, the keys windows, linux, and macOS form a group because their similarity score is 1, which is greater or equal to the threshold. A score of 1 means that they have the same nested keys,
Logistic regression Random forest SVMs
0.0897 0.1106 0.1110
0.0906 0.1198 0.1129
0.0921 0.1447 0.1029 Table 1: Avg, F1-score Results
0.0826 0.1272 0.0880
0.4875 0.5616 0.4218
F1-score 0.4875 0.5016 0.4218 which are processor and memory. Once the groups are formed, we take the average of the percentage and nesting level of the keys that constitute them and replace all the features of the individual keys within a group with the values of that group.
We collect the above information from various online sources
including Kaggle and GitHub [
After feature extraction, we train a binary classifier to determine whether a key is static or dynamic across all documents. Static and dynamic are our two categories. For this purpose, we use crossvalidation to split that data into testing and training sets. We group keys from the same dataset together. That means that for the different sets that resulted, each dataset had the opportunity to be the testing set. We then test three algorithms: 1) logistic regression, 2) random forest, and 3) support vector machines (SVMs), on the diferent training and testing sets.
To analyze our models, we calculate the average F1-score of the dynamic keys. This value represents the mean of all the F1-scores obtained from the classification of the dynamic keys from each test set. We focus on the average F1-score of the dynamic keys because our datasets are highly skewed toward static keys and we want to know if our model learns to correctly predict and classify dynamic keys with unseen data.
The experimental results can be seen in Table 1. It shows the F1-score as the features from each domain are added progressively (the final column displays results using all of the described features). We obtain low average F1-scores across all three algorithms, but random forest outperforms the other algorithms at distinguishing data from metadata. We observe that the average F1-score sometimes decreases as we add more features from diferent domains. Currently, we do not know which specific features are detrimental to the average F1-score. Overall, it is challenging to obtain high F1-score values because we have disproportionately more static keys than dynamic keys.
We also tested Jxplain against our datasets and obtained an average F1-score of 0.1304, which is significantly lower than the best results we obtained from the other three algorithms we tested.
Our goal is to enhance the quality of the discovered schemas of the JSON documents. For this purpose, we presented a major challenge that needs to be addressed related to JSON schema discovery: uncovering hidden data disguised as metadata by distinguishing static from dynamic keys. We implemented a new algorithm that classifies the JSON keys as data or metadata by extracting and computing various features. Our next steps involve gathering more datasets with dynamic keys, identifying which features are impeding the average F1-score and reducing the number of misclassified keys.