data/v1 endpoint - Filter on columns and values with spaces in their names

dp12345 · 2023-03-05T23:06:24+00:00

There was an error rendering this rich post.

Is it possible to filter results on the data/v1 endpoint with columns or values that have spaces in their names? I've tried every combination imaginable to get results for a column named Spend Category, but continuously get this 400 error. Filtering results with columns and values that don't have spaces works as expected. Documentation I've been referencing has no insight re: handling spacing. Tried %20 escape, backticks, underscores, hyphens, etc, etc, etc.

"Bad Request","message":"DA0012: A filter was provided to filter the data but it contains a grammatical error or is otherwise invalid and it could not be parsed. The offending filter is: Spend Category = redacted. The parsing error is: expression values cannot be parsed at Spend Category = redacted.

Request looks like below.

const where = ['Spend Category = REDACTED'];

//Get the data
var query = `/data/v1/${datasets[0]}?fields=${fields.join()}&filter=${where.join()}`;

Documentation being used: https://developer.domo.com/docs/dev-studio-references/data-api

Find more posts tagged with

API

DDX

Accepted answers

All comments

GrantSmith

Do you have a fields variable defined in your code prior to this segment?

dp12345

@GrantSmith I do. The code runs properly if the where claus contains words without spaces (e.g. test = test). The issue is only with the where clause having columns and values that have spaces in them.

Strange thing is the same column (Spend Category) is in the fields variable and that part of the request works fine as mentioned.

user016142

I'm having this exact same problem. The field with a space is listed as a field and that works fine. When I list the field as a groupby I get an error:

DA0057: An alias list was provided but it could not be parsed.

tigers12

https://community-forums.domo.com/main/discussion/comment/63410#Comment_63410

@dp12345 Were you able to get this to work? If so, could you share how? I have come across the same issue.

Thanks.

Miranker

I'm encountering the same scenario. Anyone have an update on this?

jasondoucette

We ran into this yesterday too, and it turned out my colleague and I worked around it in two different ways:

ETL the dataset into another one where you can change the column names to not have spaces (or change the existing one if possible to have different column names.) It's not ideal, especially if you're using filters on multiple cards, because they won't match.
Use the SQL API (at the bottom of https://developer.domo.com/portal/8s3y9eldnjq8d-data-api) and run your query as a SQL statement. If you do this, you can escape your column names in backticks (like "Select `column name` where `other column name` = 'blah'".) Unfortunately this won't work with page filters either (if you couldn't tell, our use case didn't depend on filters working) but with some extra coding you could tap into domo.onFiltersUpdate() to dynamically build your where clause if that was required.

The third option would be to raise a ticket with Domo to address the issue properly in the data API, not sure if anyone's done that yet?