Recommendations for parsing this JSON data?

[wiredup]

Dear community--

I am receiving the following JSON data from Jotform.  

Any recommendations for parsing the data--I only need what's between \" and \" ... e.g., L20 and R21

Brand, Model and "Will Supply" are rows

LEFT and RIGHT are columns represented as L and R

Partial JSON:

  "30 - Magneto:": "Brand: [\"L20\",\"R21\"]; \nModel: [\"M20\",\"M21\"]; \nWill Supply (Y/N): [\"N\",\"Y\"]; \n",

[DamoBird365]

Hey @wiredup 

You've got an action called parse json that will let you create dynamic values from your output.  The best way to get this action working is to supply a sample payload, which you can get by going to the history of a previous run and copying the output from the action where you collect that data from.

This might get you dynamic values for your brand and model elements.  

Give it a go and let me know how you get on.

Damien

[wiredup]

@DamoBird365 correct, but I can't access the escaped elements for an item. See #30 and #31 below.  

I've tried a few things I've searched for on PA to no avail.

{
  "04 - Customer Name": "Jason Test",
  "05 - Billing Address": "Street Address: 202 Free Street<br>City: City<br>State: Vermont<br>Zip Code: 23139<br>",
  "08 - Shop Name": "",
  "09 - Shipping Address": "",
  "10 - Phone Number": "",
  "11 - Email Address": "",
  "07 - Email Address": "jason@seibelfamily.com",
  "06 - Phone Number": "(804) 404-3803",
  "14 - Page Break": "\n",
  "25 - Aircraft is:": "Certified",
  "26 - Build From:": "Customer Engine",
  "27 - List any STC's Currently Installed:": "STC 1\nSTC 3.5\nNew STC",
  "28 - Do you already have the NorthPoint STC or do you need to purchase it?": "Need it",
  "29 - Page Break": "\n",
  "31 - Harness:": "This harndess",
  "32 - Spark Plugs": "this spark",
  "33 - Starter:": "Brand: [\"Starter brand\"]; \nModel: [\"starter model\"]; \nVoltage: [\"start voltage\"]; \nWill Supply (Y/N): [\"N\"]; \n",
  "30 - Magneto:": "Brand: [\"L20\",\"R21\"]; \nModel: [\"M20\",\"M21\"]; \nWill Supply (Y/N): [\"N\",\"Y\"]; \n",
  "34 - Oil Cooler:": "Type: [\"Misc Type\"]; \nLength: [\"2\\\"\"]; \n",
  "35 - Oil Cooler Method:": "Non-Decongealing",
  "36 - Oil Filter Type:": "Stock Screen",
  "37 - Oil Pan Type:": "Round",
  "38 - Upload Oil Pan Picture:": "",
  "39 - Engine Mount Type:": "3 point",
  "40 - File Upload": "",
  "41 - Alternator Bracket Type:": "Bolt on Bracket",
  "42 - Tachometer Drive:": "Cable - CW Rotation",
  "43 - Intake Type:": "O-470-A, J, K - Three Piece",
  "45 - Option (select all that apply):": "New Cylinders; Ported Cylinders; 8.5:1 Compression Upgrade\n",
  "44 - Page Break": "\n",
  "46 - Cryo Treatment:": "No",
  "48 - Other Options or Directions:": "Awesome stuff--do good work",
  "49 - Page Break": "\n",
  "50 - Engine Photos - Multiple Angles:": "",
  "13 - Engine Order Date:": "2021-03-13T00:00:00+00:00",
  "12 - Requested Completion Date: (note this is not a commitment; deposit required)": "2021-03-14T00:00:00+00:00",
  "47 - Paint Color Choice:": "Standard Continental Gold",
  "18 - Registration:": "Y2343",
  "15 - Aircraft Make:": "Cessna",
  "16 - Aircraft Model:": "50T",
  "17 - Aircraft Year:": "2020",
  "20 - Old Engine:": "Continental",
  "21 - Spec:": "v6",
  "22 - Old Serial Number:": "9083908",
  "19 - divider67": "\n",
  "24 - divider": "\n",
  "23 - Will you be sending your current engine as a core?": "No",
  "01 - Submission ID": "4914954645823758790",
  "02 - Form ID": "210695699593172",
  "03 - Form Title": "NorthPoint XP470 Order Worksheet"
}

[DamoBird365]

Hi @wiredup 

You would tackle them as follows:

body('Parse_JSON')?['30 - Magneto']?['Brand']
body('Parse_JSON')?['30 - Magneto']?['Model']
body('Parse_JSON')?['30 - Magneto']?['Will Supply (Y/N)']

Cheers,

Damien

[drfixer]

I tried to access via Compose using:

body('Parse_JSON')?['30 - Magneto']?['Brand'] and by adding a ":" after Magneto to no avail

[DamoBird365]

Morning @wiredup 

I see your problem now.  The element of "30 - Magneto;" is a single object as it is encapsulated in "".  So my earlier suggestion will not work, if you use the split function on the results of your JSON like so:

split(variables('123')?['30 - Magneto:'],';')

you will get an object with your different elements (albeit the string might need a tidy up before the split as it will contain \n and \".  You could try a replace on the string prior to the split.

Damien

[DamoBird365]

Hi @wiredup 

How are you getting on?  If you need more help then please let me know.

If my post has helped, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

[drfixer]

Hi @DamoBird365, 

I was looking at this; is there a way to build JSON by dropping the brackets and slashes?

@Paulie78 You seem to have some experience based on previous posts. Any thoughts?

I'm asking myself why escaped JSON is so hard?

[Paulie78]

@drfixer I think I helped you with this before, previous example was easy, because the escaped JSON, was actually JSON. The contents of this are not valid JSON, so you can't take the same approach (but you can still do it).

How many records are there, or just this one?

[Paulie78]

Not sure I explained myself very well, if you look at the contents of 30 - Magento, it is actually:

Brand: ["L20","R21"]; 
Model: ["M20","M21"]; 
Will Supply (Y/N): ["N","Y"]; 

Now you could easily do a bit more with that output to turn it into JSON, but as it stands, it is not JSON.

[DamoBird365]

@Paulie78 would you just use compose with a replace expression to tidy this up first?

[drfixer]

Hi @Paulie78, 

You helped me on another issue but not on this particularly.

The output is from jotform = https://form.jotform.com/210695699593172

"33 - Starter:": "Brand: [\"Starter brand\"]; \nModel: [\"starter model\"]; \nVoltage: [\"start voltage\"]; \nWill Supply (Y/N): [\"N\"]; \n",
  "30 - Magneto:": "Brand: [\"L20\",\"R21\"]; \nModel: [\"M20\",\"M21\"]; \nWill Supply (Y/N): [\"N\",\"Y\"]; \n",
  "34 - Oil Cooler:": "Type: [\"Misc Type\"]; \nLength: [\"2\\\"\"]; \n",

Basically, I need to be able to isolate the responses under each subtype of "30 Magneto:" after each section (e.g., Brand, Model, Will Supply (Y/N).. 

For example,

Brand has 2 answers: L20 and L21

Make sense

[Paulie78 · Accepted solution]

It's not so easy, this is how I did it:

I used the line break within the string to convert it into an array of lines, which makes it into this JSON Array:

[
  "Brand: [\"L20\",\"R21\"]; ",
  "Model: [\"M20\",\"M21\"]; ",
  "Will Supply (Y/N): [\"N\",\"Y\"]; ",
  ""
]

Then there is a compose action in which the contents of the lines are reformed. The expression for brand is:

json(replace(substring(outputs('Split_into_Lines')[0], indexOf(outputs('Split_into_Lines')[0], '[')), ';', ''))

What this does:

1) Takes the string from the first open square bracket [ symbol until the end of the line.

2) Replaces the semi colon ; with nothing.

3) Converts the remaining string to JSON.

So the complete compose looks like this:

{
"Brand": @{json(replace(substring(outputs('Split_into_Lines')[0], indexOf(outputs('Split_into_Lines')[0], '[')), ';', ''))} ,
"Model": @{json(replace(substring(outputs('Split_into_Lines')[1], indexOf(outputs('Split_into_Lines')[1], '[')), ';', ''))},
"Will Supply": @{json(replace(substring(outputs('Split_into_Lines')[2], indexOf(outputs('Split_into_Lines')[2], '[')), ';', ''))}
}

Then you could put the output into a Parse JSON and get your dynamic content. It's not pretty, but it works, the final output is:

{
  "Brand": [
    "L20",
    "R21"
  ],
  "Model": [
    "M20",
    "M21"
  ],
  "Will Supply": [
    "N",
    "Y"
  ]
}

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[drfixer]

Wow - ya, pretty tough. Let me go try this - give me a few hours.  THANK YOU!

[DamoBird365]

Hi @drfixer 

This hurt my brain a bit but a 1 liner.... from this

replace(replace(replace(concat('{"',substring(outputs('Compose'),0,lastIndexOf(outputs('Compose'),';')),'}'),':','":'),';',','),decodeUriComponent('%0A'),'"')

Straight into parse JSON.....

Which gives you the following output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien