Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Hello,
I'm trying to fold array multiple json columns look like this :
latitude | longitude | time |
["0.2564","-0.5698","1.3256"] | ["3.0254","0.3214","2.0326"] | ["10h30","10h45","10h50"] |
I want to have this table:
latitude | longitude | time |
0.2564 | 3.0254 | 10h30 |
-0.5698 | 0.3214 | 10h45 |
1.3256 | 2.0326 | 10h50 |
Can someone help me please? I have triyed "fold array" method but the results are not what i want.
Thanks for your assistance
Lottie
Hi, @cbimou! Can you provide any further details on the thread to assist users in helping you find a solution (insert examples like DSS version etc.) Also, can you let us know if youโve tried any fixes already?This should lead to a quicker response from the community.
Hi Lottie,
I think the key here is that this manipulation is more straightforward based on a row-by-row JSON object that contains the latitude, longitude and time for each โrecordโ.
For example, if your data is in the format with rows that look like this:
{โlatitudeโ:โ0.2564โ,โtimeโ:โ10h30โ,โlongitudeโ:โ3.0254โ} {โlatitudeโ:โ-0.5698โ,โtimeโ:โ10h45โ,โlongitudeโ:โ0.3214โ} {โlatitudeโ:โ1.3256โ,โtimeโ:โ10h50โ,โlongitudeโ:โ2.0326โ}
Then you can apply the unnest processor to split out individual columns for latitude, longitude and time. Hereโs an example of what this would look like:โ
If possible, I would suggest trying to transform your incoming data to match the format of the first column shown, so that you can easily add an โunnestโ processor step and convert the data accordingly.
While I think this is most cleanly handled upon data ingest or a brief Python recipe, here is an example of how I transformed data in the original format into the above format with processor steps:
if(zipped[0] == '[', substring(zipped, 1), zipped)
if(zipped[length(zipped) -1] == ']', substring(zipped, 0, length(zipped) -1 ), zipped)
Thanks,
Sarina