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): Branch Year Type ParentNumber CurrentNumber Structure Level Language...
alex_panganiban's avatar
alex_panganiban
Contributor
5 years ago

@mbowen Thanks for your response and it makes me feel very hopeful. I just submitted a support ticket. My ticket number is #41732. I have my fingers crossed and remain hopeful.

FYI, in my sample, the input document contains a field called “additional_romance_copy” for sku 228407. The value for this field should get placed in an output field called, “long-description,” in product-id 11110525. This is where the final results will be. In my experiments, I modified additional_romance_copy so that it contained a variety of special characters that I know my vendors use. I outlined the top 18 of them earlier in this thread. Hope this info helps.

Thanks again.

alchemiz's avatar
alchemiz
Contributor III
5 years ago

I tried to set the DOCTYPE to HTML and include the xhtml strict dtd but getting an error where DOCTYPE is not allowed XML generator snap

So, I tried to validate/parse the XML from an online tool… just something to explore

Without the DOCTYPE, the XML is invalid due to reg is not declared

Now adding the DOCTYPE, the reg and copy are now valid since it was declared from the DTD

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

    Hi koryknick,

    This is absolutely amazing! Thank you so much! I never would have figured this out on my own.

    Your explanation is super helpful. I'll dive into all the links you shared.

    Cheers!

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

    Hi koryknick,

    It got a bit more complicated. The SQL database contains different Branch, Year and Type values. The user has the option to send the Branch, Year and Type value as parameters to filter the database. Branch and Year are optional, but the Type is mandatory and has to be given by the user in the parameter in all cases. If the user decides to only give the the Type for example, I have to return all Branch and Year combination JSON objects that have that Type value. For example, the user sends parameters Type="AAA", then this could be the result:

    {
  {
    "Branch": 123,
    "Year": 2023,
    "Type": "AAA",
    ...
    "Level1": [...],
    "CreationDate": "2024-05-06",
    "LastUpdate": "2024-06-17"
  },
  {
    "Branch": 9,
    "Year": 2023,
    "Type": "AAA",
    ...
    "Level1": [...],
    "CreationDate": "2024-05-06",
    "LastUpdate": "2024-06-17"
  },
  {
    "Branch": 1,
    "Year": 2024,
    "Type": "AAA",
    ...
    "Level1": [...],
    "CreationDate": "2024-05-06",
    "LastUpdate": "2024-06-17"
  },
  {
    "Branch": 7,
    "Year": 2017,
    "Type": "AAA",
    ...
    "Level1": [...],
    "CreationDate": "2024-05-06",
    "LastUpdate": "2024-06-17"
  },
  ...
}

    Same logic in case I get the other parameters as well. I basically have to filter them first and then for every Branch, Year combination, do the library expression mapping like before.

    Any idea what I should add or change to achieve this? I was thinking of using a child pipeline, but how would I then give all the rows to the child pipeline...

    Thank you so much in advance!