cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wiredup
Helper I
Helper I

Recommendations for parsing this JSON data?

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",

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

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

convertToJSO.png

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 😘

 

 

View solution in original post

14 REPLIES 14
DamoBird365
Super User
Super User

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

@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.

 

wiredup_0-1615728576610.png

 

{
  "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"
}

 

 

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

 

I tried to access via Compose using:

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

 

drfixer_0-1615764831225.png

 

drfixer_1-1615765006864.png

 

 

DamoBird365
Super User
Super User

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.

 

split.PNG

 

Damien

DamoBird365
Super User
Super User

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

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
Super User
Super User

@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
Super User
Super User

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.

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

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
Super User
Super User

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

convertToJSO.png

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 😘

 

 

View solution in original post

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

Hi @drfixer 

 

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

 

Capture.PNG

 

replace(replace(replace(concat('{"',substring(outputs('Compose'),0,lastIndexOf(outputs('Compose'),';')),'}'),':','":'),';',','),decodeUriComponent('%0A'),'"')
 
Straight into parse JSON.....
 
Capture.PNG
 
Which gives you the following output:
Capture.PNG
 

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

 
 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,075)