search

metabase / metabase

The simplest, fastest way to get business intelligence and analytics to everyone in your company :yum:
https://metabase.com
Other
38.32k stars 5.08k forks source link

Postgres: field values query errors for JSON fields #12153

Open sbelak opened 4 years ago

sbelak commented 4 years ago 
ERROR models.field-values :: Error fetching field values
org.postgresql.util.PSQLException: ERROR: could not identify an ordering operator for type json
  Hint: Use an explicit ordering operator or modify the query.
  Position: 130
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)
    at ********.driver.sql_jdbc.execute$eval72117$fn__72118.invoke(execute.clj:267)
    at clojure.lang.MultiFn.invoke(MultiFn.java:234)
    at ********.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)
    at ********.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)
    at ********.driver.sql_jdbc$eval81659$fn__81660.invoke(sql_jdbc.clj:50)
    at clojure.lang.MultiFn.invoke(MultiFn.java:244)
    at ********.query_processor.context$executef.invokeStatic(context.clj:59)
    at ********.query_processor.context$executef.invoke(context.clj:48)
    at ********.query_processor.context.default$default_runf.invokeStatic(default.clj:69)
    at ********.query_processor.context.default$default_runf.invoke(default.clj:67)
    at ********.query_processor.context$runf.invokeStatic(context.clj:45)
    at ********.query_processor.context$runf.invoke(context.clj:39)
    at ********.query_processor.reducible$pivot.invokeStatic(reducible.clj:34)
    at ********.query_processor.reducible$pivot.invoke(reducible.clj:31)
    at ********.query_processor.middleware.mbql_to_native$mbql__GT_native$fn__52517.invoke(mbql_to_native.clj:26)
    at ********.query_processor.middleware.check_features$check_features$fn__51751.invoke(check_features.clj:42)
    at ********.query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__52716.invoke(optimize_datetime_filters.clj:133)
    at ********.query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__57096.invoke(wrap_value_literals.clj:137)
    at ********.query_processor.middleware.annotate$add_column_info$fn__50215.invoke(annotate.clj:559)
    at ********.query_processor.middleware.permissions$check_query_permissions$fn__51610.invoke(permissions.clj:64)
    at ********.query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__53253.invoke(pre_alias_aggregations.clj:40)
    at ********.query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__51840.invoke(cumulative_aggregations.clj:61)
    at ********.query_processor.middleware.resolve_joins$resolve_joins$fn__53827.invoke(resolve_joins.clj:183)
    at ********.query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__48221.invoke(add_implicit_joins.clj:245)
    at ********.query_processor.middleware.limit$limit$fn__52495.invoke(limit.clj:38)
    at ********.query_processor.middleware.format_rows$format_rows$fn__52468.invoke(format_rows.clj:81)
    at ********.query_processor.middleware.desugar$desugar$fn__51912.invoke(desugar.clj:22)
    at ********.query_processor.middleware.binning$update_binning_strategy$fn__50775.invoke(binning.clj:229)
    at ********.query_processor.middleware.resolve_fields$resolve_fields$fn__51404.invoke(resolve_fields.clj:24)
    at ********.query_processor.middleware.add_dimension_projections$add_remapping$fn__47733.invoke(add_dimension_projections.clj:272)
    at ********.query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__47969.invoke(add_implicit_clauses.clj:147)
    at ********.query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__48384.invoke(add_source_metadata.clj:105)
    at ********.query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__53478.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)
    at ********.query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__50408.invoke(auto_bucket_datetimes.clj:125)
    at ********.query_processor.middleware.resolve_source_table$resolve_source_tables$fn__51459.invoke(resolve_source_table.clj:46)
    at ********.query_processor.middleware.parameters$substitute_parameters$fn__53229.invoke(parameters.clj:97)
    at ********.query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__51513.invoke(resolve_referenced.clj:80)
    at ********.query_processor.middleware.expand_macros$expand_macros$fn__52176.invoke(expand_macros.clj:158)
    at ********.query_processor.middleware.add_timezone_info$add_timezone_info$fn__48431.invoke(add_timezone_info.clj:15)
    at ********.query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__56928.invoke(splice_params_in_response.clj:32)
    at ********.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53495$fn__53499.invoke(resolve_database_and_driver.clj:33)
    at ********.driver$do_with_driver.invokeStatic(driver.clj:61)
    at ********.driver$do_with_driver.invoke(driver.clj:57)
    at ********.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53495.invoke(resolve_database_and_driver.clj:27)
    at ********.query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__52390.invoke(fetch_source_query.clj:243)
    at ********.query_processor.middleware.store$initialize_store$fn__56945$fn__56946.invoke(store.clj:11)
    at ********.query_processor.store$do_with_store.invokeStatic(store.clj:46)
    at ********.query_processor.store$do_with_store.invoke(store.clj:40)
    at ********.query_processor.middleware.store$initialize_store$fn__56945.invoke(store.clj:10)
    at ********.query_processor.middleware.cache$maybe_return_cached_results$fn__51356.invoke(cache.clj:201)
    at ********.query_processor.middleware.validate$validate_query$fn__56962.invoke(validate.clj:10)
    at ********.query_processor.middleware.normalize_query$normalize$fn__52538.invoke(normalize_query.clj:22)
    at ********.query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48247.invoke(add_rows_truncated.clj:36)
    at ********.query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__56907.invoke(results_metadata.clj:128)
    at ********.query_processor.reducible$async_qp$qp_STAR___46992$thunk__46993.invoke(reducible.clj:100)
    at ********.query_processor.reducible$async_qp$qp_STAR___46992.invoke(reducible.clj:106)
    at ********.query_processor.reducible$async_qp$qp_STAR___46992.invoke(reducible.clj:93)
    at ********.query_processor.reducible$sync_qp$qp_STAR___47001$fn__47002.invoke(reducible.clj:128)
    at ********.query_processor.reducible$sync_qp$qp_STAR___47001.invoke(reducible.clj:127)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invokeStatic(core.clj:667)
    at clojure.core$apply.invoke(core.clj:660)
    at ********.query_processor$process_query.invokeStatic(query_processor.clj:124)
    at ********.query_processor$process_query.doInvoke(query_processor.clj:118)
    at clojure.lang.RestFn.invoke(RestFn.java:410)
    at ********.db.metadata_queries$qp_query$fn__62981.invoke(metadata_queries.clj:17)
    at ********.db.metadata_queries$qp_query.invokeStatic(metadata_queries.clj:16)
    at ********.db.metadata_queries$qp_query.invoke(metadata_queries.clj:14)
    at ********.db.metadata_queries$field_query.invokeStatic(metadata_queries.clj:26)
    at ********.db.metadata_queries$field_query.invoke(metadata_queries.clj:24)
    at ********.db.metadata_queries$eval63001$field_distinct_values__63010$fn__63013.invoke(metadata_queries.clj:71)
    at ********.db.metadata_queries$eval63001$field_distinct_values__63010.invoke(metadata_queries.clj:64)
    at ********.db.metadata_queries$eval63001$field_distinct_values__63010$fn__63011.invoke(metadata_queries.clj:68)
    at ********.db.metadata_queries$eval63001$field_distinct_values__63010.invoke(metadata_queries.clj:64)
    at clojure.lang.Var.invoke(Var.java:384)
    at ********.models.field_values$distinct_values.invokeStatic(field_values.clj:79)
    at ********.models.field_values$distinct_values.invoke(field_values.clj:73)
    at ********.models.field_values$create_or_update_field_values_BANG_.invokeStatic(field_values.clj:101)
    at ********.models.field_values$create_or_update_field_values_BANG_.doInvoke(field_values.clj:95)
    at clojure.lang.RestFn.invoke(RestFn.java:410)
    at ********.sync.field_values$eval63541$update_field_values_for_field_BANG___63546$fn__63547.invoke(field_values.clj:25)
    at ********.sync.field_values$eval63541$update_field_values_for_field_BANG___63546.invoke(field_values.clj:23)
    at ********.sync.field_values$eval63567$update_field_values_for_table_BANG___63572$fn__63573$fn__63574$fn__63575.invoke(field_values.clj:46)
    at ********.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
    at ********.sync.util$do_with_error_handling.invoke(util.clj:142)
    at ********.sync.field_values$eval63567$update_field_values_for_table_BANG___63572$fn__63573$fn__63574.invoke(field_values.clj:44)
    at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
    at clojure.core$reduce.invokeStatic(core.clj:6827)
    at clojure.core$reduce.invoke(core.clj:6810)
    at ********.sync.field_values$eval63567$update_field_values_for_table_BANG___63572$fn__63573.invoke(field_values.clj:43)
    at ********.sync.field_values$eval63567$update_field_values_for_table_BANG___63572.invoke(field_values.clj:40)
    at clojure.core$map$fn__5866.invoke(core.clj:2753)
    at clojure.lang.LazySeq.sval(LazySeq.java:42)
    at clojure.lang.LazySeq.seq(LazySeq.java:51)
    at clojure.lang.Cons.next(Cons.java:39)
    at clojure.lang.RT.boundedLength(RT.java:1792)
    at clojure.lang.RestFn.applyTo(RestFn.java:130)
    at clojure.core$apply.invokeStatic(core.clj:667)
    at clojure.core$apply.invoke(core.clj:660)
    at ********.sync.field_values$eval63594$update_field_values_for_database_BANG___63599$fn__63600.invoke(field_values.clj:54)
    at ********.sync.field_values$eval63594$update_field_values_for_database_BANG___63599.invoke(field_values.clj:52)
    at ********.sync.util$eval54128$run_step_with_metadata__54133$fn__54137$fn__54139.invoke(util.clj:345)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
    at ********.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:119)
    at ********.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:116)
    at ********.sync.util$eval54128$run_step_with_metadata__54133$fn__54137.invoke(util.clj:342)
    at ********.sync.util$eval54128$run_step_with_metadata__54133.invoke(util.clj:337)
    at ********.sync.util$eval54320$run_sync_operation__54325$fn__54326$fn__54327.invoke(util.clj:430)
    at clojure.core$mapv$fn__8445.invoke(core.clj:6912)
    at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
    at clojure.core$reduce.invokeStatic(core.clj:6827)
    at clojure.core$mapv.invokeStatic(core.clj:6903)
    at clojure.core$mapv.invoke(core.clj:6903)
    at ********.sync.util$eval54320$run_sync_operation__54325$fn__54326.invoke(util.clj:430)
    at ********.sync.util$eval54320$run_sync_operation__54325.invoke(util.clj:424)
    at ********.sync.field_values$eval63620$update_field_values_BANG___63625$fn__63626$fn__63627.invoke(field_values.clj:69)
    at ********.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
    at ********.sync.util$do_with_error_handling.invoke(util.clj:142)
    at ********.sync.util$do_with_error_handling.invokeStatic(util.clj:145)
    at ********.sync.util$do_with_error_handling.invoke(util.clj:142)
    at clojure.core$partial$fn__5839.invoke(core.clj:2623)
    at ********.driver$eval29071$fn__29072.invoke(driver.clj:530)
    at clojure.lang.MultiFn.invoke(MultiFn.java:239)
    at ********.sync.util$sync_in_context$fn__54010.invoke(util.clj:138)
    at ********.sync.util$with_db_logging_disabled$fn__54007.invoke(util.clj:129)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
    at ********.sync.util$with_start_and_finish_logging$fn__53996.invoke(util.clj:114)
    at ********.sync.util$with_sync_events$fn__53991.invoke(util.clj:88)
    at ********.sync.util$with_duplicate_ops_prevented$fn__53982.invoke(util.clj:67)
    at ********.sync.util$do_sync_operation.invokeStatic(util.clj:164)
    at ********.sync.util$do_sync_operation.invoke(util.clj:161)
    at ********.sync.field_values$eval63620$update_field_values_BANG___63625$fn__63626.invoke(field_values.clj:67)
    at ********.sync.field_values$eval63620$update_field_values_BANG___63625.invoke(field_values.clj:63)
    at ********.sync$eval65362$sync_database_BANG___65367$fn__65368$fn__65369.invoke(sync.clj:33)
    at ********.sync.util$do_with_error_handling.invokeStatic(util.clj:149)
    at ********.sync.util$do_with_error_handling.invoke(util.clj:142)
    at ********.sync.util$do_with_error_handling.invokeStatic(util.clj:145)
    at ********.sync.util$do_with_error_handling.invoke(util.clj:142)
    at clojure.core$partial$fn__5839.invoke(core.clj:2623)
    at ********.driver$eval29071$fn__29072.invoke(driver.clj:530)
    at clojure.lang.MultiFn.invoke(MultiFn.java:239)
    at ********.sync.util$sync_in_context$fn__54010.invoke(util.clj:138)
    at ********.sync.util$with_db_logging_disabled$fn__54007.invoke(util.clj:129)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
    at ********.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
    at ********.sync.util$with_start_and_finish_logging$fn__53996.invoke(util.clj:114)
    at ********.sync.util$with_sync_events$fn__53991.invoke(util.clj:88)
    at ********.sync.util$with_duplicate_ops_prevented$fn__53982.invoke(util.clj:67)
    at ********.sync.util$do_sync_operation.invokeStatic(util.clj:164)
    at ********.sync.util$do_sync_operation.invoke(util.clj:161)
    at ********.sync$eval65362$sync_database_BANG___65367$fn__65368.invoke(sync.clj:27)
    at ********.sync$eval65362$sync_database_BANG___65367.invoke(sync.clj:19)
    at ********.test.data.impl$create_database_BANG_$fn__141112.invoke(impl.clj:96)
    at ********.util$do_with_timeout$fn__10215.invoke(util.clj:310)
    at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
    at clojure.lang.AFn.call(AFn.java:18)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Sync :postgres Database Postgres with a JSON Field took 214.1 ms

According to https://stackoverflow.com/questions/16610088/how-to-perform-a-sql-query-that-orders-by-a-column-of-type-json you can only use JSON fields via an accessor in ORDER BY.

:arrow_down: Please click the :+1: reaction instead of leaving a +1 or update? comment

dker92 commented 4 years ago

I got the same issue, I can't open one of my table which have a json column field

bchrobot commented 3 years ago

We are also encountering this as well. The Postgres database is version 13.

michael-gratton commented 1 year ago

We are seeing this during Metabase's schema sync passes with Postgres 13 tables containing a json type column. The Metabase sync query that is failing is:

SELECT "public"."campaigns"."outro" AS "outro" FROM "public"."campaigns" GROUP BY  "public"."campaigns"."outro" ORDER BY "public"."campaigns"."outro" ASC LIMIT 1000

The error Postgres gives when running the query above is:

ERROR:  could not identify an ordering operator for type json
LINE 1: ..." GROUP BY  "public"."campaigns"."outro" ORDER BY "public"."...
                                                             ^
HINT:  Use an explicit ordering operator or modify the query.

Note that the error is generated by Postgres when parsing the query itself, not when executing it. So it's not related to the data in the table, rather it is related to the query and the schema/column definition.

The definition of the outro column referenced by that query is:

CREATE TABLE "public"."campaigns" (
    [snip]
    "outro" json DEFAULT '{}'::json,
    [snip]
);

This issue is that the JSON object could have any structure, and Postgres does not know how to order arbitrary objects.

A workaround is treat the column as text (may need a different workaround for jsonb fields?). For example, modifying the query above to cast the column to text does not produce an error when run:

SELECT "public"."campaigns"."outro"::text AS "outro" FROM "public"."campaigns" GROUP BY  "public"."campaigns"."outro"::text ORDER BY "public"."campaigns"."outro"::text ASC LIMIT 1000;

But this might suffer performance issues for larger datasets.

Maybe Metabase should just not attempt to scan json field types?