Json string to items

Questions
1

Hello all,

Through a soap API call, I am requesting an XML file that I have managed to turn into a json string, and that I would like to split into separate items. Now I need a field key name being one of the values.

The string looks like this:

[
  {
    "propertyName": {
      "BREAKFAST": {
        "DESCRIPTION": "Breakfast",
        "NETTTOTAL": "410.5900",
        "TAXTOTAL": "36.9100",
        "GROSSTOTAL": "447.5000"
      },
      "GOLF_BEER": {
        "DESCRIPTION": "Golf Beer",
        "NETTTOTAL": "142.1100",
        "TAXTOTAL": "12.7900",
        "GROSSTOTAL": "154.9000"
      },
      "GOLF_CT": {
        "DESCRIPTION": "Golf Coffee/Tea",
        "NETTTOTAL": "368.3300",
        "TAXTOTAL": "33.0700",
        "GROSSTOTAL": "401.4000"
      },
      "GOLF_FOOD": {
        "DESCRIPTION": "Golf Food",
        "NETTTOTAL": "392.6500",
        "TAXTOTAL": "35.3500",
        "GROSSTOTAL": "428.0000"
      },
      "GOLF_LIQ": {
        "DESCRIPTION": "Golf Liquor",
        "NETTTOTAL": "0.0000",
        "TAXTOTAL": "0.0000",
        "GROSSTOTAL": "0.0000"
      },
      "GOLF_SODA": {
        "DESCRIPTION": "Golf Soda",
        "NETTTOTAL": "112.8700",
        "TAXTOTAL": "10.1300",
        "GROSSTOTAL": "123.0000"
      },
      "GOLF_WINE": {
        "DESCRIPTION": "Golf Wine",
        "NETTTOTAL": "127.3900",
        "TAXTOTAL": "26.7100",
        "GROSSTOTAL": "154.1000"
      }
    }
  }
]

I would like to turn this into separate items, that would look like this:
GOLF_BEER | Golf Beer | 142.1100 | 12.7900 | 154.9000
GOLF_CT | Golf Coffee/Tea | 368.3300 | 33.0700 | 401.4000

(these would be records 2 and 3 from the example string above). So 5 fields in each record.

I think I would need a function node to achieve something like this, but I wish I was that good with coding :wink:

Would there be someone able to help me out?

Thanks! - Maarten

2

Hi @maartend

You can also do it with 2 set nodes.
Added both options below. I assumed you had an item per string. If not you also need another split infront with \n

3

Hi Bram,

thanks for your reaction. If I read the function node correctly, this would change the data:

“GOLF_BEER | Golf Beer | 142.1100 | 12.7900 | 154.9000”

into this format:

“GOLF_BEER”: {
“DESCRIPTION”: “Golf Beer”,
“NETTTOTAL”: “142.1100”,
“TAXTOTAL”: “12.7900”,
“GROSSTOTAL”: “154.9000”

Is that correct? I actually need to go the other way. So this last format is my input, and I would need to change that into a table format with individual records, and 5 fields so that I can then load it into a database.

Would you have some magic code for that too?

Thanks! - Maarten

4

Happy to do a quick call for some more explanation if needed :wink:

5

sure, invite me and I will join in a bit :wink:

6

HI @maartend

Don’t forget to set it as solved. :wink:

1 Like
7

Thanks for helping me out @BramKn , great work! :+1:

1 Like