currently i always flatten the struct

we may consider a new rule to json serialize the struct

You don't need to serialize the struct in the new added module. It can be

Map

or

List

In the rule, config the columns to extract and columns to flatten. If the extracted column is primitive type, leave it as is; if the extracted column is complex type, convert it to

Map

or

List

okay, i think the default behavior is different from yours and mine

in mine, primitive types takes precedence over json type

so i always try to flatten it until i cannot, e.g. array

so perf is preferred

While, I think the only difference here is to make the flatten columns configurable

We can start with always flattening all complex types

Then everything will be extracted into primitive type, and you don't need the json index feature

right, that's the behavior in the proposal

i think we are on the same page

hey, folks, i have a PR to implement the transformer proposed in the design. https://github.com/apache/incubator-pinot/pull/6845 there will be followup PRs on other surrounding components

plz take a look

@User did we summarize the notes from our zoom call the other day

Also, there was a fair amount of discussion on complex type (STRUCT/MAP/LIST) as well, so here is a summary related to that discussion:

Adding support for JSON	has commonalities with adding	support	for complex data types (STRUCT, LIST, MAP) with the key difference between JSON and STRUCT/LIST/MAP support being that JSON will not enforce schema validation (in keeping with JSON standard) while as STRUCT/LIST/MAP will support schema validation. A table could be defined with both a JSON column and a STRUCT/LIST/MAP column. For example:
 nestedColumn1 JSON,
 nestedCOLUMN2 STRUCT (name : string, age: INT : salary : INT, addresses : LIST (STRUCT ( apt: int, street : string, city : string, zip : INT )))

The implementation steps that we describe under	"Near Term Enhancements" are common to supporting both JSON and	complex	data types (STRUCT, LIST,
MAP). Both JSON and STRUCT/LIST/MAP columns:
-would be stored as text,
-would use JsonIndex for fast filtering (with additional support for multidimensional arrays)
-be queried via new dot/array based syntax as proposed in "Language enhancements"

In the long term it is quite possible that these data types share common hierarchical indexing functionality and storage mechanisms while providing JSON specific semantics with JSON column type and a more well-defined schema and type checking semantics with STRUCT/LIST/MAP type.

@User If you are moving forward with long term storage aspects of JSON, would definitely like to discuss that further with you. cc: @User

yes, will write it up

@User in continuation of what we discussed earlier, this is the existing query:

select jsoncolumn,json_extract_scalar(jsoncolumn, '$.person.companies[*].name', 'STRING') from jsontable where id = 106

which produces the results:

{"person":{"name":"daffy duck","companies":[{"name":"n1","title":"t1"},{"name":"n2","title":"t2"}]}}, 	["n1","n2"]

What we would like to do is to rewrite this query from user query:

select jsoncolumn.person.companies[*].name from jsontable where id = 106

I believe by "unnesting" you are referring to the fact that

["n1","n2"]

could be separate rows in Pinot? Also, for JSON storage support, we had briefly looked at BSON format (mongodb), JSON2 (derivative of BSON used in postgres), and OSON (used by oracle json database.). In either case, we were looking at a format that would help to minimizing parsing of json strings into json object before query evaluation as is being done in json_extract_scalar.

I was referring to a different query/requirement

Support for nested (multi-dimensional) array: https://github.com/apache/incubator-pinot/pull/6877

@User @User Please take a look

JSON_MATCH filter expression to be JSONPath compatible

Nice

Thanks @User. Will review tomorrow

Also, this is the PR for JSON column type support: https://github.com/apache/incubator-pinot/pull/6878

@User has left the channel

Hi everyone! Can some fine lad lend me a hand with this.

I have this json array to ingest ['{"objects":[{"detection":{"bounding_box":{"x_max":0.18486635386943817,"x_min":0.11353801190853119,"y_max":0.23475037515163422,"y_min":0.06590475142002106},"confidence":1.0,"label_id":1},"emotion":{"confidence":0.9898877739906311,"label":"happy","label_id":1,"model":{"name":"0003_EmoNet_ResNet10"}},"h":122,"region_id":131,"w":91,"x":145,"y":47}],"resolution":{"height":720,"width":1280},"timestamp":294364856}', ' {"onecallid": 1716745129 } ']

no matter how much i try to ingest the onecallid field, it doesnt happen,