Nested mapping or comparing fields from different indices [SQL]

Elastic Stack Elasticsearch
1

I'm aiming to replace our existing search functionality with Elasticsearch and I'm taking small steps towards that goal. Right now I'm trying to update a specific method but I would like some guidance.

We have in our system Groups and Units and every Unit belongs to a Group. Right now I've indexed these separate into index groups and index units respectively but my problem is that I've got a some pretty complicated sql queries in some places that I need to translate (I'm in .net using Nest) and I'm starting to wonder if it would be better to index these tables as one nested index instead.

The query I'm trying to translate right now looks like this

            const string selectSql = @"
SELECT 
	groups.GroupId, 
    groups.GroupName,
    units.UnitId, 
    units.UnitName
	FROM Groups groups
LEFT JOIN Units units ON groups.GroupId = units.GroupId
WHERE groups.GroupId IN 
	(SELECT groups.GroupId FROM Groups 
	LEFT JOIN Units units ON groups.GroupId = units.GroupId
	/**where**/
	GROUP BY
	groups.GroupId, 
    groups.GroupName
	/**orderby**/
	OFFSET @Skip ROWS 
	FETCH NEXT @Take ROWS ONLY)
GROUP BY 
	groups.GroupId, 
    groups.GroupName,
    units.UnitId, 
    units.UnitName
/**orderby**/ , units.UnitName
";

So my question is, is there a smart enough way to translate the left join and compare fields from different indices or would it be best to give nested mapping a go making a new, combined index? And if so would that apply to bigger, more nested examples (with more tables, joins and data) too?

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.