Query JSON with SQL

Queries and Resources
1

Hi,

Am struggling with the syntax and structure to filter a SQL Query in Retool and wondered if i could get some assistance

I need to get a count of product_id, where the rank =8
vw_PS_Data = Datatable

SELECT COUNT({{vw_PS_Data .data.Product_ID}}) AS Num FROM {{vw_PS_Data .data.PriceRank}} where {{vw_PS_Data .data.PriceRank="8"}}

Any help would be great, as it just returns the total number or rows in the table.

Tim.

2

@PriceSynergy Welcome to the forum!
Guessing here as I don't see the data output...

SELECT COUNT({{Product_ID}}) AS Num FROM {{vw_PS_Data.data}} where {{PriceRank}} =8

Assuming 8 is an int and not text otherwise add quotes around 8

Screenshots?

3

Sure no problem, and thanks for responding, but i still can't seem to get it to work, appreciate it's also a very simple SQL statement and having written SQL for years am stumped at why.

I retrieve the data from a View in SQL server in to vw_PS_Data, this new query (Query Json with SQL) then tries to count the number of rows that have a rank of 1 for instance, this column is a BigInt, I have attached some screen shots.
Column_Data
Top_Level_Table

4

Hey @PriceSynergy!

I'm curious if something like the following works:

SELECT COUNT(Product_ID) AS Num FROM {{formatDataAsArray(vw_PS_Data.data)}} where PriceRank='8'

You shouldn't need to wrap your column names in {{}} and Query JSON with SQL also expects an array of objects hence the formatDataAsArray helper function (docs).

What do you see when you run that?

5

Thanks, all worked, now i can really crack on :slight_smile:

Tim