Index structure for a relational database table

• US 9,600,507 B2
• Filed: 06/27/2014
• Issued: 03/21/2017
• Est. Priority Date: 07/19/2013
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Li, Lei, Li, Peng, Wang, Xi Ning, Wu, Ya Xin, Zhou, Ying Hua
• Primary Examiner(s): HO, BINH VAN
• Application Number:
  US14/316,911
• Publication Number:
  US 20150026189A1
• Time in Patent Office: 998 Days
• Field of Search: 707/741
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/81 Indexing, e.g. XML tags; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/93 Document management systems

Abstract

Embodiments are directed to building an index structure for a relational database (RDB) table and for querying the RDB table. An aspect includes obtaining JSON documents from a RDB table, identifying JSON objects in the JSON documents and assigning identifiers for them, and building an index structure according to values in the JSON objects such that the index structure includes at least one node that records values in a JSON object and includes the identifier of the specific JSON object. Querying the RDB table includes receiving a query request including an indicator which indicates that the query is to be performed on JSON objects, and searching the index structure based on query conditions in the query request so as to determine a target JSON object that meets the query conditions.

Claims

1. A method for building an index structure for a relational database table, comprising:

obtaining at least one JavaScript Object Notation (JSON) document from the relational database table;

identifying a plurality of JSON objects in the at least one JSON document and assigning identifiers for the plurality of JSON objects, the assigning identifiers for the plurality of JSON objects includes assigning the identifiers based on a relative position of the JSON objects in the at least one JSON document;

wherein identifying the plurality of JSON objects in the at least one JSON document comprises identifying indicative symbols in the at least one JSON document, wherein the symbols are in pairs represented in a form of key;

value; and

wherein a value comprises one or more of an array having a character string within double quotation marks, a number, a Boolean value, an object and another array;

combining a document identifier (ID) of the least one JSON document with each of the assigned identifiers of the plurality of JSON objects to create a global identifier (ID) for each of the plurality of JSON objects, wherein the document ID referenced by a corresponding column in the relational database table in which the at least one JSON document is stored; and

building the index structure according to values in the plurality of JSON objects such that the index structure includes at least one node which records the values in one of the plurality of JSON objects and includes a corresponding global identifier of the at least one JSON document and the one of the plurality of JSON objects.

2. The method of claim 1, wherein assigning the object identifications (IDs) within the JSON documents for the identified JSON objects comprises generating the object IDs for the identified JSON objects.

3. The method of claim 1, wherein assigning the object IDs within the JSON documents for the identified JSON objects comprises using JSON paths corresponding to the identified JSON objects as the object IDs.

4. The method of claim 1, wherein the at least one node is a leaf node of the index structure.

5. A method for querying a relational database table, comprising:

defining an indicator in a query grammar that specifies a query is directed to JSON objects in at least one JSON document included in the relational database table, the indicator indicative of symbols in the at least one JSON document;

wherein the symbols are in pairs represented in a form of key;

value; and

wherein a value comprises one or more of an array having a character string within double quotation marks, a number, a Boolean value, an obiect and another array;

receiving a query request;

determining whether the query request contains the indicator;

upon determining the query request contains the indicator, searching an index structure based on query conditions in the query request, and determining a target JSON object that meets the query conditions; and

upon determining the query request does not contain the indicator, performing a default search of the relational database table according to query conditions in the query request.

6. The method of claim 5, wherein determining the target JSON object meets the query conditions comprises:

determining a target node that records the target JSON object in the index structure based on values specified in the query conditions; and

locating the target JSON object in the relational database table using an identifier recorded in the target node.

7. An apparatus for building an index structure for a relational database table, comprising:

a memory storing program instructions; and

a computer processor configured to execute the program instructions, the program instructions performing the program instructions performing;

obtaining at least one JSON document from the relational database table;

identifying a plurality of JSON objects in the at least one JSON document and assigning identifiers for the plurality of JSON objects, the assigning identifiers for the plurality of JSON objects includes assigning the identifiers based on a relative position of the JSON objects in the at least one JSON document;

wherein identifying the plurality of JSON objects in the at least one JSON document comprises identifying indicative symbols in the at least one JSON document, wherein the symbols are in pairs represented in a form of key;

value; and

wherein a value comprises one or more of an array having a character string within double quotation marks, a number, a Boolean value, an object and another array;

combining a document identifier (ID) of the least one JSON document with each of the assigned identifiers of the plurality of JSON objects to create a global identifier (ID) for each of the plurality of JSON objects, wherein the document ID referenced by a corresponding column in the relational database table in which the at least one JSON document is stored; and

building the index structure according to values in the plurality of JSON objects such that the index structure includes at least one node which records the values in one of the plurality of JSON objects and includes a corresponding global identifier of the at least one JSON document and the one of the plurality of JSON objects.

8. The apparatus of claim 7, wherein the identifying unit is further configured to generate the object identifications (IDs) for the identified JSON objects.

9. The apparatus of claim 7, wherein the identifying unit is further configured to use JSON paths corresponding to the identified JSON objects as the object IDs.

10. The apparatus of claim 7, wherein the at least one node is a leaf node of the index structure.

11. An apparatus for querying a relational database table, comprising:

a memory storing program instructions; and

a computer processor configured to execute the program instructions, the program instructions performing the program instructions performing;

defining an indicator in a query grammar that specifies a query is directed to JSON objects in at least one JSON document included in the relational database table,

the indicator indicative of symbols in the at least one JSON document;

wherein the symbols are in pairs represented in a form of key;

value; and

wherein a value comprises one or more of an array having a character string within double quotation marks, a number, a Boolean value, an object and another array;

receiving a query request;

determining whether the query request contains the indicator;

upon determining the query request contains the indicator, searching an index structure based on query conditions in the query request, and determine a target JSON object that meets the query conditions; and

upon determining the query request does not contain the indicator, performing a default search of the relational database table according to query conditions in the query request.

12. The apparatus of claim 11, wherein the querying unit is configured to:

determine a target node that records the target JSON object in the index structure based on values specified in the query conditions; and

locate the target JSON object in the relational database table using an identifier recorded in the target node.