Solved

How to create a query with custom dimensions via the API?

  • 27 October 2020
  • 4 replies
  • 754 views

I’m trying to construct a query dynamically in Python and provide a link to it as part of some output. I’m posting to [url]/api/3.1/queries with body equivalent to

{'model': 'company_investigations', 'view': 'investigations', 'fields': ['a_bunch.of_fields', 'a_table.like_this.one'], 'filters': {'investigations.created_time': '2020-06-01 to 2020-07-01', 'investigations.team': '1234'}, 'query_timezone': 'UTC', 'limit': 500, 'dynamic_fields': [{'dimension': 'a_name_for_it', 'label': 'A Name For It', 'expression': '${investigations.a_number} <= 25', '_kind_hint': 'dimension', '_type_hint': 'yesno'}]}

Getting a 500 “An error has occurred.”

The same thing works if I remove the dynamic_fields kvp.

Does anyone have an example of a body that works while including a custom dimension?

icon

Best answer by izzy 27 October 2020, 23:49

View original

4 replies

From the logs: Java::JavaSql::SQLSyntaxErrorException: (conn=112875697) Unknown column 'dimension' in 'field list': INSERT INTO `query` (`view`, `fields`, `filters`, `limit`, `dynamic_fields`, `query_timezone`, `model`, `hash`, `slug`, `created_at`) VALUES *REDACTED*, *REDACTED*, *REDACTED*, '5000000', (((`dimension` = 'a_name_for_it') AND (`label` = 'A Name For It') AND (`expression` = '1') AND (`_kind_hint` = 'dimension') AND (`_type_hint` = 'number'))), 'UTC', ...

Userlevel 7
Badge +1

Hmm that’s interesting. I’m not 100% sure what you’re doing there, but I just did some tinkering and here is a very barebones query that worked against create_query()

 

{
"model": "thelook",
"view": "order_items",
"fields": [
"order_items.id",
"calculation_5"
],
"pivots": [],
"fill_fields": [],
"filters": {},
"filter_expression": "",
"sorts": [],
"limit": "500",
"column_limit": "",
"total": false,
"row_total": "",
"subtotals": [],
"vis_config": {},
"filter_config": {},
"visible_ui_sections": "",
"dynamic_fields": "[{\"dimension\":\"calculation_5\",\"label\":\"Calculation 1\",\"expression\":\"1 \",\"value_format\":null,\"value_format_name\":null,\"_kind_hint\":\"dimension\",\"_type_hint\":\"number\"}]",
"query_timezone": "America/Los_Angeles",
"runtime": 0
}

 

 

I’m sure the answer must lie in the dynamic_fields param:

Mine:

"dynamic_fields": "[{\"dimension\":\"calculation_5\",\"label\":\"Calculation 1\",\"expression\":\"1 \",\"value_format\":null,\"value_format_name\":null,\"_kind_hint\":\"dimension\",\"_type_hint\":\"number\"}]",

Yours:

'dynamic_fields': [{'dimension': 'a_name_for_it', 'label': 'A Name For It', 'expression': '${investigations.a_number} <= 25', '_kind_hint': 'dimension', '_type_hint': 'yesno'}]

Perhaps the URL encoding and escaping? I would give that a try.

Answer: we want the dynamic_fields value to be a _string_ which can be parsed as json, rather than being a json object itself.

dynamic_fields = [
{
'dimension': 'cheap',
'label': 'Cheap',
'expression': '${a_table.dollar_value} <= 25',
'_kind_hint': 'dimension',
'_type_hint': 'yesno'
}
]

query_request['dynamic_fields'] = json.dumps(dynamic_fields)

...

response = self.session.post(url, json=query_request)

This works fine.

Userlevel 7
Badge +1

Aaah of course, I hadn’t noticed the obvious quotation marks. Nice, glad you got it working.

Reply