Getting Unique Values for a Field

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.

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

cube(`Users`, {
  sql: `SELECT * FROM public.users`,

  dimensions: {
    city: {
      sql: `city`,
      type: `string`,
    },

    state: {
      sql: `state`,
      type: `string`,
    },
  },
});

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

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

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',
  },
];

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
// Information about cubes, dimensions included
{
  "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.

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

Did you find this page useful?