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

dp12345
dp12345 Member
in APIs & Domo Developer

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

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • dp12345
    dp12345 Member

    @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
    user016142 Member

    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
    tigers12 Member
    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
    Miranker Member

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

  • jasondoucette
    jasondoucette Member

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

    1. 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.
    2. 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?

Categories