the missing piece might be an aggregate function e.g. something like

json_object_agg

, if you want ingestion to build the nested structures instead of just treat them like dimensions

i intend to add something like that function someday, but haven’t got to it yet

it could be modeled at query time with the native “expression” aggregator (which im not sure is documented, but used internally for some sql aggregations), though this hasn’t been wired up to work at ingestion time yet

the column itself is a bit bloated currently probably for your use case since it currently stores a ‘raw’ copy of the data in json form, but i have plans to allow customization of that as well to support cases where the raw data isn’t necessary, as well as control over which columns are indexed, and to allow exclusions, etc

what is needed to implement json_object_agg ?

I might give it a try…

let me see if i can find something like docs for it

https://github.com/apache/druid/pull/11104 is the original PR, but the description is a bit stale

https://github.com/apache/druid/blob/master/processing/src/main/java/org/apache/druid/query/aggregation/ExpressionLambdaAggregatorFactory.java#L103 for current set of parameters it accepts

for example,

ARRAY_AGG

is currently only implemented as an expression aggregator https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/ArraySqlAggregator.java#L123

i haven’t tried the json functions in an expression aggregator yet, but i think the idea would be something like define an initial value with json_object expression, then use json_value to extract things from the object, do stuff to them, make a new object with combined values

thanks a lot for the direction! I’ll try to hack something today

heh, js aggregator and functions are security nightmare 😅

the refactor that will make expression aggregator able to work at ingest time is hinted at in the follow-up work on this PR, https://github.com/apache/druid/pull/13653, so hopefully i will get to it soon

im sort of turning nested columns into a universal column, but they will keep working like they do right now too

hence the using it for schemaless ingest and adding better array support, etc

im especially excited for array typed columns, i’ve been planning and laying foundations for this for years now heh

anyway, getting off track here, feel free to ping me if you want to try to add json_object_agg or something like it as an expression aggregator that works with nested columns

also tried with arrays(combine function is not yet well defined):

{
  "type": "expression",
  "name": "country_id_agg",
  "fields": [
    "countryId"
  ],
  "initialValue": "[]",
  "fold": "array_concat(fold( (kv1, kv2) -> json_object('k', json_value(kv1, '$.k'), 'v', json_value(kv1, '$.v') + json_value(kv2, '$.v') ),     filter((kv) -> json_value(kv, '$.k') == countryId, __acc),       json_object('k', countryId, 'v', 1)      ) ,      filter((kv) -> json_value(kv, '$.k') != countryId, __acc)      )",
  "combine": "array_concat(__acc, country_id_agg)"
}

however it fails on

Error: Unknown exception

Cannot find strategy for type [COMPLEX<json>]

org.apache.druid.java.util.common.IAE

will try to work with arrays only

will have a closer look sometime today

oh, i think missing an expression to set a value of a key of an existing json object to some value that will make building json_object_agg hard

were a couple of bugs preventing array_agg working with nested types, https://github.com/apache/druid/pull/13781

will look into if anything besides an additional native expression would be needed for expression based json_object_agg next downtime i have

implementing native stuff would likely be faster than an expression aggregator, so is still worth exploring

thanks for your time @Clint Wylie

there should be a handful of examples

FixedBucketsHistogramQuantileSqlAggregator

is the fixed bucket histogram one

@Clint Wylie can you explain to me please at high level what are requirements for the aggregator to support rollup as well? (maybe it’s already) I just don’t understand how it’s connected

its basically the same at query time with different mechanisms

aggregators don’t have to support being ingested and stored as a column and can be strictly query time

for complex types, typically you implement both an aggregate method that can process the raw inputs to build into the complex type (often explicitly defined as a “build” aggregator) and an aggregate method that can handle merging the complex values, for combining the aggregated complex values across segments (often explicitly called “merge” aggregators)

if the aggregator supports ingestion, it can strictly use the ‘merge’ style of aggregation at query time

ok I have something we can talk about if you have time It’s a bit different, and solves rather specific problem, and not some general capability of aggregate all kinds of JSON complex types… https://github.com/IgorBerman/druid-complexaggs-extension/blob/main/src/test/java/[…]/complexaggs/aggregator/sql/LongFrequencySqlAggregatorTest.java basic tests work, both for native aggregator and sql operator I’d appreciate if you can clarify following : 1. how to finalize to JSON type(currently I’m using ugly json mapper in finalizer to serialize aggregated result to json-string) 2. I assume that I’m starting from regular field(currently only long) and then I count it’s occurrences. My question is - if I will enable rollup, the rollup-ed partial aggregation will be saved in intermediate format? I think I need more tests around it, I’d appreciate any pointers(I couldn’t find something that uses rollup(true) in tests)

https://github.com/apache/druid/issues/12695 has details on the inner workings of nested columns

if you don’t need all of that stuff at query time (fast filtering, optimized value selection from the nested data, etc) then you have other options

lots of complex columns use a generic serializer that just stores a column of whatever variable sized blobs the object strategy spits out

if you want to do something like that, but want compress the blobs, you could re-use some of the parts of the nested column serializer (the

CompressedVariableSizedBlobColumnSerializer

and corresponding

CompressedVariableSizedBlobColumnSupplier

)

btw, if you do not use the nested column serializer directly, i don’t think you can call the type

COMPLEX<json>

since that would conflict with the real nested columns

anyway, i’ll try to have a look at your stuff as soon as i have some free time

oh and a bit of a heads up, the nested column stuff is under very active development, so stuff is still changing internally pretty frequently

ok, just to update I have working version (tested on 24.0.1 druid) meanwhile only counts long values serialization format is array of [key,value,key2,value2]