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?
Solved! Go to Solution.
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.
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', ...
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.
Aaah of course, I hadn’t noticed the obvious quotation marks. Nice, glad you got it working.