Query JSON with SQL, Select Distinct

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}}


[ "Apple", "Apple", "Apple", "Orange", "Pear", "Apple" ]

How do I return distinct when querying JSON with SQL?

You could just run the distinct query on the first call like so: the table name I set up is "fruit"
Screenshot 2023-01-04 at 7.56.40 AM

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 :grimacing:
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