How to extract json-object and count the value

[c2h4ds]

I have a json-type column in pinot like below:
{"value1":1, "value2":2, "value3":3}
{"value1":2, "value4":4, "value5":1}
{"value2":3, "value3":1}

now I just wanna extract the key and count the value of each key, the result is shown like below:
"value1":3
"value2":5
"value3":4
"value4":4
"value5":1

I mean there are more than 5 kinds of "values"(maybe value6, value7 ...) so how to write the corresponding sql ?

[Jackie-Jiang]

I don't think pinot can do it as of now. The json extraction is key based, meaning the key has to be provided. The workaround would be to store each key as a separate column, but it can potentially result in too many columns.
Do you know any other database or query engine that can solve this request? We can potentially build an aggregation function to solve it, but I don't think that is a general solution.

[siddharthteotia]

@Jackie-Jiang @c2h4ds - please take a look at the KVGEN functionality I had described in #7881

While, it is not a "Standard SQL" function, it is commonly supported in OLAP engines to allow analysis on nested data