Forum Discussion

adam_gataev's avatar
adam_gataev
New Contributor II
2 years ago
Solved

Creating complex nested JSON from CSV file

I am reading a CSV/XLSX file (could be an sql table as well) with the follwing structure (file attached below as well):

BranchYearTypeParentNumberCurrentNumberStructureLevelLanguage1Language1_ValueLanguage2Language2_ValueCreationDateLastUpdate
12342024AAANULL12342024010000011NLvoornaamENfirst name05/06/202405/06/2024
12342024AAA123420240100001234202401010001.012NLachternaamENlast name05/06/202405/06/2024
12342024AAANULL12342024020000021NLgeboortedatumENbirthday05/06/202405/06/2024
12342024AAANULL12342024030000031NLgeslachtENgender05/06/202405/06/2024
12342024AAA123420240300001234202403010003.012NLopslaanENsave05/06/202405/06/2024
12342024AAA123420240301001234202403010103.01.013NLversturenENsend05/06/202405/06/2024
12342024AAA123420240301001234202403010203.01.023NLsluitenENclose05/06/202405/06/2024

I want to transform this table (could be thousands of rows) to the following JSON structure(file attached below as well):

{
"Branch": 1234,
"Year": 2024,
"Type": "AAA",
"MaxLevel": 3,
"Level1": [
{
"ParentNumber": "NULL",
"CurrentNumber": 12342024010000,
"Structure": "01",
"Level": 1,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "voornaam"
},
{
"Language2": "EN",
"Language2_Value": "first name"
}
],
"LastUpdate": "2024-05-06",
"Level2": [
{
"ParentNumber": 12342024010000,
"CurrentNumber": 12342024010100,
"Structure": "01.01",
"Level": 2,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "achternaam"
},
{
"Language2": "EN",
"Language2_Value": "last name"
}
],
"LastUpdate": "2024-05-06"
}
]
},
{
"ParentNumber": "NULL",
"CurrentNumber": 12342024020000,
"Structure": "02",
"Level": 1,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "geboortedatum"
},
{
"Language2": "EN",
"Language2_Value": "birthday"
}
],
"LastUpdate": "2024-05-06"
},
{
"ParentNumber": "NULL",
"CurrentNumber": 12342024030000,
"Structure": "03",
"Level": 1,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "geslacht"
},
{
"Language2": "EN",
"Language2_Value": "gender"
}
],
"LastUpdate": "2024-05-06",
"Level2": [
{
"ParentNumber": 12342024030000,
"CurrentNumber": 12342024030100,
"Structure": "03.01",
"Level": 2,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "opslaan"
},
{
"Language2": "EN",
"Language2_Value": "save"
}
],
"LastUpdate": "2024-05-06",
"Level3": [
{
"ParentNumber": 12342024030100,
"CurrentNumber": 12342024030101,
"Structure": "03.01.01",
"Level": 3,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "versturen"
},
{
"Language2": "EN",
"Language2_Value": "send"
}
],
"LastUpdate": "2024-05-06"
},
{
"ParentNumber": 12342024030100,
"CurrentNumber": 12342024030102,
"Structure": "03.01.02",
"Level": 3,
"Translations": [
{
"Language1": "NL",
"Language1_Value": "sluiten"
},
{
"Language2": "EN",
"Language2_Value": "close"
}
],
"LastUpdate": "2024-05-06"
}
]
}
]
}
],
"CreationDate": "2024-05-06",
"LastUpdate": "2024-05-06"
}

Is this even possible in the first place?

Can someone help me out?

Thank you!

  • Aleksandar_A's avatar
    Aleksandar_A
    3 years ago

    Hello @negi_sagar,

    You can try by using a Mapper Snap with Pass-through enabled:

    For Choices try using the below expression:

    $Choices.extend({[Math.max(...$Choices.keys().map(x => parseInt(x))).toString()]:$Choices.get(Math.max(...$Choices.keys().map(x => parseInt(x))).toString()).extend({"TextEntry":"true"})})

    Sample pipeline:
    SL-comm-add-field-to-max-in-json_2023_03_20.slp (4.4 KB)

    Let me know if this helps you.

    BR,
    Aleksandar.

  • negi_sagar's avatar
    negi_sagar
    3 years ago

    Thank you Aleksandar for answering this quickly and it worked.

    Can you tell me some resources from where I learn to create these expressions?

6 Replies

  • djsiegel's avatar
    djsiegel
    Former Employee
    3 years ago

    Hi Mohit!
    What you might try is passing that JSON over to a mapper, and using the mapper to do what you want.
    Turn off pass through in the mapper and use an expression language statement to convert everything to a string like this:

    $.mapValues(x=>x == null ? "" : x.toString())

    This will essentially set everything to a string, even an empty string.
    Here’s what my mapper looks like to do this:

    Hope that gets you closer!
    – David

    • mohit3's avatar
      mohit3
      New Contributor
      3 years ago

      @djsiegel I am not using the pass through. I am just mapping the fields in mapper snap and then sending it to Json formatter snap to generate the Json but it is only showing single quotation mark for empty values.

      I tried your expression as well but it is also not working.

  • ptaylor's avatar
    ptaylor
    Employee
    3 years ago

    Mohit, this is just an odd bug in the Designer’s viewer for JSON preview data. If you actually save the output of the JSON Formatter to a file using a File Writer, you’ll see that the empty string is a pair of double quotes as you expect.

    • djsiegel's avatar
      djsiegel
      Former Employee
      3 years ago

      Can confirm, Patrick - I happened to just be building an example to test myself.
      When I download it, I do see the doublequotes.
      Mohit - here’s my example, with both the input and output. Hope that helps.

      CommunityExampleJSON.zip (4.3 KB)

      • ptaylor's avatar
        ptaylor
        Employee
        3 years ago

        David, I downloaded and validated your pipeline. Even with the Mapper, the preview dialog for the JSON Formatter’s output shows the same problem with displaying a single double quote instead of two double quotes:

        Your Mapper doesn’t change these values since they are already strings.