apache / apache/pinot

Support Structured, Repeated Records

Open
#7,978 8 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

As of today, the JSON index is an inverted index and can't be further configured by the user via schema settings. It would be very powerful to support the ability to specify different data types and indexes for fields of JSON columns. Using the example from the JSON Docs[1]:

```
{
"name": "adam",
"age": 30,
"country": "us",
"addresses":
[
{
"number" : 112,
"street" : "main st",
"country" : "us"
},
{
"number" : 2,
"street" : "second st",
"country" : "us"
},
{
"number" : 3,
"street" : "third st",
"country" : "ca"
}
]
}
```

For example, having the ability to specify that `number` is an `int` and building a range index would allow for range queries. The street address number is a contrived example that's not really practical, but it gets the point across.

Adding a range index could be done today using an ingestion transform. But adding a range index to JSON data becomes very powerful when combined with the idea that JSON context can be maintained when using JSON_MATCH.

Ex.
```sql
SELECT ...
FROM mytable
WHERE JSON_MATCH(person, '"$.addresses[*].number"<=2 AND "$.addresses[*].country"=''ca''')
```

This would not match the above `adam` record, because within the context of the JSON object itself there is no satisfactory result where both predicates are true. Using an ingestion transform cannot reproduce this functionality. Note that range index is a single example, and ideally all types of indexes would be supported for this type of semi-structured JSON data with a predictable schema.

[1] https://docs.pinot.apache.org/basics/indexing/json-index#chained-key-lookup

Contributor guide

Open the contributing guide

Research direction

The payload names no implementation files or tests; start with the linked JSON index documentation and the JSON_MATCH entry point. Trace how nested JSON fields are indexed and how context is preserved, then establish tests for typed indexes and the address-number/country example before implementation.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.