Support for JSON field selectors (data->"$.a.b.c")

[skolodyazhnyy]

MySQL allows querying a property from a JSON field using special syntax, for example, like so SELECT col->"$.mascot" FROM qtest. In our system, we use normal columns to narrow down results to just a few hundreds rows, but then apply a few JSON filters to further filter and sort records based on the values in the JSON fields. The value stored in JSON is dynamic, and it's not feasible to extract it into a column.

The query looks something like this:

SELECT * FROM records WHERE scope = ? AND data->'$.r_12345' != 0 ORDER BY data->'$.r_12345' ASC

I can add a filter by JSON field using rel.FilterFragment API, but unfortunately, there is nothing similar for ORDER BY portion.

What would be the best way to implement it, and is there a workaround? For example, adding support for col->"..." to a field escape function or maybe just adding support for rel.OrderFragment?

[skolodyazhnyy]

Found workaround rel.SortAsc("^col->'$.mascot'"), still wonder if this might be useful as rel feature.