title: How to handle nested fields in Clickhouse's AggregatingMergeTree Materialized View?
body: <p>Is there a way to combined (concatenate) <code>Nested</code> fields in a group by in Clickhouse in an <code>AggregatedMergeTree</code> materialied view?</p>
  <p>Imagine that I have a table with a schema (simplified) like this:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE test
  (
      key1 String,
      key2 String,
      clicks Int32,
      points Nested(x Int32, y Int32)
  ) Engine = Log
  </code></pre>
  <p>I would like to be able to use an <code>AggregatingMergeTree</code> to generate a materialized view that combines the nested fields by &quot;concatenating&quot; them (as if nested records could be just concatenated as complex values as some SQL dialects can).</p>
  <p>If I do that as a query it's possible:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      key1,
      key2,
      arrayMap(p -&gt; p.1, points) as x,
      arrayMap(p -&gt; p.2, points) as y
  FROM
  (
      SELECT  
          key1,
          key2,
          groupArray(tuple(x, y)) as points
      FROM
      (
          SELECT 
              key1, key2, points.x as x, points.y as y
          FROM test 
          ARRAY JOIN points
      )
      GROUP BY key1, key2
  )
  </code></pre>
  <p>Is there a way to express this in the query used in a materialized view based on the <code>AggregatingMergeTree</code> engine? The best I could come up with is something like this:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE MATERIALIZED VIEW testagg1
    engine = AggregatingMergeTree partition by key1 order by (key1, key2)
  AS 
  SELECT
      key1,
      key2,
      sumState(clicks) as clicks,
      groupArrayState(points.x) as `points.x`,
      groupArrayState(points.y) as `points.y`
  FROM test
  GROUP BY key1, key2
  </code></pre>
  <p>I can then get the flattened form using this query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      arrayMap(p -&gt; p.1, arrayZip(x, y)) as x1,
      arrayMap(p -&gt; p.2, arrayZip(x, y)) as y1
  FROM
  (
      SELECT 
          key1,
          key2,
          groupArrayMerge(`points.x`) as x, 
          groupArrayMerge(`points.y`) as y 
      FROM testagg1
      GROUP BY key1, key2
  ) as points 
  ARRAY JOIN x, y
  </code></pre>
  <p>It works but seems a bit complex.</p>
  <ul>
  <li>Is there a simpler and better way to do this?</li>
  <li>Are the <code>groupArrayState</code> and <code>groupArrayMerge</code> aggregation used above <strong>guaranteed</strong> to preserve the same ordering of the x/y fields in the parallel arrays?</li>
  </ul>