Master JSON in Bubble ⚙️🧠 The ninja trick that supercharges your integrations 🚀

Have you ever wondered why some businesses seem to be able to connect with the entire industry while everyone else is stuck struggling with the easiest of integrations? Here's the dirty secret that most bubble developers don't know about. Being a good neighbor with the other applications in your ecosystem. I'm Dennis Lewis from Ratio.dev and after 40 years in tech from IBM secret aix team to helping hundreds of startups scale with no code, I can tell you that mastering JSON manipulation is your biggest competitive advantages. Hiding in plain sight when it comes to integrating your application with other applications. Today, I'm pulling back the curtain on advanced ninja tricks with JSON in your bubble application that 99% of developers just completely ignore. And if you stick around until the end, I'll show you how to get free access to our no Code Resource center, the same toolkit we use with our clients to build massive applications. Let me paint you a picture that's probably way too familiar. You've got your Bubble application connected to external APIs, maybe QuickBooks or Stripe or some AI service. The data comes back as this massive JSON blog. And you're thinking, now what do I do? You start creating dozens of database fields, trying to map everything manually. Your workflows become these bloated, complicated messes. Trying to hack your way around this kind of funky data structure that you've got. And every time the API changes slightly, it's back to the drawing board. It's a pain in the ass. It's just a real problem. Sound too familiar? That's exactly where I was three or four years ago when we were building our first Web3 application using Bubble. Picture this. We had this incredible blockchain integration that was returning complex JSON structures with nested objects and arrays of transactions and metadata. The whole shebang. And our first approach was to try and create a database field for every single piece of data and put it into Bubble's database. And that was just a big mistake. We ended up with database tables that had 40 plus fields, queries that would take forever, all sorts of weird workflows. Funky stuff trying to build a house of cards in a thunderstorm. It just didn't work. And then we discovered something that really changed everything, and I mean everything. And it's about how we handle JSON in our bubble applications. The secret weapon, the JSON Assistant plugin by no Code Artisan combined with jsonata, which is basically a programming language specifically for manipulating JSON. Instead of trying to map every piece of JSON data into a separate database field you can store the entire JSON response in a single text field in your double database. And then you can use jsonata to extract, filter, transform, manipulate that data on the fly. It's kind of like having a Swiss army knife for JSON data. And I'm going to show you exactly how to use it. So in the next few minutes, you're going to discover how to store complex API responses without creating dozens of database fields. You're going to find out about the JSON auto functions that will save you hours of workflow complexity, a real workflow example using QuickBooks financial reports, and the backend workflow patterns that make this bulletproof. So let's dig into this. First, let's talk about the fundamental shift in thinking when you get a JSON response from an API. Stop thinking, how do I break this apart and think about how do I keep this together? And here's what we do. Almost always we create a single text field in your database called API Response, JSON data, or whatever makes sense in your use case, and you store the entire API response there. Now you have the complete data set preserved exactly as it came back from the source. But here's where it gets interesting, and this is something I learned the hard way during my IBM years, is when we were working on transaction processing systems. You want to create what I call a dual storage approach. Store the raw JSON data, yes, but also create a few key extracted fields for the data you access most frequently. And this gives you the speed of direct database queries when you need it, but the flexibility of having the full JSON available whenever something gets a little bit more complicated. This is where the ninja tricks come in. Think of jsonada as SQL for JSON. You can query, filter, transform, aggregate JSON data with simple expressions. First, I'm going to show you a report that we've built that drinks in information from QuickBooks. And I'll tell you, I've got another video coming out soon about QuickBooks integration because it's not the easiest one to do, but it happens to bring back a whole lot of data. If you want profit and loss information, that's one report. If you want balance sheet information, that's another report. If you want to pull in just journal entries from the accounting, that's a different report. So there's a lot going on in the background. Let me show you here on the screen, just a quick one of these API calls. This is the balance sheet report. It uses the realm ID. That's like the company ID for QuickBooks. You pass it through this and it will return an entire structure for the balance sheet report similar to profit and loss. They all work in a similar way. And so I'm going to go in and show you the first jsonata for the balance sheet data. We'll see in a minute. I'll walk you through the workflow that actually populates this data into my database. But the first thing I have is I have a JSON that just takes in from the database where I've stored the entire response for that specific API call right into a text field in bubble. And now I can start by reading from that and using jsonada to break it down here on the left hand side you're seeing the humongous JSON structure that QuickBooks has sent me. So it's got a header up here, it defines the different columns in this report. So in this case it's just an account name and a total and then it has rows and all of these rows can have multiple levels deep of grouping, they could be aggregated and you may not know how many groups you're going to get back. You have to open your head to this isn't a clean sort of a balance sheet response. This is just a generic report response. And you've got to figure out how to get the information. And what we've done is up here I have the expression that I use in my JSON which tells it start at the beginning and go down all of the rows, dot row, dot rows, dot row, go to their summary. So if we look over here we can come over here, we can see first rows and then the next level is row and then I'm going to go down a level rows and then I'm going to row here. Okay. And then I'm going to look for the summary of this group. So I'm going to skip this header, I'm going to skip these sub rows and I'm going to go down to the summary and I'm going to grab the first element of column data, its value. I'm going to grab the amount and I'm going to call it level one and I'm going to have JSON on a build this into a more simplified JSON structure. So now I've taken this huge thing and it's broken down all of the first level of the balance sheet, the total assets, the total fixed assets, total liabilities, total equity. So there's only four in that case. Now I can come back into the bubble editor and here you can see this is exactly what I've put in for level one, and I have another one for level two. In fact, if I take this, can I just copy it and we go back to our little JSON playground. Let's paste that in. And now this is level two. You can see now it's bringing me in level two. I'm putting in level two here and I'm going in one level deeper. So instead of rows, row, rows, row, dot summary, I've got an extra rows, row in there. So now I'm going down a level into the nesting. And now it's brought in the sub levels, total bank accounts, total accounts receivable, with their amounts. And basically I can do this for each level that I'm interested in in my balance sheet and bring these into a much simpler structure. One of the cool things is that this adapts itself well. So little changes to the API probably aren't going to cause this to break, but we can do something even better. Here I go back into my definition for my plugins, my API connector. Let's look at this one called JSON. Okay? Just pure off JSON. And in here I've created a dummy API call, has no URL, doesn't matter. Okay, I just called it account pair, but what I did was clicked on manually enter API response and I gave it this structure. So count, amount, level, type. Okay? And now what I can do is when I go into my definition of my JSON here, I can tell it to use that structure and to automatically convert that JSON that it's extracted out of that huge JSON structure and to put it into this standardized data type that bubble understands. So now I can do little things like just filter it and pull out a specific data piece, and I can do it in a normal bubble expression. I don't have to be thinking about JSON anymore. Now it's already mapped into a bubble data type that I can use like any other one. So this is a really cool ninja trick that you can do this way and it will make your life so much easier. Now, what I'd like to do first, we've set up the API calls we're getting in the JSON. We're storing the raw JSON response into our bubble database. Then we use this plugin with the JSON out of expressions to parse and pull out the pieces that we need. Then we can convert it on the fly into a bubble data type. You might be thinking, this sounds really complicated. I'm not a developer and here's the thing, this is something that I'VE learned from teaching tons of people over the years. You don't need to be a developer to use these techniques effectively. JSON Auto has a gentler learning curve than you might expect. Start with some simple expressions and work your way up. Some of them can get more complicated, but remember, no code does not mean no work. What comes out the other end is something you can understand, maintain and scale. And that's worth learning how to do this. Let's dig in to our examples a little bit more. This is a profit and loss report. And here I am pulling in now this, this same basic structure. I do this in my report on level one, level two, level three. I pull them all in and because they have this type, I can figure out the hierarchy and actually build repeating groups in bubble that will pull this, show it up in a cool slick way. I'm not here to show you the inner workings of that, but just that you can pull in. And I'm going to show you one more which is a little bit more complicated. In this case, I'm doing a monthly profit loss. Here I'm pulling in columns of amounts for each month and I'm separating them with a colon so that I can parse those into individual months. Here I needed to combine two different levels in one JSON structure. So what I used was something called a pin. I just can take first level, append it to second level and it will just flatten that out into one JSON structure that I can map into those bubble data types and then use however I need. So if I need to sum up all of the ones that are expenses, then it's really easy for me to do that. Let's go back to the bubble editor and let's just look here quickly at the back end workflow that makes this report actually do its work. Just so you can see that.

I'm going to go here to report load data. Well, I'm calling an API call called cash flow report and then I'm taking the response of that and storing it into a field in bubble. And then I'm doing the balance sheet report and I'm doing the profit and loss and I'm doing here the other one, the monthly profit and loss and a general ledger for a different part of this report and an aged receivables and all of these API calls are just mapping their data back into a bubble field that I can later then go and manipulate on the fly and pull out that the information I need in each specific case. One last cool thing I want to show you is that you can use this in any bubble page that you want. You put the elements on the page and you can use them however you want. But you can also do this in a backend workflow. What we're doing is we're pulling in the actual data from the QuickBooks account and we're comparing it to budgeted data. So the provisions, the budget that we had planned, and we're able to do a comparison month by month, account by account. This is how much you expected to spend, and this is how much you actually spent, and this is how much it changed the variance. And when we do this, we can pull in here the monthly information in this back end and then we're pulling in level one, we're pulling in level two, and then I'm just using this JSON to things action which allows me to take from step two, level one, and I'm mapping it into a data type which I created one of these dummy data types, which is Coke with Books, Profit, MLS monthly and number two here as well. And then what I do is I create a backend workflow that takes this information and I schedule it over a list and I pass it the data type list that I've just done. So I take this list from step four, I run this API workflow, does the budget calculations, it's associated to this here. I pass it the header, the account and the columns, which are amounts, and I just let it go one by one over that list. It's really important because if you try to break these down into text lists or something like that, you're going to run into a big obstacle because a bubble list can't have duplicates and it can't have a whole bunch of empty elements in the list. So you wouldn't be able to match them up. But by doing this way, you can just run it over the list and it will create all of the rows or update all of the rows needed for each one of those items that you pulled in from the API call. And so this is a powerful advanced tip that I think will be important for you. Let me tell you what happens when you implement this approach correctly. First, your database becomes cleaner and more maintainable. Instead of having dozens of fields might or might not have data, and you just keep the JSON in there, your workflows become more resilient because these small changes in the API probably are going to affect you back in workflows as much because you still able to extract as long as the information is there. If they've added in new stuff, it won't make any difference. And if they've taken stuff away that isn't critical for what you're doing, then that won't make any difference either. And third, and the big one is you become more agile because you don't have to. From the very beginning, think through every possible data extraction that you might need from this big JSON, because you've got it. You can always go back and create a new workflow to pull something new and do something different with it. We've used this approach to help clients process millions of transactions and handle really complicated marketplace data and integrate from all sorts of different systems. Because in the end, JSON is the language of integration. This is the key structure of data when you're passing between two different applications is through JSON structures. So this is an important thing. So mastering JSON manipulation in bubble isn't just about writing cleaner bubble workflows. It's about building applications that can adapt and scale and be good neighbors with everyone else in your ecosystem. And if you're interested in seeing how this can be done for your business, I want you to use the link down below and get access to our no Code Resource Center. That's where we put all of the plugins that we recommend. That's where we put all of our videos or podcast episodes. You'll find links to a bunch of great books. If you have a written by yours truly, all the secret sauce we put in there and it's absolutely free, we update it every week and all you have to do is go to ratio.dev and sign up or use the link below. And I'm confident that if you're watching this video, there's a ton of great stuff in there for you. Don't let this opportunity pass you by. JSON mastery is what separates the big boys from little boys in Bubble World. And with the techniques I have shared with you today, you'll be building applications that your competitors can't even imagine. Another cool trick is that you can store the JSON in the user's browser and then manipulate it without even touching the bubble database. So if you have a performance issue that you're struggling with, this could be a solution for that as well. Hope you've enjoyed this video. If you have, give it a thumbs up. Subscribe to our channel. You don't know how much that would mean to us. We appreciate it. And if you get stuck, if you're not sure how to use this in your specific use case, or you just don't have the time to figure it all out. Feel free to reach out to us at ratio.dev, we'd be more than happy to help you out, lend your hand and get your application cranking with that complicated JSON structure that you've been banging your head against. Hope you've enjoyed this video. See you in the next one.