azure - 如何根据 kusto 查询(KQL)语言中的命名键从 Json 中获取值

Question

我在日志分析工作区中有一个 json 字段，结构如下所示

{
"AdditionalDetails": [
    {
        "value": "SomeValue",
        "key": "SomeKey"
    },
    {
        "value": "SomeValue",
        "key": "SomeKey"
    },
    {
        "value": "somevalue",
        "key": "somekey"
    },
    {
        "value": "SomeTicketNumber",
        "key": "TicketNumber"
    },
    {
        "value": "1/1/0001 6:00:00 AM",
        "key": "ExpirationTime"
    }
]

}我正在使用 Kusto 查询根据键值票号过滤此数据。删除所有其他列后，我就找到了值中捕获的实际票号。

我尝试过 mvexpand、mv-expand，得到如下所示的结果。

print d = dynamic ({
    "AdditionalDetails": [
        {
            "value": "SomeValue",
            "key": "SomeKey"
        },
        {
            "value": "SomeValue",
            "key": "SomeKey"
        },
        {
            "value": "somevalue",
            "key": "somekey"
        },
        {
            "value": "SomeTicketNumber",
            "key": "TicketNumber"
        },
        {
            "value": "1/1/0001 6:00:00 AM",
            "key": "ExpirationTime"
        }
    ]
})
| project details = d.['AdditionalDetails'] 
| mvexpand details
| project  ticketnumber = details

输出

{"value":"SomeValue","key":"SomeKey"}
{"value":"SomeValue","key":"SomeKey"}
{"value":"somevalue","key":"somekey"}
{"value":"SomeTicketNumber","key":"TicketNumber"}
{"value":"1/1/0001 6:00:00 AM","key":"ExpirationTime"}

要求是仅获取键名称为票号的行，一旦我拥有该行，我应该能够将票号投影为列，有什么建议吗？

注意:我能够根据索引获取票据的值，但 Json 结构是动态的，因此我无法对索引进行硬编码。

Answer 1

您可以使用mv-apply:https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mv-applyoperator

datatable(d:dynamic)
[
    dynamic({
        "AdditionalDetails":[
            {"value":"SomeValue","key":"SomeKey"},
            {"value":"SomeValue","key":"SomeKey"},
            {"value":"somevalue","key":"somekey"},
            {"value":"SomeTicketNumber","key":"TicketNumber"},
            {"value":"2/2/0002 7:00:00 AM","key":"ExpirationTime"}
        ]
    }),
    dynamic({
        "AdditionalDetails":[
            {"value":"AnotherTicketNumber","key":"TicketNumber"},
            {"value":"SomeValue","key":"SomeKey"},
            {"value":"1/1/0001 6:00:00 AM","key":"ExpirationTime"},
            {"value":"SomeValue","key":"SomeKey"},
            {"value":"somevalue","key":"somekey"}
        ]
    }),
]
| mv-apply ad = d.AdditionalDetails on (
    where ad.key == "TicketNumber"
    | project value = tostring(ad.value)
)
| project value