In my Azure Data Explorer table, I have a JSON string with a list of key value pairs. Each key can be different between different rows. And the value of the key is not known ahead of time (i.e. I don't know what are the possible values).
| id | value |
|---|---|
| 1 | {"270":"2","360":"2","480":"1","540":"1","720":"4"} |
| 2 | {"320":"1","480":"4","1280":"5"} |
Is there a way to mv-expand each key into separate row?
| id | key | value |
|---|---|---|
| 1 | 270 | 2 |
| 1 | 360 | 2 |
| 1 | 480 | 1 |
| 1 | 540 | 1 |
| 1 | 720 | 4 |
| 2 | 320 | 1 |
| 2 | 480 | 4 |
| 2 | 1280 | 5 |
Or find out what are all the possible values of the keys? i.e [270, 320, 360, 480, 540, 720, 1280]