Hey folks, can anyone confirm whether range queries will work with

JSON_MATCH

? Ex. Modifying example query from json-index 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"
    }
  ]
}

SELECT ... 
FROM mytable 
WHERE JSON_MATCH(person, '"$.addresses[*].number">2')

I would expect that this

adam

doc/row would match. But I’m not sure if range queries are supported. Thanks! https://docs.pinot.apache.org/basics/indexing/json-index#chained-key-lookup

the json index is a purely inverted index, doesn't support range queries

Thanks for confirming @User. I had considered the approach you mentioned but from my understanding one important feature I would lose doing that would be the context maintained using the JSON_MATCH function. Ex. assuming that ranges did work, if the query was:

SELECT ... 
FROM mytable 
WHERE JSON_MATCH(person, '"$.addresses[*].number">2 AND "$.addresses[*].country"=''us''')

this would not match. Using the approach of extracting the address numbers would remove the possibility of using the context for conjunctions. Do you happen to know if there have been attempts to implement effectively defining a schema for a json column, allowing definition of different data types/indexes within the JSON? Any idea off-hand if that’s completely infeasible, reasonable with effort, etc?

Hi Evan, you will have to use a combination of JSON_MATCH and json_extract

thanks @User 👍

the planner is optimized to only evaluate the json country = “us”

Could you elaborate on this a bit? My initial impression was that your recommendation might still have the limitation where JSON context is not maintained, but your input about the planner made me wonder. Keen to learn, thanks!

my bad, I see what you are asking for

no problem, I still got some ideas from your input 🙂

yes, mine too, I missed the context

but you can build on that idea

what might be complexity level be, if even possible at all, of having different indexes for different properties of a JSON blob? I would think a fairly common use case would be structured, repeated (array) entities. Would be super powerful to have this capability and I’d be interested in helping to contribute if it’s feasible at all

I was just chatting with Richard about adding this. It is possible to support all types on indexes in json fields

will do 👍