[GitHub] [pinot] egalpin commented on issue #7978: Support Structured, Repeated Records

[GitBox <gi...@apache.org>]

egalpin commented on issue #7978:
URL: https://github.com/apache/pinot/issues/7978#issuecomment-1276490883

   I was actually considering using IN_PARTITIONED_SUBQUERY as a way to implement rudimentary nested column support.  I think this could potentially be implemented natively without the need for IN_PARTITIONED_SUBQUERY in the future as well.
   
   Imagine a table named foo with data organized like so, based on the [json index docs](https://docs.pinot.apache.org/basics/indexing/json-index#when-to-use-json-index):
   
   
   |   id   |   name   |  age  |    country |  parent_id |   addresses.number |   addresses.street |   addresses.country |
   |--------|----------|-------|------------|------------|--------------------|--------------------|---------------------|
   | 1      | "adam"   | 30    | "us"       |  1      |   null             |   null             |   null              |
   | null   | null     | null  |  null      |  1         |   112              |   "main st"        |   "us"              |
   | null   | null     | null  |  null      |  1         |   2                |   "second st"      |   "us"              |
   | null   | null     | null  |  null      |  1         |   3                |   "third st"       |   "ca"              |
   
   
   This would allow for rich filtering support using all existing indexing techniques in Pinot with “context” maintained.  Ex: “Who has lived in us while having an address number greater than 3?” (adam would match). Could use range index on addresses.number to make filters as efficient as possible.  (age filter only present to show mixture of parent and nested filtering)
   
   ```sql
   SELECT name, age, country
   FROM foo
   WHERE
     IN_PARTITIONED_SUBQUERY(id, 'SELECT ID_SET(parent_id) FROM foo WHERE addresses.number > 3 AND addresses.country = ''us'' ') = 1
     AND age > 10
   ```
   
   Whereas a similar query of “Who has lived in ca while having an address number greater than 3?” (adam would not match)
   
   ```sql
   SELECT name, age, country
   FROM foo
   WHERE
     IN_PARTITIONED_SUBQUERY(id, 'SELECT ID_SET(parent_id) FROM foo WHERE addresses.number > 3 AND addresses.country = ''ca'' ') = 1
     AND age > 10
   ```
   
   It would also support querying “across” multiple records for the same parent_id i.e. “bypassing” context. Ex: “Has Adam ever lived in ca , and also lived on main st at some point?”
   
   ```sql
   SELECT name, age, country
   FROM foo
   WHERE
     IN_PARTITIONED_SUBQUERY(id, 'SELECT ID_SET(parent_id) FROM foo WHERE addresses.country = ''ca''') = 1
     AND IN_PARTITIONED_SUBQUERY(id, 'SELECT ID_SET(parent_id) FROM foo WHERE addresses.street = ''main st''') = 1
     AND age > 10
   ```
   
   This pattern would also support efficient aggregation of values from the "nested" column (as compared to something like `jsonextractscalar` which needs to deserialize every match to accomplish the same).  Ex. (note the inversion of `id` and `parent_id` in the use of `IN_PARTITIONED_SUBQUERY` this time, given that we want the result set to include the nested rows as well, and not only the parent rows).  This is where it's important that the parent row have id == parent_id so that nested elements can be grouped with their parent (if aggregated).
   
   ```sql
   SELECT parent_id, name, SUM(addresses.number)
   FROM foo
   WHERE
     IN_PARTITIONED_SUBQUERY(parent_id, 'SELECT ID_SET(id) FROM foo WHERE name = ''adam'' AND age > 10') = 1
   GROUP BY parent_id
   ```
   
   Thoughts on this approach?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org