Getting unique values for a field

Use case

We have a dashboard with information about the users, and we'd like to filter them by city. To do so, we need to display all unique values for cities in the dropdown. In the recipe below, we'll learn how to get unique values for dimensions.

Data modeling

To filter users by city, we need to define the appropriate dimension:

cubes:
  - name: users
    sql_table: users
 
    dimensions:
      - name: city
        sql: city
        type: string
 
      - name: state
        sql: state
        type: string

Query

It is enough to include only a dimension in the query to get all unique values of that dimension:

{
  "dimensions": ["users.city"]
}

Result

We got the unique values of the city dimension, and now we can use them in the dropdown on the dashboard:

[
  {
    "users.city": "Austin"
  },
  {
    "users.city": "Chicago"
  },
  {
    "users.city": "Los Angeles"
  },
  {
    "users.city": "Mountain View"
  }
]

Choosing dimensions

In case we need to choose a dimension or render dropdowns for all dimensions, we can fetch the list of dimensions for all cubes from the /meta endpoint:

curl http://localhost:4000/cubejs-api/v1/meta

{
  "cubes": [
    {
      "name": "users",
      "title": "Users",
      "measures": [],
      "dimensions": [
        {
          "name": "users.city",
          "title": "Users City",
          "type": "string",
          "shortTitle": "City",
          "suggestFilterValues": true
        },
        {
          "name": "users.state",
          "title": "Users State",
          "type": "string",
          "shortTitle": "State",
          "suggestFilterValues": true
        }
      ],
      "segments": []
    }
  ]
}

Then, we can iterate through dimension names and use any of them in a query.

Source code

Please feel free to check out the full source code (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.