Hasura中如何获取projects表各属性的所有唯一值？

Problem Statement #

I'm using Hasura with a projects table that has these columns:

projects: {
  country // string
  type // boolean
  year // integer
}

I want to get all unique values for each column individually, expecting a result structure like:

{
  "countries": ["a", "b", "c"],
  "types": [true, false],
  "years": [2000, 2001, 2002]
}

I tried this query, but it doesn't work as expected:

query Test {
  projects(distinct_on: [country, type, year]) {
    country
    type
    year
  }
}

The issue is this returns unique combinations of (country, type, year), so individual columns still have duplicate values (e.g., the same country appears in multiple rows with different types/years).

Is there a better way to do this than running separate queries for each column?

Answer #

Great question! You don't need to run multiple separate queries—Hasura lets you fetch all these unique values in a single request using either multiple subqueries with distinct_on or aggregate queries with the distinct function (which gives a cleaner array output).

Option 1: Subqueries with distinct_on

You can define multiple named subqueries, each targeting a single column with distinct_on to get unique values for that column alone:

query GetUniqueValues {
  # Fetch unique countries
  uniqueCountries: projects(distinct_on: [country]) {
    country
  }
  # Fetch unique types
  uniqueTypes: projects(distinct_on: [type]) {
    type
  }
  # Fetch unique years
  uniqueYears: projects(distinct_on: [year]) {
    year
  }
}

This will return a result where each key is an array of objects containing the unique value:

{
  "data": {
    "uniqueCountries": [{"country": "a"}, {"country": "b"}, {"country": "c"}],
    "uniqueTypes": [{"type": true}, {"type": false}],
    "uniqueYears": [{"year": 2000}, {"year": 2001}, {"year": 2002}]
  }
}

Option 2: Aggregate Queries with distinct (Cleaner Array Output)

If you want pure arrays of values instead of objects, use Hasura's aggregate API with the distinct function. This directly returns an array of unique values for each column:

query GetUniqueValueArrays {
  countries: projects_aggregate {
    uniqueValues: distinct(column: country)
  }
  types: projects_aggregate {
    uniqueValues: distinct(column: type)
  }
  years: projects_aggregate {
    uniqueValues: distinct(column: year)
  }
}

The result will be exactly the structure you're looking for:

{
  "data": {
    "countries": {
      "uniqueValues": ["a", "b", "c"]
    },
    "types": {
      "uniqueValues": [true, false]
    },
    "years": {
      "uniqueValues": [2000, 2001, 2002]
    }
  }
}

Why This Works

Both approaches avoid the problem with your original query because each subquery/aggregate targets only one column for deduplication, not a combination of columns. This ensures you get every unique value for each field without duplicates.

内容的提问来源于stack exchange，提问作者supersize