Project certain column and carry on one entry in every row

user_13412377 0
Viewed 22

I have a dataset which looks like the following

{
    "metadata":"d_meta_v_1.5.9",
    "data": {
            "a": {
                "T": [
                    1652167964645,
                    1652168781684,
                    1652168781720

                ],
                "V": [
                    1,
                    2,
                    3
                ]
            },
            "b": {
                "T": [
                    1652167961657,
                    1652168781720,
                    1652168781818
                ],
                "V": [
                    1,
                    3,
                    4
                
                ]
            },
            "c": {
                "T": [
                    1652167960194,
                    1652168787377

                ],
                "V": [
                    1,
                    3
                ]
            }
        }
}

I want to select the certain column and carry on the metadata also at the end. a part of this question is working in my perviou question here How can I get my desired output ?

Metadata, Time, a, b
d_meta_v_1.5.9, <Time>, <value of _a>, < value of b>
d_meta_v_1.5.9, <Time>, <value of _a>, < value of b>
d_meta_v_1.5.9, <Time>, <value of _a>, < value of b>
kql azure-data-explorer
1 Answers
let requested_columns = dynamic(["a","b"]);
datatable(doc:dynamic)
[
    dynamic
    (
        {
            "metadata":"d_meta_v_1.5.9",
            "data": {
                    "a": {
                        "T": [
                            1652167964645,
                            1652168781684,
                            1652168781720
                        ],
                        "V": [
                            1,
                            2,
                            3
                        ]
                    },
                    "b": {
                        "T": [
                            1652167961657,
                            1652168781720,
                            1652168781818
                        ],
                        "V": [
                            1,
                            3,
                            4
                        ]
                    },
                    "c": {
                        "T": [
                            1652167960194,
                            1652168787377
                        ],
                        "V": [
                            1,
                            3
                        ]
                    }
                }
        }
    )
]
| project metadata = doc.metadata, data = doc.data
| mv-expand data = data
| extend key = tostring(bag_keys(data)[0])
| where key in (requested_columns)
| mv-expand T = data[key].T to typeof(long), V = data[key].V to typeof(long)
| evaluate pivot(key, take_any(V), metadata, T)
| order by T asc
metadata T a b
d_meta_v_1.5.9 1652167961657 1
d_meta_v_1.5.9 1652167964645 1
d_meta_v_1.5.9 1652168781684 2
d_meta_v_1.5.9 1652168781720 3 3
d_meta_v_1.5.9 1652168781818 4

Fiddle

user_63364790
Related