Optimizing JSON document usage

An approach is provided for optimizing data fetching. A query employing a method to fetch data from a JSON document is received. An amount of time required to execute the query and a number of nested layers in a traversal of the JSON document required to fetch the data are determined. Based on the amount of time and the number of nested layers, a cost associated with an execution of the query is calculated. The cost is determined to exceed a threshold value. Responsive to the determination that the cost exceeds the threshold value and using historical query patterns and historical query execution times, a schema of the JSON document is re-designed. The data is fetched from the JSON document using the re-designed schema.

BACKGROUND

The present invention relates to database management, and more particularly to optimizing a fetching of data in a JavaScript® Object Notation (JSON) document. JavaScript is a registered trademark of Oracle America, Inc. located in Redwood Shores, Calif.

NoSQL databases use JSON structures (i.e., JSON documents) as a data format to represent data. Complex applications such as banking, medical, order processing, supply chain, data integration, etc. can have complex data schemas with nested (i.e., multi-level) JSON structures. Data represented by JSON structures is transferred from one application to another based on the needs of the applications. For a given application use case, if the required data is nested inside JSON structures, code is written to fetch a JSON structure from an underlying repository in which the JSON structure is stored, load the fetched JSON structure into memory, and traverse through one or more levels of the JSON structure via a query to fetch the desired data.

SUMMARY

In one embodiment, the present invention provides a method of optimizing data fetching. The method includes receiving, by one or more processors, a query employing a method to fetch data from a JSON document. The method further includes determining, by the one or more processors, an amount of time required to execute the query and a number of nested layers in a traversal of the JSON document required to fetch the data. The method further includes based on the amount of time required to execute the query and the number of nested layers in the traversal required to fetch the data, calculating, by the one or more processors, a cost associated with an execution of the query. The method further includes determining, by the one or more processors, that the cost exceeds a threshold value. The method further includes responsive to the determining that the cost exceeds the threshold value and using historical query patterns and historical query execution times, re-designing, by the one or more processors, a schema of the JSON document. The method further includes fetching, by the one or more processors, the data from the JSON document using the re-designed schema of the JSON document.

In another embodiment, the present invention provides a computer program product which includes a computer readable storage medium having computer readable program code stored on the computer readable storage medium, the computer readable program code being executed by a central processing unit (CPU) of a computer system to cause the computer system to perform a method. The method includes the computer system receiving a query employing a method to fetch data from a JSON document. The method further includes the computer system determining an amount of time required to execute the query and a number of nested layers in a traversal of the JSON document required to fetch the data. The method further includes based on the amount of time required to execute the query and the number of nested layers in the traversal required to fetch the data, the computer system calculating a cost associated with an execution of the query. The method further includes the computer system determining that the cost exceeds a threshold value. The method further includes responsive to the determining that the cost exceeds the threshold value and using historical query patterns and historical query execution times, the computer system re-designing a schema of the JSON document. The method further includes the computer system fetching the data from the JSON document using the re-designed schema of the JSON document.

In another embodiment, the present invention provides a computer system including a central processing unit (CPU); a memory coupled to the CPU; and a computer readable storage medium coupled to the CPU. The computer readable storage medium contains instructions that are executed by the CPU via the memory to implement a method. The method includes the computer system receiving a query employing a method to fetch data from a JSON document. The method further includes the computer system determining an amount of time required to execute the query and a number of nested layers in a traversal of the JSON document required to fetch the data. The method further includes based on the amount of time required to execute the query and the number of nested layers in the traversal required to fetch the data, the computer system calculating a cost associated with an execution of the query. The method further includes the computer system determining that the cost exceeds a threshold value. The method further includes responsive to the determining that the cost exceeds the threshold value and using historical query patterns and historical query execution times, the computer system re-designing a schema of the JSON document. The method further includes the computer system fetching the data from the JSON document using the re-designed schema of the JSON document.

DETAILED DESCRIPTION

Overview

Using known techniques to manage the fetching of data in NoSQL databases that employ JSON structure(s), if desired data is nested inside multiple levels of the JSON structure(s), complex functions and/or a significant amount of code is required to be written to traverse through the JSON structure(s) to fetch the desired data, which provides a direct negative impact on application performance. As more levels of a JSON structure need to be traversed, the complexity of the coding increases. Performance including response time is negatively affected because the application code is required to traverse through multiple levels of JSON nested structures or because the size of the JSON structure is significant. Excessive memory consumption is also an issue because of the size of the JSON structure in which the data is fetched and because the JSON structure resides in the application hosted server memory until the memory is garbage collected.

Embodiments of the present invention address the aforementioned unique challenges of managing the fetching of data by optimizing a design of JSON documents by generating suggestions regarding re-designing existing JSON documents or designing new JSON documents to improve application performance, where the re-designing or designing of the JSON documents is based on past querying patterns and query execution times to fetch the desired attributes from the JSON documents.

System for Optimizing Data Fetching in a JSON Document

FIG. 1is a block diagram of a system100for optimizing data fetching in a JSON document, in accordance with embodiments of the present invention. System100includes a computer102, which executes a software-based data fetching optimization system104. In one embodiment, computer102is operatively coupled via a computer network (not shown) with one or more computer devices (not shown) operated by respective one or more users.

Data fetching optimization system104receives a query106sent from a computing device operated by a user. Query106includes a method used in a software application to fetch data that resides in a JSON document. In one embodiment, query106includes a getter method to fetch the data in the JSON document. The computing device that sends query106can be computer102or another computing device (not shown). Data fetching optimization system104determines an amount of time needed to execute query106and a number of nested layers that are required to be traversed in a schema of the JSON document to fetch the data. Data fetching optimization system104determines a cost based on a combination of the aforementioned amount of time, the aforementioned number of nested layers, a size of the JSON document, and an amount of memory used to load the JSON document.

Data fetching optimization system104accesses data in a data repository108, which is operatively coupled to computer102. Data repository108is a persistent data store that includes schemas (also referred to herein as JSON schemas or JSON document schemas) of JSON documents and information specifying queries that have been executed against the schemas. In one embodiment, data repository108includes (1) JSON schemas, (2) query patterns, and (3) query execution times (i.e., response times). The JSON schemas stored in data repository108were the subject of previously executed queries to fetch data from the JSON documents. A given query pattern stored in data repository108specifies nested layers of a schema of a corresponding JSON document that is required to be traversed to fetch data in the JSON document as a result of executing the query associated with the query pattern. The schema of the corresponding JSON document is a schema stored in data repository108. Query execution times stored in data repository108are amounts of time that are needed to complete executions of respective queries that fetch data via traversals of respective JSON schemas (i.e., JSON schemas stored in data repository108). In other embodiments, data repository108includes the schemas, query patterns, and query execution times described above, and also includes sizes of the JSON documents, amounts of memory utilized to load the JSON documents, and/or amounts of data retrieved from the previously executed queries that fetched data from the JSON documents, where the JSON documents are specified by the schemas stored in data repository108.

Based on the aforementioned cost and using the query patterns and the query execution times stored in data repository108, data fetching optimization system104generates (i) a re-design110of the schema of the JSON document or (ii) a design112of a new schema of the JSON document. In one embodiment, data fetching optimization system104generates a suggestion that specifies the re-design110or the design112and sends the suggestion to a computing device operated by the user.

In one embodiment, data fetching optimization system104re-designs the schema of the JSON document according to re-design110or designs a new schema for the JSON document according to design112. After the re-designing of the schema or the designing of the new schema, data fetching optimization system104fetches the data from the JSON document using the re-designed schema or the new schema, so that the performance of the query execution is improved (i.e., the query execution time is faster compared to executing the query without the re-design110of the schema and faster compared to executing the query without the new design112).

The functionality of the components shown inFIG. 1is described in more detail in the discussion ofFIG. 2andFIG. 3presented below.

Process for Optimizing Data Fetching in a JSON Document

FIG. 2is a flowchart of a process of optimizing data fetching in a JSON document, where the process is implemented in the system ofFIG. 1, in accordance with embodiments of the present invention. The process ofFIG. 2starts at step200. In one embodiment, prior to step202, a computing device operated by a user sends query106(seeFIG. 1) to computer102(seeFIG. 1). In step202, data fetching optimization system104(seeFIG. 1) receives query106(seeFIG. 1), which employs a method to fetch data from a JSON document.

In step204, data fetching optimization system104(seeFIG. 1) determines (i) an amount of time needed to execute query106(seeFIG. 1) (i.e., query execution time) and (ii) a number of nested layers in a traversal of the JSON document, where the traversal is required to fetch the data. Alternatively, in step204, data fetching optimization system104(seeFIG. 1) determines (i) and (ii) listed above, together with (iii) a size of the JSON document and/or (iv) an amount of memory needed to load the JSON document in memory.

In step206, based on the amount of time determined in step204and the number of nested layers determined in step204, data fetching optimization system104(seeFIG. 1) calculates a cost associated with an execution of query106(seeFIG. 1) to fetch the data in the JSON document. Alternatively, based on a combination of: (i) the amount of time determined in step204, (ii) the number of nested layers determined in step204, (iii) a size of the JSON document, and (iv) an amount of memory needed to load the JSON document in memory, data fetching optimization system104(seeFIG. 1) calculates a cost associated with an execution of query106(seeFIG. 1) to fetch the data in the JSON document. In one embodiment, data fetching optimization system104(seeFIG. 1) assigns weights to items (i), (ii), (iii), and (iv) listed above and the calculation of the cost is based in part on the assigned weights.

In step208, data fetching optimization system104(seeFIG. 1) determines whether the cost calculated in step206exceeds a threshold value. In one embodiment, data fetching optimization system104(seeFIG. 1) receives the threshold value prior to step208.

If data fetching optimization system104(seeFIG. 1) determines in step208that the cost calculated in step206exceeds the threshold value, then the Yes branch of step208is followed and step210is performed.

Prior to step210, data fetching optimization system104(seeFIG. 1) retrieves historical query patterns from data repository108(seeFIG. 1), where the retrieved query patterns are associated with schemas stored in data repository108(seeFIG. 1), and where the schemas match the schema of the JSON document. Alternatively, the retrieved query patterns are associated with types of JSON documents, where the types match the type of the JSON document. In one embodiment, the type of a JSON document is a description of a business function that includes fetching data from the JSON document (e.g., the type of JSON document XYZ is order processing).

In step210, using one of the retrieved historical query patterns, data fetching optimization system104(seeFIG. 1) generates a re-design110(seeFIG. 1) of the schema of the JSON document. For example, the re-design110(seeFIG. 1) may specify a structure of the JSON document that is a flat structure or a structure that has fewer nested layers than the current nested layer structure of the JSON document (i.e., a re-designed structure that is less complex than the structure of the JSON document prior to the re-design). Step210also includes data fetching optimization system104(seeFIG. 1) determining a cost of executing the query106(seeFIG. 1) to fetch data in a re-designed version of the JSON document (i.e., the JSON document having the re-design110(seeFIG. 1)). The cost determined in step210is calculated in the same way that cost was calculated in step206. If the cost determined in step210is less than the cost determined in step206, the re-design110causes a decrease in one or more of the following: the query execution time, the number of nested layers traversed, the size of the JSON document, and the amount of memory needed to load the JSON document.

Data fetching optimization system104(seeFIG. 1) checks if the cost determined in step210exceeds the aforementioned threshold. If the cost exceeds the threshold, data fetching optimization system104(seeFIG. 1) repeats step210using another historical query pattern retrieved from the data repository108(seeFIG. 1) until the cost does not exceed the aforementioned threshold. In one embodiment, data fetching optimization system104(seeFIG. 1) spawns threads that performs the iterations of step210described above.

In step212, data fetching optimization system104(seeFIG. 1) fetches the data from the JSON document by using the re-designed schema of the JSON document.

In one embodiment, data fetching optimization system104(seeFIG. 1) generates a suggestion that specifies the re-design110(seeFIG. 1) and sends the suggestion to the computing device that sent query106(seeFIG. 1) to computer102(seeFIG. 1). In one embodiment, data fetching optimization system104(seeFIG. 1) receives from the aforementioned computing device a user authorization of an implementation of the suggestion, and step212is performed in response to receiving the user authorization.

In one embodiment, prior to step210, data fetching optimization system104(seeFIG. 1) determines that a complexity of the schema of the JSON document for fetching the data exceeds a threshold amount of complexity. In response to the determination that the complexity of the schema exceeds the threshold amount, data fetching optimization system104(seeFIG. 1) in an alternate to step210designs a new schema for the JSON document according to design112(seeFIG. 1), which is based on the retrieved historical query patterns and historical query execution times. The new schema is designed so that the cost of executing the query106(seeFIG. 1) to fetch data in the JSON document specified by the new schema having design112(seeFIG. 1) does not exceed the aforementioned threshold. In an alternate to step212, data fetching optimization system104(seeFIG. 1) fetches the data from the JSON document by using the new schema that was designed according to design112(seeFIG. 1).

In step214, data fetching optimization system104(seeFIG. 1) determines whether there is another query to process. If data fetching optimization system104(seeFIG. 1) determines in step214that there is another query to process, then the Yes branch of step214is followed and the process loops to step202. If data fetching optimization system104(seeFIG. 1) determines in step214that there is not another query to process, then the No branch of step214is followed and the process ofFIG. 2ends at step216.

Returning to step208, if data fetching optimization system104(seeFIG. 1) determines that the cost does not exceed the threshold value, then the No branch of step208is followed and step214is performed, as described above.

Computer System

FIG. 3is a block diagram of a computer included in the system ofFIG. 1and that implements the process ofFIG. 2, in accordance with embodiments of the present invention. Computer102is a computer system that generally includes a central processing unit (CPU)302, a memory304, an input/output (I/O) interface306, and a bus308. Further, computer102is coupled to I/O devices310and a computer data storage unit312. CPU302performs computation and control functions of computer102, including executing instructions included in program code314for data fetching optimization system104(seeFIG. 1) to perform a method of optimizing data fetching in a JSON document, where the instructions are executed by CPU302via memory304. CPU302may include a single processing unit or be distributed across one or more processing units in one or more locations (e.g., on a client and server).

Memory304includes a known computer readable storage medium, which is described below. In one embodiment, cache memory elements of memory304provide temporary storage of at least some program code (e.g., program code314) in order to reduce the number of times code must be retrieved from bulk storage while instructions of the program code are executed. Moreover, similar to CPU302, memory304may reside at a single physical location, including one or more types of data storage, or be distributed across a plurality of physical systems in various forms. Further, memory304can include data distributed across, for example, a local area network (LAN) or a wide area network (WAN).

I/O interface306includes any system for exchanging information to or from an external source. I/O devices310include any known type of external device, including a display, keyboard, etc. Bus308provides a communication link between each of the components in computer102, and may include any type of transmission link, including electrical, optical, wireless, etc.

I/O interface306also allows computer102to store information (e.g., data or program instructions such as program code314) on and retrieve the information from computer data storage unit312or another computer data storage unit (not shown). Computer data storage unit312includes a known computer readable storage medium, which is described below. In one embodiment, computer data storage unit312is a non-volatile data storage device, such as, for example, a solid-state drive (SSD), a network-attached storage (NAS) array, a storage area network (SAN) array, a magnetic disk drive (i.e., hard disk drive), or an optical disc drive (e.g., a CD-ROM drive which receives a CD-ROM disk or a DVD drive which receives a DVD disc).

Memory304and/or storage unit312may store computer program code314that includes instructions that are executed by CPU302via memory304optimize data fetching in a JSON document. AlthoughFIG. 3depicts memory304as including program code, the present invention contemplates embodiments in which memory304does not include all of code314simultaneously, but instead at one time includes only a portion of code314.

Further, memory304may include an operating system (not shown) and may include other systems not shown inFIG. 3.

In one embodiment, computer data storage unit312includes data repository108(seeFIG. 1).

As will be appreciated by one skilled in the art, in a first embodiment, the present invention may be a method; in a second embodiment, the present invention may be a system; and in a third embodiment, the present invention may be a computer program product.

Any of the components of an embodiment of the present invention can be deployed, managed, serviced, etc. by a service provider that offers to deploy or integrate computing infrastructure with respect to optimizing data fetching in a JSON document. Thus, an embodiment of the present invention discloses a process for supporting computer infrastructure, where the process includes providing at least one support service for at least one of integrating, hosting, maintaining and deploying computer-readable code (e.g., program code314) in a computer system (e.g., computer102) including one or more processors (e.g., CPU302), wherein the processor(s) carry out instructions contained in the code causing the computer system to optimize data fetching in a JSON document. Another embodiment discloses a process for supporting computer infrastructure, where the process includes integrating computer-readable program code into a computer system including a processor. The step of integrating includes storing the program code in a computer-readable storage device of the computer system through use of the processor. The program code, upon being executed by the processor, implements a method of optimizing data fetching in a JSON document.

While it is understood that program code314for optimizing data fetching in a JSON document may be deployed by manually loading directly in client, server and proxy computers (not shown) via loading a computer-readable storage medium (e.g., computer data storage unit312), program code314may also be automatically or semi-automatically deployed into computer102by sending program code314to a central server or a group of central servers. Program code314is then downloaded into client computers (e.g., computer102) that will execute program code314. Alternatively, program code314is sent directly to the client computer via e-mail. Program code314is then either detached to a directory on the client computer or loaded into a directory on the client computer by a button on the e-mail that executes a program that detaches program code314into a directory. Another alternative is to send program code314directly to a directory on the client computer hard drive. In a case in which there are proxy servers, the process selects the proxy server code, determines on which computers to place the proxy servers' code, transmits the proxy server code, and then installs the proxy server code on the proxy computer. Program code314is transmitted to the proxy server and then it is stored on the proxy server.

Another embodiment of the invention provides a method that performs the process steps on a subscription, advertising and/or fee basis. That is, a service provider can offer to create, maintain, support, etc. a process of optimizing data fetching in a JSON document. In this case, the service provider can create, maintain, support, etc. a computer infrastructure that performs the process steps for one or more customers. In return, the service provider can receive payment from the customer(s) under a subscription and/or fee agreement, and/or the service provider can receive payment from the sale of advertising content to one or more third parties.

The present invention may be a system, a method, and/or a computer program product at any possible technical detail level of integration. The computer program product may include a computer readable storage medium (or media) (i.e., memory304and computer data storage unit312) having computer readable program instructions314thereon for causing a processor (e.g., CPU302) to carry out aspects of the present invention.

Computer readable program instructions (e.g., program code314) described herein can be downloaded to respective computing/processing devices (e.g., computer102) from a computer readable storage medium or to an external computer or external storage device (e.g., computer data storage unit312) via a network (not shown), for example, the Internet, a local area network, a wide area network and/or a wireless network. The network may comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers and/or edge servers. A network adapter card (not shown) or network interface (not shown) in each computing/processing device receives computer readable program instructions from the network and forwards the computer readable program instructions for storage in a computer readable storage medium within the respective computing/processing device.

Aspects of the present invention are described herein with reference to flowchart illustrations (e.g.,FIG. 2) and/or block diagrams (e.g.,FIG. 1andFIG. 3) of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer readable program instructions (e.g., program code314).

These computer readable program instructions may be provided to a processor (e.g., CPU302) of a general purpose computer, special purpose computer, or other programmable data processing apparatus (e.g., computer102) to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks. These computer readable program instructions may also be stored in a computer readable storage medium (e.g., computer data storage unit312) that can direct a computer, a programmable data processing apparatus, and/or other devices to function in a particular manner, such that the computer readable storage medium having instructions stored therein comprises an article of manufacture including instructions which implement aspects of the function/act specified in the flowchart and/or block diagram block or blocks.