I have a use case where I want to convert json stored in a table to be expanded as columns. Now the issue is that Json contains object + arrays + nested array e.g.
{prop1: "Hi", prop2Arr: [{aP1: "Bye", aP2: "Bye2", aP3: "Bye3" }, {aP1: "Bye", aP2: "Bye2", aP3: "Bye3" }], prop3: "", prop4Arr: []}
above is just a sample; actual json is longer; I'm finding a way to dynamically extract the keys and bring them into results as columns along with other Non-json columns
| Non-Json-Col | Prop1 | Prop2Arr_0_aP1 | Prop2Arr_0_aP2 | Prop2Arr_0_aP3 | Prop2Arr_1_aP1 |
|---|---|---|---|---|---|
| Cell 1 | Cell 2 | Bye | Bye 2 | Bye 3 |
That array length is not fixed. It can be 0, 1 to nth.
I tried OPENJSON, but I have to define the json properties manually, and the array is not being handled the way I mentioned above.
Looking for someone with a similar use case and how did you resolve or, I will say, how that can be achievable?
I was thinking of generating the SQL statement via stored proc and executing it dynamically. Still, I cannot find a way to extract keys, handle the array, and make them flatten.
