Query JSON with SQL on API data returns no results

Hi guys,

I've got an API setup which is correctly pulling in information (. However, when I run a Query JSON with SQL script, it returns no results. Any ideas on how I can get this working?

Script:
SELECT *

FROM
{{ formatDataAsObject([table_mozenda_data.data]['0']) }}

WHERE
{{ formatDataAsObject([table_mozenda_data.data]['0'])['Product Code'] }} iLIKE {{textInput1.value}};

Screenshot:

Hey dhretool,

Instead of {{ formatDataAsObject([table_mozenda_data.data]['0'])['Product Code'] }} you can use key of the column you are searching by.
Example code:

SELECT *
FROM {{table1.data}}
WHERE product_code={{numberInput1.value}}

Hope this example helps!

1 Like

Thanks, Stefan! Hope this helps, @dhretool. Let us know if any questions come up

There's also additional examples in our docs; the Query JSON with SQL resource accepts an array of JSON objects in its FROM clause.

Thanks Stefan. I did actually try that - but it doesn't work. The idea behind the search box (or possibly a select box), is that a user will be able to type in the Product Code and the table will update based on the results. Have done this a few times before in other Retool apps using SQL data and it works fine. Just can't seem to nail it down with the API data though. The other option would be to use the table filter and search for Product Code that way, but it's a bit clunky in terms of user experience.

Note: the code used to get the table to display the data is as follows (whether or not I need to alter this as well, not sure). The few things I have tried results in no data in the table:

SELECT *
FROM
{{ mozenda_api_view_css_retool_items.data.Item }};

Sample of the API returned data (JSON):

{
"PageItemCount": 10,
"PageNumber": 1,
"PageCount": 1162,
"Item": [
{
"ItemID": 128917,
"Store": "Smiths City",
"Product Code": "KFN96AXEAA",
"Product Code Alternate": "KFN96AXEAA",
"Product SKU": "9074736",
"Price": "4,699.00",
"Product URL": "Bosch 605L Series 6 French Door Bottom Mount Fridge Freezer - KFN96AXEAA",
"Refreshed": "2023-04-05 16:29:46"
},
{
"ItemID": 128918,
"Store": "Smiths City",
"Product Code": "GFV706BSLCASB",
"Product Code Alternate": "GFV706BSLC",
"Product SKU": "9074670",
"Price": "7,999.00",
"Product URL": "LG 637L French Door Fridge - Black Stainless Finish - GF-V706BSLC",
"Refreshed": "2023-04-05 16:29:46"
}

Thanks again.

Considering you are using new table component - maybe this approach would work with no code.

The new Table component has an inbuilt search feature which would filter the table without any new code.

Well now how did I miss that?! Thanks Stefan - that certainly makes things a lot easier! Points to the Retool team for that. Just one query though: is it possible to use a select box instead of text box, so that search results are displayed as they're being typed? Thanks again.

2 Likes

Certainly I don't see a reason why it wouldn't work if search value matches.

1 Like