DISTINCT Query Help

SQL++
1

Hi,
I am currently trying to query all of my documents to get a list of Distinct values for a specific field. They will then be displayed in a Combo box for sorting in the future. The Query "SELECT DISTINCT license_type FROM LiquorLocator" works fine as expected when executed on the database panel. When converted to Spring Boot

@Query(“SELECT DISTINCT license_type, META(b).id as _ID, META(b).cas as _CAS FROM #{#n1ql ucket} as b”)

It is returning every documents value. I am guessing that this is due the the fact that it’s looking for all distinct combinations of those three values?

Is there a way around this or is my best bet making a new document that holds all the possible distinct values for license_type?

Thanks

2 
CREATE INDEX ix1 ON LiquorLocator(license_type);
SELECT ARRAY_AGG(DISTINCT license_type) AS license_types, 0 AS _ID, 1 AS _CAS
FROM LiquorLocator
WHERE license_type IS NOT NULL;
3

The results of the this query is this:
[
{
“_CAS”: 1,
“_ID”: 0,
“license_types”: [
“Beverage Room”,
“Cabaret”,
“Club”,
“Eating Establishment”,
“Lounge”,
“Military”,
“Permanent Special Occasion”,
“Special Premises”
]
}
]

I need something along the lines of:

[
  {
    "license_type": "Special Premises"
  },
  {
    "license_type": "Lounge"
  },
  {
    "license_type": "Military"
  },
  {
    "license_type": "Club"
  },
  {
    "license_type": "Eating Establishment"
  },
  {
    "license_type": "Cabaret"
  },
  {
    "license_type": "Beverage Room"
  },
  {
    "license_type": "Permanent  Special Occasion"
  },
  {}
]

So that Spring can read it into my Entity

4 
SELECT DISTINCT license_type , 0 AS _ID, 1 AS _CAS 
FROM LiquorLocator WHERE license_type IS NOT NULL;
5 
    @Query("SELECT DISTINCT license_type, 0 AS _ID, 1 AS _CAS FROM #{#n1ql.bucket} WHERE license_type IS NOT NULL")
List<EstablishmentType> findAllEstablishmentTypes();

Appears to be giving me a
java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String @vsr1

EDIT: Discovered that _ID is suppose to be a string, now it appears I’am getting this error:

org.springframework.data.mapping.model.MappingInstantiationException: Failed to instantiate com.liqourlocator.Liquor.Locator.model.EstablishmentType using constructor public com.liqourlocator.Liquor.Locator.model.EstablishmentType(java.lang.String,int,java.lang.String) with arguments null,null,Beverage Room

EDITEDIT: I have fixed the issue by removing the null values from the constructor.

6

change 0 AS _ID to “0” AS _ID

OR

If really need ID and CAS values

CREATE INDEX ix1 ON LiquorLocator(license_type, META().cas);

SELECT  mx.license_type, mx.id AS _ID, mx.cas AS _CAS
FROM LiquorLocator  AS l 
WHERE l.license_type IS NOT NULL
GROUP BY l.license_type
LETTING mx = MAX([l.license_type, {l.license_type, META(l).id, META(l).cas}])[1];