How to select and format results returned in a JSON?

Hello!

I'm discovering Retool with a first app where I'm trying to return rows in a table based on a JSON file.
I've managed to create a RESTquery I've called answers to get a JSON file containing a list of answers to a thread posted in my Basecamp.

The JSON file looks like this (short version):

{
"id": 12345,
"content": "<div>this my answer</div>",
"creator":{ "name":"Jeremy", "id":67890, "role":"admin" }
}

How can I push in my table only the answers that have creator.name = "Jeremy"?

For now, I've filled my app's Data input with {{answers.data}} and it returns every answer.
I've tried for the last hour to build a new query of type "Query JSON with SQL" but i can't figure out how to access creator.name.

Then, I would like to display only one row per page. Is this possible?

Sorry for these noob questions but I have to start somewhere :slight_smile:
Thanks!

Hello @jeremy and welcome to the community! These are totally normal questions, don't feel too bad :slight_smile:

For your filter question, there are a couple of ways to do this - the easiest for you is probably going to indeed be querying JSON with SQL. We use an open source package called AlaSQL to power this – and the syntax to reference a nested field is actually -> not .. So if you wanted to filter for records where creator.name = "Jeremy" you'd probably want to try something like WHERE creator->name = 'Jeremy'. Let me know if this works!

For your pagination question - what do you mean by display one row per page? Per table page?

1 Like

Hello @justin thanks for your message, it works like a charm.
I knew it had to do with syntax but I couldn’t figure it out. Awesome!

For the pagination question, yes I meant one row per table page.
TBH, using a table was the only idea I had to be able to print out a set of infos from a JSON file. Maybe, there’s better component to do that, all I need out of my JSON is 3 properties (creator.name, content & id).

Last question while I’m at it (not sure I understand the mechanics of Retool here): does the RESTquery is automatically executed when a new answer is posted in my Basecamp? Maybe there’s a detail I still ignore in the configuration of my app that will be important soon or later.

Thanks again for your help!

Great! I've had to clarify that syntax to like 3 people over the past week so you're not alone :slight_smile:

Re: table – you're definitely going to have an easier time with a few text components. If you want it to look real slick, you can put them in a container component and give the container a title. In those text components, you can reference something like {{ query.data.name }} or whatever the format ends up being.

Re: Basecamp - Retool doesn't work natively with webhooks right now, so systems outside of Retool can't programmatically make things happen inside of Retool (with a few exceptions). I'm not sure exactly what your setup looks like, but feel free to chat with Support via the Intercom widget for more questions!

1 Like

You're right @justin, using containers & texts components is the right move.
I've used buttons to run my queries, it works great. I've found a couple of helpful docs, it's getting better & better :blush:

For the table and my idea to display one row per table page, it seems to be impossible as you can't configure pageSize (see this post). My inputs varies too much and playing with the table height doesn't do the trick.

Anyway, thanks again for your help @justin !

This would be an awesome feature. It would allow me to stop using Zapier and integrate notification-based APIs (like ThriveCart).

@nacho could you share examples of how you use Zapier with Retool?
I’m a heavy Zapier user but I didn’t plug it yet.

Hi Jeremy,

Zapier lets me use webhooks, but I would prefer to do this with Retool if they make that feature available.

I’m currently not using Zapier with Retool, unfortunately.

OK I see @nacho , I thought you were using RESTquery in Retool to POST a request to your Zapier webhook to do some actions over there.

I’m not sure if that’s possible, I’m actually interested in the reverse: get a webhook (from another provider or from Zapier) to trigger an action in Retool.

You might want to investigate their docs. It might be possible to build an integration for your Retool app that Zapier might receive data from.

I’m using RESTquery to send POST & GET requests to Basecamp & Google Calendar APIs and they both work like a charm. I don’t see why Zapier’s webhooks won’t trigger, I use them often and they’re super easy to handle.

For anyone interested in working with webhooks and scheduled tasks, Autocode is an excellent and much more powerful replacement for Zapier.

Hey @jeremy - is there any chance you could screenshot (with some things blurred out) the resource details for the Google Calendar API? I have been really struggling to get Google Cal and Google Drive working as an API.

Hi @RCarlson3232! I can try to help as well! :wave:

Are you running into a specific error?

Hey Tess - I'm actually just newer to the API scene and haven't been able to connect it at all. So I was hoping to see the resource details of one that was working. There hasn't been a "specific" error.

Got it! This community post may be more helpful: http://community.retool.com/t/how-to-connect-to-google-apis/1838

We also have docs on rest apis here: https://docs.retool.com/docs/api-authentication#oauth-20

Let us know what questions come up!