Value of ingestion_time() not going beyond mv-expand operator

Question

My requirement is the following logic

  MyTable
        | where ingestion_time() >= ago(1d)
        | mv-expand todynamic(SomeColumnContainingArrayStoredAsString)
        | project category='somecategory', body = pack_all() 
        | extend partitiontime=ingestion_time() 
        | take 10     

So ideally this should produce 3 column resultset as following:-

1. category (string)
2. body (dynamic)
3. partitiontime (datetime)

I very much need the value of ingestion_time() to go to the calculated column partitiontime. But it's always blank in the output. Based on my observation , it seems that the value of ingestion_time() is getting lost because of mv-expand. But I can't do away with mv-expand. I also need partitiontime as a separate column , so I don't want it to go into body. That's why I am trying to issue the extend operator for partitiontime as the last thing. But that's not working , so it's a tricky situation. I am not sure how else can I make this work.

Answer 1

as mentioned in the documentation: This function must be used in context of a table for which the IngestionTime policy is enabled. Otherwise, this function produces null values.

meaning, you'll need to rewrite your query as follows:

MyTable
| extend partitiontime = ingestion_time() 
| where partitiontime() >= ago(1d)
| mv-expand todynamic(SomeColumnContainingArrayStoredAsString)
| project category='somecategory',
          body = bag_remove_keys(pack_all(), dynamic(["partitiontime"]),
          partitiontime
| take 10