Nested Objects Querying

General Structure

When there are Java POJO Plain Old Java Object. A regular Java object with no special restrictions other than those forced by the Java Language Specification and does not require any classpath. classes that are not made of primitive type fields only, those objects are available for retrieval through SQL.

It is also possible to store an original JSON document in its original form within a Space Where GigaSpaces data is stored. It is the logical cache that holds data objects in memory and might also hold them in layered in tiering. Data is hosted from multiple SoRs, consolidated as a unified data model. and retrieve its data through SQL.

 

Syntax: to select nested field by using JSONPath expression (each next nested level accessing by “.”, getting value from array by integer index - [int] and from map by spring key - ['stringKey'])

 

1. Identifier with simple nesteds (ISN) has such view:

   baseField1:nestedFieldDeep1.nestedFieldDeep2….nestedFieldDeepN

2. Identifier with array nesteds (IAN) has such view:

   baseField1:nestedFieldDeep1[intArrayIndex | *].nestedFieldDeep2 | *….nestedFieldDeepN - as the index in square brackets only 
   >= 0 numbers have to be used

3. Identifier with map nesteds (IMN) has such view:

   baseField1:nestedFieldDeep1[‘stringMapKey’  | *].nestedFieldDeep2 | *….nestedFieldDeepN  - as the key in square brackets only 
   >= 0 numbers have to be used

 

General structure for Select with nesteds is:

SELECT ISN | alias, IAN | alias, IMN  | alias …  aggregation_function(ISN) | alias  FROM 
TableExpression1
[INNER | CROSS | LEFT | RIGHT] JOIN
TableExpression1
ON (booleanExpression with usage ISN)
WHERE (booleanExpression with usage (ISN | IAN | IMN  | alias) only from one side of 
operator
GROUP BY (ISN | alias)
HAVING (booleanExpression with usage (ISN | IAN | IMN  | alias))
ORDER BY (ISN | alias, IAN | alias, IMN  | alias …  aggregation_function(ISN) | alias) ASC | 
DESC

 

Additional Supported Features:

1. AND / OR operators to combine two or more conditions.

2. All basic logical operations to create conditions: =, <>, <, >, >=, <=, like, NOT like, is null, is NOT null, IN.

3. Aggregate functions: COUNT, MAX, MIN, SUM, AVG

4. BETWEEN

 

Examples:

For example, suppose we have a class (or SpaceDocument with DocumentProperties):

Company company {
 Integer id;
 Director director  {
   Integer id;>
   Integer salary;
   String email;
   DevTeam team {
     Integer id;
     String specialization;
     Employee[] employees;
     Map<String, Employee> lineEmployeesMap;
     Employee mainEmployee {
       Integer id
       String email;
      }
     }
    }
   }

Company: Integer Id, Director director

Director: Integer id, Integer salary, String email, DevTeam team

DevTeam: Integer id, String specialization, Employee[] employees, Map<String, Employee> lineEmployeesMap, Employee mainEmploye

Employee: Integer id, String email

In the above example we have a list of companies each having a company id and directory, who has a name, salary and DevTeam.

The DevTeam contains an id, specialization name and three types of employees: object, array and StringKey-Employee Map,

 

To search for all the directors for all companies (company director = toString of director):

Select company.director from Company as company

As director is a complex object, the result will be printed by overriden method toString in director class.

 

A way to access nested path field

If we want to get a particular simple field (e.g. director id) from the base field (in our example it is directory) we need to use a colon.  This symbol separates the base field part from the nested json path part:

Select company.director:id as company_director_id from Company as company

• company: alias to Company table

• director: base field of Company table

• id: nested field of director base field with deep level 1

• company_director_id: alias to provide such a name to the result column, otherwise the default column name = base field = director,

 

A nested and base field in one select:

Get company id and director salary:

Select id as company_id, director:salary as company_director_salary, director:team as company_director_team from Company

Example result if we have one company in the database (emails will be the entire array of emails to string):

company_id	 |company_director_salary 	    | company_director_team |
1                |20				     | DevTeam toString()    |

 

Using nested queries in aggregation functions and groupBy with having section.

Calculate the sum of salaries for directors who have teams with the same specializations for all companies where the sum of the director salary is not higher than 1000 and order sum for max to min:

Select sum(director:salary) as director_salary, director:team.specialiazation
as director_team_specialization from Company group by director_team_specialization 
having director_salary <= 1000 ORDER BY director_salary DESC

 

Using Arrays as nesteds

To get the company id and list of emails for all employees in all companies:

Select id as company_id, director:team.employees[*].email as employee_emails from Company

Example result (emails will be entire array of emails to string):

company_id	 |employee_emails                                          |
1                |["test@gmail.com","test1@gmail.com"]                     |
2		  |["test3@gmail.com","test4@gmail.com","test5@gmail.com"]  |

To get the company id and each second email of employees in each company:

Select id, director:team.employees[1].email from Company

 

Using Maps as Nesteds - accessing map fields giving a key

To get the director email and emails of all employees that have the label "BestEmploee" for each company:

Select director:email,director:team.lineEmployeesMap['BestEmployee'].email from Company

 

Using Nesteds with Joins

Join Company and Employee Classes row when director email equals to the employee email, or if devTeam mainEmployee email equals to the employee email:

Select new Employee.id,company,id,company.director:email from Company as company INNER JOIN 
Employee as newEmployee ON newEmployee.email=director:email OR newEmployee.email = director:team.mainEmployee.email