hey team! very new to Druid - we're testing it out and I'm trying to get up to speed with it. so far it's been great! 🙂 I've got a question I haven't been able to find an answer for in the official docs: it is possible to

UNNEST

a nested JSON column at ingestion time? to be more precise, I'm using streaming ingestion from Kafka and each source row has a column that looks like this (simplified):

{"items": [{"key": "item1", "value": {...}}, {"key": "item2", "value": {...}}, ..., {"key": "itemN", "value": {...}}]}

- that is,

items

is not constant-sized. to make the data easier to work with at query time I'd like to

UNNEST

it so that it results in something like this:

__time,item_key,item_value
1,item1,`{...}`
2,item2,`{...}`
...
N,itemN,`{...}`

where

item_value

is a

COMPLEX<json>

. I can easily do this when batch ingesting using SQL, for example:

INSERT INTO "my_new_table"
SELECT
  __time,
  JSON_VALUE(item, '$.key') AS item_key,
  JSON_VALUE(item, '$.value') AS item_value
FROM "original_table"
CROSS JOIN UNNEST(JSON_QUERY_ARRAY(original_column, '$.items')) AS item
PARTITIONED BY ...

so is this sort of unnesting possible for streaming ingestion?

Hi Giorgio, please check out flattenSpec ... https://druid.apache.org/docs/latest/ingestion/data-formats#flattenspec

hey @John Kowtko! I've read about

flattenSpec

, but my understanding is that it only allows flattening a nested column into other columns of the same row, whereas I'd like to unnest 1 source row into N Druid rows (at ingestion time). the docs don't seem to mention any unnest operator for

flattenSpec

I don't know of a way to "unnest" single events into multiple event records during streaming ingestion ...

Giorgio, it is not possible to use UNNEST functionality for streaming ingestion (Kafka). For batch ingestion it should work and within queries it should work as well. Alternatively, you could:

hey @David Brauchli, thanks for the answer! yes, I'm actually already doing [2] and [3]; however [3] is quite annoying to manage because SQL Batch ingestion tasks are not managed by a Supervisor, which means they are one-offs, and I'd need to have a cron job or similar scheduling them... (though, it might be what I end up doing) [1] is also an option though the idea of having the source data ingested directly into Druid is very enticing 🙂 so I'll leave it for last very interested about the druid extension! what do you have in mind?