sql: implement JSON{,B}_{OBJECT,}_AGG

[justinj]

I think this would be an ambitious, but doable first issue.

There's a handful of things that need to happen for this:

• We need a public interface from pkg/util/json to incrementally construct a
  JSON object/array. In the case of objects, since their keys need to be
  sorted, we would want to do something like push the new keys into a priority
  queue (the Go stdlib has a heap implementation). This should be tested
  independently from SQL-land. I think this will probably be useful outside of
  these aggregates so it should be a somewhat general interface.
• Add the aggregates in aggregate_builtins.go that use these new constructors.
  These will need be careful to monitor their memory usage, see the
  implementation of ARRAY_AGG for reference.
• Test the aggregates in SQL-land. Care should be taken to compare the
  semantics of what is implemented with what Postgres does (handling of NULL,
  duplicate keys, etc.).

This needs to happen for the next major release so I'll get to it if nobody is
interested in picking it up.

[heyihong]

Hi @justinj, is the issue being worked on? I would like to pick it up if possible.

[justinj]

@heyihong it's not being worked on, you're free to pick it up! You're probably a good person to work on it anyway since it requires your work on #20234 I believe :)

[heyihong]

JSON_OBJECT_AGG requires support of multiple arguments in aggregation function. Related to #10495

[justinj]

I think that issue only relates to aggregates which have an argument which is not aggregated (say, for STRING_AGG), but I think since JSON_OBJECT_AGG aggregates over both inputs we should be able to support that type signature (though I haven't exhaustively checked). For example FINAL_VARIANCE (which is used internally) aggregates over three inputs.

[heyihong]

I felt that aggregating keys and values separately and combining them finally to build json object seems to be a short-term solution, compared with supporting aggregate functions with secondary arguments. Is it ok to add the secondary arguments support instead?

[justinj]

@heyihong Sorry if I was unclear - we support all the features required to implement this. What we don't support is having another argument which is not aggregated over, like in STRING_AGG. I believe this feature should be implementable without modifying the aggregate function system.

[heyihong]

@justinj According to the hint you gave, the way I could find is to aggregate keys and values separately and then combine them by FinalRendering. If that's the way we should implement, I still doubt that we may need to change the aggregate function system a little bit. The reason is that un like FINAL_VARIANCE , json_object_agg is order sensitive(i.e. we should avoid local stage aggregation) if rows are sorted. The current system seems to not support skipping local aggregate stage while keeping the final aggregate stage and final rendering in dist aggregation specification.

[justinj]

Hey @heyihong, I talked to a coworker who knows a bit more about distributed execution - he says there should be a way to do what is needed, but you might need to, worst-case, add a "passthrough" aggregator that does nothing and configure that as the local aggregator step. Does that make any sense?

[heyihong]

Yes, it makes sense. Thanks for the explanation.

[C0rWin]

it seems to be stalled for a while, I would like to give it a try if no one minds :)

[jordanlewis]

Far from it, @C0rWin! We'd love to see this get done. Please follow up with questions if you have them.

[C0rWin]

@jordanlewis I've created draft PR #48306 still missing unit test and most likely missed something else, hence would appreciate any suggestions or comments for improvement. More specifically would be glad to get some guidelines regarding unit testing.