Ingesting from large json files to kusto from blob - expanding array of objects

Question

I am trying to ingest json file into kusto (.zip file), and further processing json using update policies

Approach 1 :file has following contents

{
  "id": "id0",
  "logs": [
    {
      "timestamp": "2021-05-26T11:33:26.182Z",
      "message": "test message 1"
    },
    {
      "timestamp": "2021-05-26T11:33:26.182Z",
      "message": "test message 1"
    }
  ]
}

.create table test (
  logs : dynamic
)

.create-or-alter table test ingestion json mapping 'testmapping'
'['
'{"column":"logs","path":"$.logs","datatype":"","transform":"None"}'
']'

.ingest into table test(
  h@"sasUrlfromazureBlob"
)
with (
  format = "multijson",
  ingestionMappingReference = "testmapping",
  zipPattern = "*"
)

Above is ingesting the entire logs array in one row, but I want it to be expanded into multiple rows

Approach2:

Input file conatains:

[
    {
      "timestamp": "2021-05-26T11:33:26.182Z",
      "message": "test message 1"
    },
    {
      "timestamp": "2021-05-26T11:33:26.182Z",
      "message": "test message 1"
    }
]

.create table test (
  logs : dynamic
)

.create-or-alter table test ingestion json mapping 'testmapping'
'['
'{"column":"logs","path":"$","datatype":"","transform":"None"}'
']'

Above nicely expands logs into multiple rows (2 rows in this example) But when I select array from an object (Approach1) it dumps into single row and problem with this is, it has a limitation of 1MB data for dynamic data type

Answer 1

If the issue is the transformation from the input data as in option #1 to the desired data as option #2, you should use an external service to do the transformation, for example, Azure function that reads the data in format #1 and writes it as #2.

As a side note, with option #2, you lose the "id" property.