Trying to create a sentinel query (KQL) which uses the externaldata() operator to ingest the information from the json file 'https://www.gov.uk/bank-holidays.json'. Problems I am finding is due to this json file containing the column / field 'date' sentinel does not allow this as a variable. Anyone been able to get a multilayer json fields from an external file?
externaldata (title:string, date:string, notes:string, bunting:bool)[
@"https://www.gov.uk/bank-holidays.json"
]
with(format="multijson")
['date']
or ["date"]
json
is enough. No need for multijson
.title:string, date:string, notes:string, bunting:bool
)."england-and-wales"
, "scotland"
& "northern-ireland"
.txt
or raw
, parsing it to JSON and then explode it, as demonstrated in the query below.externaldata(doc:string)
[h'https://<storage-account-name>.blob.core.windows.net/mycontainer/bank-holidays.json;<secret>']
with(format='txt')
| project parse_json(doc)
| mv-expand kind=array doc
| project kingdom = tostring(doc[0])
,division = doc[1].division
,events = doc[1].events
| mv-expand events
| evaluate bag_unpack(events)
//| sample 10
kingfom | division | bunting | date | notes | title |
---|---|---|---|---|---|
northern-ireland | northern-ireland | false | 2017-04-14T00:00:00Z | Good Friday | |
england-and-wales | england-and-wales | true | 2017-05-29T00:00:00Z | Spring bank holiday | |
scotland | scotland | false | 2018-03-30T00:00:00Z | Good Friday | |
england-and-wales | england-and-wales | true | 2018-12-25T00:00:00Z | Christmas Day | |
northern-ireland | northern-ireland | false | 2019-04-19T00:00:00Z | Good Friday | |
england-and-wales | england-and-wales | true | 2019-12-25T00:00:00Z | Christmas Day | |
northern-ireland | northern-ireland | true | 2020-01-01T00:00:00Z | New Year’s Day | |
scotland | scotland | true | 2022-01-04T00:00:00Z | Substitute day | 2nd January |
scotland | scotland | false | 2022-09-19T00:00:00Z | Bank Holiday for the State Funeral of Queen Elizabeth II | |
scotland | scotland | true | 2023-01-02T00:00:00Z | Substitute day | New Year’s Day |