Sql-like query in elastic

Hello!

Here is an example of a kind of a query I am after:

I have an index containing 300 million JSON documents. Each of these documents, references one of 3 million items (let's call them "item" - they are numbered from item_1 to item_3000000). Each of the documents in the index is basically a item_X:value combination where each value is a floating number belonging to one of 100 different groupings of these values.

I want to produce a matrix where each row is item_X (from 1 to 3,000,000) and each column is the grouping.

Basically:
item group_1 group_2 ...group_100
1 0.5 0.7 .... 0.01
2 0.1 0.3 .... 0.99
.
.
3,000,000 ......

The item:value pairings are a JSON document (each a separate one) stored in the same index (essentially 300,000,000 documents). What is the most efficient way to create this query, if any? I understand this is essentially trying to mimic a relational structure (join on the same table) but the need for this query has come up and I am unsure how to go about it...

Thanks for any input!

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