This message was deleted.

Hi Anshu, I don't know how to suppress those field from the output using native query, but if you write your query in SQL it's fairly straightforward, e.g:

select channel, avg(numPages) 
  FROM (
        select channel, flags, count(page) numPages 
          from wikipedia 
         group by 1, 2
       )
 group by 1

Fyi I did an Explain Plan to get the native query from the above SQL (see below), but when I ran that native query it printed the intermediate results ... so in this case I couldn't use Explain to get a working native query for your use case. Fyi here is the native query from Explaining the above:

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "groupBy",
      "dataSource": {
        "type": "table",
        "name": "wikipedia"
      },
      "intervals": {
        "type": "intervals",
        "intervals": [
          "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
        ]
      },
      "granularity": {
        "type": "all"
      },
      "dimensions": [
        {
          "type": "default",
          "dimension": "channel",
          "outputName": "d0",
          "outputType": "STRING"
        },
        {
          "type": "default",
          "dimension": "flags",
          "outputName": "d1",
          "outputType": "STRING"
        }
      ],
      "aggregations": [
        {
          "type": "filtered",
          "aggregator": {
            "type": "count",
            "name": "a0"
          },
          "filter": {
            "type": "not",
            "field": {
              "type": "null",
              "column": "page"
            }
          },
          "name": "a0"
        }
      ],
      "limitSpec": {
        "type": "NoopLimitSpec"
      },
      "context": {
        "queryId": "9ea54258-e72d-412f-ad6e-f690dcc54d49",
        "sqlOuterLimit": 1001,
        "sqlQueryId": "9ea54258-e72d-412f-ad6e-f690dcc54d49",
        "useNativeQueryExplain": true
      }
    }
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "d0",
      "outputName": "_d0",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "doubleSum",
      "name": "_a0:sum",
      "fieldName": "a0"
    },
    {
      "type": "filtered",
      "aggregator": {
        "type": "count",
        "name": "_a0:count"
      },
      "filter": {
        "type": "not",
        "field": {
          "type": "null",
          "column": "a0"
        }
      },
      "name": "_a0:count"
    }
  ],
  "postAggregations": [
    {
      "type": "arithmetic",
      "name": "_a0",
      "fn": "quotient",
      "fields": [
        {
          "type": "fieldAccess",
          "fieldName": "_a0:sum"
        },
        {
          "type": "fieldAccess",
          "fieldName": "_a0:count"
        }
      ]
    }
  ],
  "limitSpec": {
    "type": "default",
    "columns": [],
    "limit": 1001
  },
  "context": {
    "queryId": "9ea54258-e72d-412f-ad6e-f690dcc54d49",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "9ea54258-e72d-412f-ad6e-f690dcc54d49",
    "useNativeQueryExplain": true
  }
}

Hi @John Kowtko Yepp SQL I know. We will be working on json query since we are working on data sketches where there is not full support for sql grammar

Hi Anshu, fyi I checked with @Sergio Ferragut ... he experimented a bit and found that if he wrapped the entire statement in an outer groupBy you could project out only the columns you want (as dimensions). So maybe try that and let us know if it works for you.

If possible could you provide an example

Here is from Sergio ...

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "groupBy",
      "dataSource": {
        "type": "query",
        "query": {
          "queryType": "groupBy",
          "dataSource": {
            "type": "table",
            "name": "wikipedia"
          },
          "intervals": {
            "type": "intervals",
            "intervals": [
              "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
            ]
          },
          "granularity": {
            "type": "all"
          },
          "dimensions": [
            {
              "type": "default",
              "dimension": "channel",
              "outputName": "d0",
              "outputType": "STRING"
            },
            {
              "type": "default",
              "dimension": "flags",
              "outputName": "d1",
              "outputType": "STRING"
            }
          ],
          "aggregations": [
            {
              "type": "filtered",
              "aggregator": {
                "type": "count",
                "name": "a0"
              },
              "filter": {
                "type": "not",
                "field": {
                  "type": "null",
                  "column": "page"
                }
              },
              "name": "a0"
            }
          ],
          "limitSpec": {
            "type": "NoopLimitSpec"
          },
          "context": {
            "queryId": "0151551c-c165-4dc6-bb64-65fdaef2ac4d",
            "sqlOuterLimit": 1001,
            "sqlQueryId": "0151551c-c165-4dc6-bb64-65fdaef2ac4d",
            "useNativeQueryExplain": true
          }
        }
      },
      "intervals": {
        "type": "intervals",
        "intervals": [
          "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
        ]
      },
      "granularity": {
        "type": "all"
      },
      "dimensions": [
        {
          "type": "default",
          "dimension": "d0",
          "outputName": "_d0",
          "outputType": "STRING"
        }
      ],
      "aggregations": [
        {
          "type": "doubleSum",
          "name": "_a0:sum",
          "fieldName": "a0"
        },
        {
          "type": "filtered",
          "aggregator": {
            "type": "count",
            "name": "_a0:count"
          },
          "filter": {
            "type": "not",
            "field": {
              "type": "null",
              "column": "a0"
            }
          },
          "name": "_a0:count"
        }
      ],
      "postAggregations": [
        {
          "type": "arithmetic",
          "name": "_a0",
          "fn": "quotient",
          "fields": [
            {
              "type": "fieldAccess",
              "fieldName": "_a0:sum"
            },
            {
              "type": "fieldAccess",
              "fieldName": "_a0:count"
            }
          ]
        }
      ],
      "limitSpec": {
        "type": "default",
        "columns": [],
        "limit": 1001
      },
      "context": {
        "queryId": "0151551c-c165-4dc6-bb64-65fdaef2ac4d",
        "sqlOuterLimit": 1001,
        "sqlQueryId": "0151551c-c165-4dc6-bb64-65fdaef2ac4d",
        "useNativeQueryExplain": true
      }
    }
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "_d0",
      "outputName": "channel",
      "outputType": "STRING"
    },
    {
      "type": "default",
      "dimension": "_a0",
      "outputName": "avg_numPages",
      "outputType": "double"
    }
  ]
}

Thanks