Nested Objects Search
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'])
-
Identifier with simple nesteds (ISN) has such view:
baseField1:nestedFieldDeep1.nestedFieldDeep2….nestedFieldDeepN
-
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
-
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:
AND / OR operators to combine two or more conditions.
All basic logical operations to create conditions: =, <>, <, >, >=, <=, like, NOT like, is null, is NOT null, IN.
Aggregate functions: COUNT, MAX, MIN, SUM, AVG
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