This message was deleted.

What are you trying to do? Something like:

select category, count(*), anyValue(jsonColumn) as "randomValue"
from table
group by 1

So any full json would be ok?

I could not find a 'stringAny' function, but I did find a 'stringEvery' function, which checks to see if every element of the set satisfies a given condition. So long those lines I am guessing stringAny would indicate if one or more elements of the set satisfied the given condition? If that is the logic of this function, then in SQL we would generally implement that logic using a filtered aggregate, e.g.: • select max(case when strField like 'x%' then 1 else 0 end) The above aggregate expression would return either 1 or 0. Nested JSON columns can then then accessed in place of the field reference, e.g.: • select max(case when JSON_VALUE(jsonCol, '$.strField') like 'x%' then 1 else 0 end) and of course you can use whatever conditional expression you want in a CASE statement. Let us know if this is not what you were looking for either. Thanks. John

I believe

stringAny

how you put in ingestion spec a rollup equivalent for

ANY_VALUE(expr, maxBytesPerString)

in SQL

Looking for aggregation function in group by queries. especially select any value in the group. ANY_VALUE(expr, maxBytesPerString) throws exception that complex<json> not supported.

maybe

ANY_VALUE(TO_JSON_STRING(expr), maxBytesPerString)

Still not sure what you are looking to do. If you can post your SQL statement here that should hopefully clarify. Fyi this statement against the KTTM-Nested demo dataset works for me:

select adblock_list GroupName, 
        count(*) NumEntries,
        max(case when json_value(event, '$.type') like 'Save%' then 1 else 0 end) HasSave
   from "kttm-nested" 
  group by 1

Here is a screenshot showing the statement and output:

Thanks John for looking into this. I wanted to do this from this example data https://druid.apache.org/docs/latest/querying/nested-columns.html#native-batch-ingestion

SELECT
  department,
  ANY_VALUE(product, 100),
  ANY_VALUE(details, 100)
FROM "nested_data_example"
GROUP BY department

Like this?

Thanks that works