The table below is a collection in a MongoDB server.
I need to count the number of rows where 'Mgmt Type ID' is null.
I couldn't find a way using 'Simple Question' or 'Custom question'.
If I keep the field 'Enter some text' empty, I'm not able to confirm.
If I select the option 'is empty' I get an error after add the filter.
How can I do it ?
Hi @kleysonr
Post "Diagnostic Info" from Admin > Troubleshooting.
When I use "is empty", then I'm not getting any errors. Can you post the full stacktrace from Admin > Troubleshooting > Logs, so I can see what could be causing this, so we can fix it?
The latest release (0.39.0.1) has a new Custom Expression function, where you can check for nulls:
isnull([Mgmt Type ID])
Is it supposed isnull appear on the box below ?
And the logs as requested.
{
"browser-info": {
"language": "pt-BR",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "4.15.0-135-generic",
"user.language": "en",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"h2",
"mongo"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-04-20",
"tag": "v0.39.0.1",
"branch": "release-x.39.x",
"hash": "47bb5f2"
},
"settings": {
"report-timezone": "America/Sao_Paulo"
}
}
}
I gave I try on this isnull([Mgmt Type ID]) and seems to be working.
I'm gonna do more tests.
Thanks.
@kleysonr No, it's not a common function, so it's only available via Custom Expressions.
Can you include the full stacktrace, when you get an error - Admin > Troubleshooting > Logs?
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:12:39-03:00 DEBUG metabase.server.middleware.log GET /api/database 200 7.5 ms (4 DB calls) App DB connections: 0/10 Jetty threads: 5/50 (3 idle, 0 queued) (116 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:12:39-03:00 DEBUG metabase.server.middleware.log GET /api/database 200 7.1 ms (7 DB calls) App DB connections: 0/10 Jetty threads: 5/50 (3 idle, 0 queued) (116 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:12:54-03:00 DEBUG metabase.server.middleware.log GET /api/table/7/query_metadata 200 11.1 ms (9 DB calls) App DB connections: 0/10 Jetty threads: 5/50 (2 idle, 0 queued) (116 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:12:58-03:00 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 4.0 s (28 DB calls) App DB connections: 0/10 Jetty threads: 4/50 (2 idle, 0 queued) (120 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:14-03:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 3,
:started_at #t "2021-04-23T16:13:13.819587-03:00[America/Sao_Paulo]",
:json_query
{:type "query",
:query {:source-table 7, :filter ["is-empty" ["field" 54 nil]]},
:database 3,
:parameters [],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:native nil,
:status :failed,
:class java.lang.IllegalArgumentException,
:stacktrace
["org.bson.types.ObjectId.parseHexString(ObjectId.java:550)"
"org.bson.types.ObjectId.<init>(ObjectId.java:239)"
"--> driver.mongo.query_processor$eval658$fn__660.invoke(query_processor.clj:230)"
"driver.mongo.query_processor$filter_expr.invokeStatic(query_processor.clj:327)"
"driver.mongo.query_processor$filter_expr.invoke(query_processor.clj:325)"
"driver.mongo.query_processor$eval744$fn__746.invoke(query_processor.clj:340)"
"driver.mongo.query_processor$eval800$fn__802.invoke(query_processor.clj:355)"
"driver.mongo.query_processor$handle_filter.invokeStatic(query_processor.clj:379)"
"driver.mongo.query_processor$handle_filter.invoke(query_processor.clj:376)"
"driver.mongo.query_processor$eval1247$generate_aggregation_pipeline__1252$fn__1253$fn__1254.invoke(query_processor.clj:643)"
"driver.mongo.query_processor$eval1247$generate_aggregation_pipeline__1252$fn__1253.invoke(query_processor.clj:642)"
"driver.mongo.query_processor$eval1247$generate_aggregation_pipeline__1252.invoke(query_processor.clj:639)"
"driver.mongo.query_processor$mbql__GT_native.invokeStatic(query_processor.clj:674)"
"driver.mongo.query_processor$mbql__GT_native.invoke(query_processor.clj:667)"
"driver.mongo$eval2538$fn__2539.invoke(mongo.clj:220)"
"query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:14)"
"query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:9)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47220.invoke(mbql_to_native.clj:22)"
"query_processor.middleware.check_features$check_features$fn__46462.invoke(check_features.clj:39)"
"query_processor.middleware.limit$limit$fn__47206.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__45914.invoke(cache.clj:211)"
"query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47466.invoke(optimize_temporal_filters.clj:204)"
"query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49396.invoke(validate_temporal_bucketing.clj:50)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45033.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41378.invoke(wrap_value_literals.clj:161)"
"query_processor.middleware.annotate$add_column_info$fn__41253.invoke(annotate.clj:598)"
"query_processor.middleware.permissions$check_query_permissions$fn__46334.invoke(permissions.clj:81)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48324.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46535.invoke(cumulative_aggregations.clj:60)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48623.invoke(resolve_joined_fields.clj:102)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__48936.invoke(resolve_joins.clj:171)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44609.invoke(add_implicit_joins.clj:190)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47170.invoke(large_int_id.clj:59)"
"query_processor.middleware.format_rows$format_rows$fn__47151.invoke(format_rows.clj:74)"
"query_processor.middleware.desugar$desugar$fn__46601.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__45420.invoke(binning.clj:227)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__46137.invoke(resolve_fields.clj:34)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__44258.invoke(add_dimension_projections.clj:314)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44487.invoke(add_implicit_clauses.clj:147)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49345.invoke(upgrade_field_literals.clj:40)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44772.invoke(add_source_metadata.clj:123)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48498.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__44980.invoke(auto_bucket_datetimes.clj:147)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46184.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__48306.invoke(parameters.clj:111)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46236.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__46857.invoke(expand_macros.clj:155)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__44781.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49298.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48509$fn__48513.invoke(resolve_database_and_driver.clj:31)"
"driver$do_with_driver.invokeStatic(driver.clj:60)"
"driver$do_with_driver.invoke(driver.clj:56)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48509.invoke(resolve_database_and_driver.clj:25)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47097.invoke(fetch_source_query.clj:274)"
"query_processor.middleware.store$initialize_store$fn__49307$fn__49308.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:38)"
"query_processor.middleware.store$initialize_store$fn__49307.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__49352.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__47233.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44627.invoke(add_rows_truncated.clj:35)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49283.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__46478.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__48395.invoke(process_userland_query.clj:135)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__46418.invoke(catch_exceptions.clj:173)"
"query_processor.reducible$async_qp$qp_STAR___37956$thunk__37957.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___37956.invoke(reducible.clj:109)"
"query_processor.reducible$sync_qp$qp_STAR___37965$fn__37968.invoke(reducible.clj:135)"
"query_processor.reducible$sync_qp$qp_STAR___37965.invoke(reducible.clj:134)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:239)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:235)"
"query_processor$fn__49442$process_query_and_save_execution_BANG___49451$fn__49454.invoke(query_processor.clj:251)"
"query_processor$fn__49442$process_query_and_save_execution_BANG___49451.invoke(query_processor.clj:243)"
"query_processor$fn__49486$process_query_and_save_with_max_results_constraints_BANG___49495$fn__49498.invoke(query_processor.clj:263)"
"query_processor$fn__49486$process_query_and_save_with_max_results_constraints_BANG___49495.invoke(query_processor.clj:256)"
"api.dataset$run_query_async$fn__55699.invoke(dataset.clj:56)"
"query_processor.streaming$streaming_response_STAR_$fn__55678$fn__55679.invoke(streaming.clj:72)"
"query_processor.streaming$streaming_response_STAR_$fn__55678.invoke(streaming.clj:71)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
"async.streaming_response$do_f_async$fn__16071.invoke(streaming_response.clj:84)"],
:context :ad-hoc,
:error "invalid hexadecimal representation of an ObjectId: []",
:row_count 0,
:running_time 0,
:preprocessed
{:type :query,
:query
{:source-table 7,
:filter
[:or
[:=
[:field 54 nil]
[:value
nil
{:base_type :type/MongoBSONID,
:effective_type :type/MongoBSONID,
:coercion_strategy nil,
:semantic_type nil,
:database_type "org.bson.types.ObjectId",
:name "mgmt_type_id"}]]
[:=
[:field 54 nil]
[:value
""
{:base_type :type/MongoBSONID,
:effective_type :type/MongoBSONID,
:coercion_strategy nil,
:semantic_type nil,
:database_type "org.bson.types.ObjectId",
:name "mgmt_type_id"}]]],
:fields
[[:field 56 nil]
[:field 50 {:temporal-unit :default}]
[:field 54 nil]
[:field 49 nil]
[:field 47 {:temporal-unit :default}]
[:field 59 nil]
[:field 48 {:temporal-unit :default}]
[:field 52 {:temporal-unit :default}]
[:field 55 nil]
[:field 51 nil]
[:field 58 nil]
[:field 57 nil]
[:field 46 nil]
[:field 44 nil]
[:field 45 nil]
[:field 43 nil]
[:field 53 nil]],
:limit 2000},
:database 3,
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
:info
{:executed-by 1,
:context :ad-hoc,
:nested? false,
:query-hash
[44, 105, 48, -19, 95, -34, -11, 35, 124, 27, 63, -127, 40, 46, 40, -119, 127, -104, -64, -117, -80, -74, -32, 89,
-39, 50, 43, -16, -18, 54, 105, -115]},
:constraints {:max-results 10000, :max-results-bare-rows 2000}},
:data {:rows [], :cols []}}
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:14-03:00 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 525.6 ms (26 DB calls) App DB connections: 0/10 Jetty threads: 4/50 (2 idle, 0 queued) (116 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:40-03:00 DEBUG metabase.server.middleware.log GET /api/setting 200 1.0 ms (0 DB calls) App DB connections: 1/10 Jetty threads: 7/50 (1 idle, 0 queued) (114 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:40-03:00 DEBUG metabase.server.middleware.log GET /api/setup/admin_checklist 200 7.1 ms (11 DB calls) App DB connections: 0/10 Jetty threads: 7/50 (1 idle, 0 queued) (114 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:40-03:00 DEBUG metabase.server.middleware.log GET /api/session/properties 200 8.4 ms (2 DB calls) App DB connections: 0/10 Jetty threads: 8/50 (0 idle, 0 queued) (116 total active threads) Queries in flight: 0 (0 queued)
[ae09027b-01c7-4d0b-908b-4b061e044468] 2021-04-23T16:13:42-03:00 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 2.3 ms (1 DB calls) App DB connections: 0/10 Jetty threads: 5/50 (1 idle, 0 queued) (119 total active threads) Queries in flight: 0 (0 queued)
@kleysonr Excellent - I have reproduced and created an issue for it:
https://github.com/metabase/metabase/issues/15801 - upvote by clicking 
on the first post
Thanks.