Wrangling JSON and Text in Xano

So this is an office hour session where I was guest mentoring with Zanno, and the person who needed help here, Erin, originally described her problem as needing to strip out control characters from her payload because it was causing problems with the JSON parsing that was going back and forth. As we dig deeper into it, you're going to find that we peel back the layers of not needing to deal with as many concerns, and you're going to find there's a broad lesson here of applying simplification as opposed to trying to just dive in directly at the immediate error message that we're looking at. Watch all the way to the end in order to be able to see how this whole thing evolves, and I hope you find it helpful.

I'm pulling in data that's coming through a web form.

Yeah.

Form. I've pasted in some sample data, and in this particular form, I put an extra line break in it. I process that data, save some records, and then kick off a notification via SendGrid. And SendGrid hates the line break. And my method for removing the line break, which is to take out the /n, is apparently not removing the control character. So what is this further control character concept and how can I clean it out of text?

Well, how did it get into there in the first place? Like, at what point are we creating the JSON for all this? Can you tell me where, like, formatted data is coming from?

Yeah, it's coming from Webflow. It's a field on Webflow where people can put in tabs and line breaks if, like, I can't put them.

Let's look at this from the point of view of inputs and outputs. Let's look at, if this is a, if this is an endpoint, let's go back over to, let's start, we'll, we'll start within Xano. We might look at this in Webflow in a second. But if I could get you to close up these two sidebars here, you know, by using the little X in the corner. Yeah, cool. Scroll to the top, please. The very tippy top, very top.

Yep.

There we go. You see that 3 dots in the upper right-hand corner?

Yes.

Let's click that and let's take a look at the request history and let's see what's come in the door from Webflow that's causing this.

Let's, since this is being recorded, let's not use my client's real data. So I will open—

That's fine. Would you like me to stop recording? I can do that.

That works too. I can also, I have, I prepared some data beforehand. This is exactly what's coming in. And the field that's causing the trouble is right here. So you can see there's that.

Well, okay, so that, that so far, that doesn't seem okay. So far this is properly formatted JSON, which is excellent. So now let's scroll on down and can you tell me more about like what SendGrid is saying it doesn't like?

Yes. So what happens when I— well, first of all, to try and take out that control character, I replace my slash n with just an additional space.

We'll need to do something else in a minute. Don't, don't worry about— yes, I, I appreciate that. I appreciate why it didn't work. So we'll, we'll work that problem just a second. I just wanna make sure we go through the whole cycle of seeing how it comes in and what, what message we got coming out to make sure we're fixing the right thing.

Okay, roger that. So then the message is when I get to SendGrid sending it out, sorry, Twilio in this case, but it happens for both Twilio and SendGrid. They don't like the control character.

Well, let's find out if that's actually the case. Let's go to the debugger, please. Oh, you get down to results. Result is the inquiry. Maybe. Let's go. Let's debug. Let's click on debugger and go all the way to the bottom for me. Uh-huh. So it gets Twilio send SMS. Uh-huh. And the input is— what are we trying to send, uh, send into there?

Uh, so what I'm sending into Twilio is, um, the phone number to send it to, the body of the text. This is where the problem is happening, so I create the body of the text in this variable. And JSON decode it.

I see. So then there's a question of why we would— okay, so, so the body is the body that's over there, which includes the JSON or what have you, that, that part's in there.

So what, what would happen ideally is I would strip the incoming stuff of any of its existing control characters and then as necessary could re-encode it because as you can see, I'm sending a lot of data in this. It's a notification to someone that a new lead has come in. So I need to give this different line breaks than is in the original.

Uh-huh. Okay. So basically we're saying is when we do this stuff, so, so the body is just gonna be a string at the end, right?

Correct.

Okay. Let's go to Twilio Send SMS on number 17. It's not obvious to me why, why we're not just putting body directly into, into that item instead of using the, the sprintf with the JSON decode?

Yeah. So I, um, could hypothetically apply the JSON decode right here on the body itself, but I do this in so many places throughout my app that I have just found this to be— if I always do it this way, then if I have additional stuff as well, then I know I just do it throughout the whole entire app.

Okay. So let's, let's go over to the body for a second. Uh, and that, and this is starting to, to gel for me. So we click into this and then, uh, presumably the field name that has the weird thing is somewhere in here?

Yeah, it, it's the, the field name got saved into a record called inquiry as the field intake message.

Okay, so here's my proposal. So the intake, so can we find, can we maybe do before we do the create variable, so before line 16, let's do a stop and debug and take a look at what inquiry.intake_message is right now.

Happy to.

These cleanup type things, I mean, we can throw a couple pieces of spaghetti against the wall for it, but I find they really reward looking, just taking 80% of the time to look at the inputs and the outputs and then 20% of the time on the actual fix of it. So let's, I think you called it inquiry, right?

It's inquiry.intake_message. Do you want to see the whole record that comes out or do you want to see my reformatted intake message I'm putting into that record?

Actually, let's show me line 17 first. Let's, before we do that, let's go back into line 17 and ask what actually is getting fed into line 17 so we can answer that question just a little bit better.

It's using the inquiry record.

Okay, let's use, let's use the inquiry version of it. Yeah. Uh-huh. And then we'll just rerun it over on the right.

We have to repaste.

Forgets things so fast, doesn't it? Okay. So the intake message is a couple of different shades of light pink and white, 12. Okay. So the issue we have here is you have a weird mix of stuff that is encoded and not encoded in your, in your, so let's over on the left-hand side here, let's scroll down to number 17, which is where your, is it 17? Yeah, so 17, 17's a weird animal. Let's look at 17 for a second because 17 is you are trying to create basically JSON encoded text at this point, right? So, and I'm not a huge fan of using sprintf, but the, and partially because it makes it easy to make this kind of slight bit of error. What I think is going on here is that each of these data points you're inserting in are not themselves JSON encoded. JSON encoded, but you're expecting that the result is going to be JSON encoded, right? So that the—

I'm not expecting that anything in this step is fully JSON encoded because although I'm putting in the /n's, I don't have like the quotes around it. So I'm basically pre—

Oh no, it is all JSON encoded. This is a JSON encoded string. So when you use a \n, you're using actually JSON encoding for that job and then you're JSON decoding it subsequent. Does that make sense?

Oh, okay. I follow now. Thank you.

Okay.

Got it.

So, right. So the— so here is— so you have a couple of techniques we can use to work with this. Okay. One is, and I might actually recommend that we do a duplicate of the create variable right now so that you can— so you have a couple of options available to you. So let's take, let's take number 17 and let's clone it and then we'll comment out the clone that way. Yeah, right. Now let's go back into your create variable. So my proposal here is instead of worrying about stripping things out, let's add the encoding so everybody's at the same level because the goal is the final text, the string up here is supposed to be JSON encoded. That's what, 'cause it has the backslash ends to it. So the way, so then what you want is for the stuff inside that you're worried might not be JSON encoded, to itself get JSON encoded. So let's scroll down just a little bit and this becomes cool because now instead of trying to strip out control characters, you're kind of re-encoding them back in. So let's take the query intake message and let's click on that guy for a second and we're gonna add a filter to it. And the filter is the JSON encode, right? That'll take all your control characters or, you know, new lines, whatever, and it will turn them into JSON-friendly format that will then compose into the rest of this correctly.

Let me come and undo my attempt at stripping then. Nope, it's not that one. Where is it? Here we go.

The reason why the stripping didn't work is because it's looking for the literal string backslash n, but the thing inside your intake message is that it's I, I, I think that that's already the, that, that's already been decoded from your JSON, right? So what you really were trying to say is please replace new lines with a blank, which we can do. It's more complicated, but it's also the long way around. This, I think, gets you where you want to go more directly.

Okay. All right. So now my slash n is still in there when it gets saved. Do we know whether it will save it as /n or if it's going to read it as a control character? Or should I run it really?

Let's find out.

Okay.

So do we save this already? Okay. Hopefully we did.

I might not have. I will come back and do that in just a moment. But this is before that step.

So let's just check out number— yeah, let's try that.

Okay. It did save it as a control character. And I'm going to just double check that I did make the change I thought I made. Okay. I saved my original. I came here and I took off that address.

And let's make sure that we have the JSON encode in like line 19 or whatever that is.

Yes. Yeah.

Yeah. All good. Great. Let's comment out the stop and debug and let's see if it goes through to SendGrid correctly.

Oh, sorry, I need to paste it again.

Okay, well, let's take a look at what the JSON is after line 19. So let's do the stop and debug after line 19 to find out what it has to say.

Oh, stop and debug after 19. Okay, moving. I have to move my video over here.

Yeah, right.

And now we're having it return the body.

Body. Right. Exactly. Let's, let's, let's see what this thing looks like at this point.

Oh.

Okay. So the quotation marks that are still in there.

Yes.

And that's probably from a different piece of it, or is that also from the message?

That I don't remember manually adding quotation marks. And I'm also noticing now it has slash and— oh yeah, and it changed my like 10 to 12 foot. The apostrophe got changed to this.

U27. Yep. That's a, that's a nice apostrophe. That's not, that's not terrible.

Okay.

So that's all just like new lines. I wouldn't worry too much about that stuff right in here. So the, the, the, but however, the quotation mark is invalid. So let's take a look at your create variable on line 18.

On 18. Okay.

And there's your intake message. So let's actually, I kind of suspect it's in the primary text. Let's go back up to your primary text. Sorry, in body, right? So let's go back to number 19. 18. I did mean 18. I apologize.

No worries.

Okay. You see where it says text at the top? No, I don't want to look at this guy again. Yeah. Right. I, I think it's, it's in the structure. Yeah. So can we open that guy up and use the expander so we can see more of it? Messages %s. All right. All right. Okay. So the issue is, oh, got it. Now I'm starting to understand. So the issue is that the message, the, the, when we encoded it, it gave it with the quotation marks. So then we just need to trim off the quotation marks in order for that to work properly. So let's go back into number, Let's go back into the message that we're inserting into here. Mm-hmm.

And just do left trim, right trim 1.

Yeah. After doing the JSON encode. Yeah.

And you just—

The quotation mark. The quotation mark. There we go. Yep. That should work for you.

Okay, here we go.

But no, you're good. The trim is left and right.

Oh, that did both sides. I only did L trim.

Oh, is that— oh, you did only do L trim. My apologies. Yes, your intuition was correct, except I think there's one called just trim.

Oh my goodness, you're right. Okay, well, cool. Thank you.

That's what I thought you were doing at first, and then I just got caught up. Okay, let's try, let's try rerun. Yeah, rerun it with the stop and debug. Once again, we gotta clean up the input and—

All right, and I have stop and debug on. Okay.

All right, this looks a lot better. So now try commenting out the stop and debug and shipping it. Santa's quite forgetful. Boom.

Okay. So, encode it, remove the quotation marks. It seems like it would be easier to just take what's coming in and remove control characters, unless removing control characters is kind of a monster.

Removing control characters, you're gonna try to remove everything except for a certain subset of stuff, and that makes life much more messy. What the— my recommendation actually would be like, let's take a look at the body here for a second in this create variable on line 18.

Look at the debugger or—

No, look at the create variable for a second. You're using these backslash ends and you don't gotta. So let's click into the value thing here and let's expand it. So here's my proposal. Backslash over— see where it says backslash n, the first one after new inquiry?

Yeah.

Press Enter. Just make them into newlines. Now you don't have any more of that. Now you don't need to JSON encode or decode anything. You take JSON out of the equation.

Except that what arrived originally had a slash n in it. So did that get just properly handled when I saved it?

Yep. Because it decodes it and encodes it. You're allowing the transformers that come with the system do that job for you. There is no more backslash n at that point. Okay.

Rather than meeting the input how it's coming in and then changing all of it before the output, trust that Xano is handling that for me. Okay. I will hang out on mute for a little bit and try this without the slash n and might hold on to you for just a very moment after everyone else is gone if I hit trouble.

I'd be glad to help.

Okay, awesome. Everyone, thank you so much.

A quick note at the end here. Erin stayed on the call to implement some things we talked about at the very end and reported back through chats that she was able to, you know, get the whole thing working without having to do any of the JSON decoding at all. So let that be a lesson for us all. Simplification for the win.