Im trying to reduce the number of requests to my sql server so im using Query JSON with SQL in a test app.
Query 1: SELECT * from dbo.database
This contains the below table
ID | Type
1 | Apple
2 | Apple
3 | Apple
4 | Orange
5 | Pear
6 | Apple
Query 2 - SELECT DISTINCT {{query1.data.Type}}
$0
[ "Apple", "Apple", "Apple", "Orange", "Pear", "Apple" ]
How do I return distinct when querying JSON with SQL?
ScottR
January 4, 2023, 1:02pm
2
You could just run the distinct query on the first call like so: the table name I set up is "fruit"
or you can use Query with JSON and do the following:
select DISTINCT type from {{formatDataAsArray(query47.data)}}
I would suggest using something other than type.... use name
1 Like
Hi rcanpolat!
Looks like select distinct works fine in Query JSON with SQL;
Can you confirm if your data model is similar? Array of objects?
1 Like
select DISTINCT type from {{formatDataAsArray(query47.data)}}
worked perfect, thank you
Wait, that's what you're using it for? This is quite inefficient
I'd recommend you use a transformer, with a map to remove duplicates - such as lodash's built-in uniq
(ref ).
Could you show me an example? My JS skills aren't too hot beyond if/else
Add a transformer underneath your initial query (query47
) such as;
Replacing
fruit
with the data column name you want returned :-).
1 Like
Ok I'll give it a go, thanks mate
1 Like