JSON unfolding not working in some cases

Automatic JSON unfolding is not working, for some reason, for a particular column in my Postgres db.

Seeing this error (don't know if it's exactly related or not) :

Error
[fab5b8e7-fa92-4695-a28e-f009716f6af6] 2022-12-16T22:45:22+05:30 ERROR metabase.models.field-values Error fetching field values
java.sql.SQLException: An SQLException was provoked by the following failure: com.mchange.v2.resourcepool.ResourcePoolException: Attempted to use a closed or broken resource pool
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:74)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:694)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
	at metabase.driver.sql_jdbc.execute$fn__55821.invokeStatic(execute.clj:191)
	at metabase.driver.sql_jdbc.execute$fn__55821.invoke(execute.clj:189)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:496)
	at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:485)
	at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:493)
	at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:485)
	at metabase.driver.sql_jdbc$fn__88395.invokeStatic(sql_jdbc.clj:58)
	at metabase.driver.sql_jdbc$fn__88395.invoke(sql_jdbc.clj:56)
	at clojure.lang.MultiFn.invoke(MultiFn.java:244)
	at metabase.query_processor.context$executef.invokeStatic(context.clj:59)
	at metabase.query_processor.context$executef.invoke(context.clj:48)
	at metabase.query_processor.context.default$default_runf.invokeStatic(default.clj:67)
	at metabase.query_processor.context.default$default_runf.invoke(default.clj:65)
	at metabase.query_processor.context$runf.invokeStatic(context.clj:45)
	at metabase.query_processor.context$runf.invoke(context.clj:39)
	at metabase.query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)
	at metabase.query_processor.reducible$identity_qp.invoke(reducible.clj:9)
	at metabase.query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___53529.invoke(cache.clj:220)
	at metabase.query_processor.middleware.permissions$check_query_permissions$fn__49184.invoke(permissions.clj:109)
	at metabase.query_processor.middleware.mbql_to_native$mbql__GT_native$fn__52474.invoke(mbql_to_native.clj:23)
	at metabase.query_processor$fn__55005$combined_post_process__55010$combined_post_process_STAR___55011.invoke(query_processor.clj:212)
	at metabase.query_processor$fn__55005$combined_pre_process__55006$combined_pre_process_STAR___55007.invoke(query_processor.clj:209)
	at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53450$fn__53455.invoke(resolve_database_and_driver.clj:35)
	at metabase.driver$do_with_driver.invokeStatic(driver.clj:76)
	at metabase.driver$do_with_driver.invoke(driver.clj:72)
	at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53450.invoke(resolve_database_and_driver.clj:34)
	at metabase.query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__49450.invoke(fetch_source_query.clj:314)
	at metabase.query_processor.middleware.store$initialize_store$fn__49640$fn__49641.invoke(store.clj:11)
	at metabase.query_processor.store$do_with_store.invokeStatic(store.clj:45)
	at metabase.query_processor.store$do_with_store.invoke(store.clj:39)
	at metabase.query_processor.middleware.store$initialize_store$fn__49640.invoke(store.clj:10)
	at metabase.query_processor.middleware.normalize_query$normalize$fn__53722.invoke(normalize_query.clj:22)
	at metabase.query_processor.reducible$async_qp$qp_STAR___45482$thunk__45484.invoke(reducible.clj:100)
	at metabase.query_processor.reducible$async_qp$qp_STAR___45482.invoke(reducible.clj:106)
	at metabase.query_processor.reducible$async_qp$qp_STAR___45482.invoke(reducible.clj:91)
	at metabase.query_processor.reducible$async_qp$qp_STAR___45482.invoke(reducible.clj:88)
	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:662)
	at metabase.query_processor.reducible$sync_qp$qp_STAR___45493.doInvoke(reducible.clj:126)
	at clojure.lang.RestFn.applyTo(RestFn.java:137)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.query_processor$process_query.invokeStatic(query_processor.clj:247)
	at metabase.query_processor$process_query.doInvoke(query_processor.clj:241)
	at clojure.lang.RestFn.invoke(RestFn.java:410)
	at metabase.db.metadata_queries$qp_query$fn__69150.invoke(metadata_queries.clj:21)
	at metabase.db.metadata_queries$qp_query.invokeStatic(metadata_queries.clj:20)
	at metabase.db.metadata_queries$qp_query.invoke(metadata_queries.clj:18)
	at metabase.db.metadata_queries$field_query.invokeStatic(metadata_queries.clj:31)
	at metabase.db.metadata_queries$field_query.invoke(metadata_queries.clj:29)
	at metabase.db.metadata_queries$fn__69170$field_distinct_values__69179$fn__69182.invoke(metadata_queries.clj:76)
	at metabase.db.metadata_queries$fn__69170$field_distinct_values__69179.invoke(metadata_queries.clj:69)
	at metabase.db.metadata_queries$fn__69170$field_distinct_values__69179$fn__69180.invoke(metadata_queries.clj:73)
	at metabase.db.metadata_queries$fn__69170$field_distinct_values__69179.invoke(metadata_queries.clj:69)
	at clojure.lang.Var.invoke(Var.java:384)
	at metabase.models.field_values$distinct_values.invokeStatic(field_values.clj:292)
	at metabase.models.field_values$distinct_values.invoke(field_values.clj:277)
	at metabase.models.field_values$create_or_update_full_field_values_BANG_.invokeStatic(field_values.clj:319)
	at metabase.models.field_values$create_or_update_full_field_values_BANG_.doInvoke(field_values.clj:311)
	at clojure.lang.RestFn.invoke(RestFn.java:410)
	at metabase.sync.field_values$fn__68985$update_field_values_for_field_BANG___68990$fn__68991.invoke(field_values.clj:26)
	at metabase.sync.field_values$fn__68985$update_field_values_for_field_BANG___68990.invoke(field_values.clj:24)
	at metabase.sync.field_values$fn__69012$update_field_values_for_table_BANG___69017$fn__69018$fn__69019$fn__69020.invoke(field_values.clj:51)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:160)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:153)
	at metabase.sync.field_values$fn__69012$update_field_values_for_table_BANG___69017$fn__69018$fn__69019.invoke(field_values.clj:49)
	at clojure.lang.PersistentVector.reduce(PersistentVector.java:343)
	at clojure.core$reduce.invokeStatic(core.clj:6885)
	at clojure.core$reduce.invoke(core.clj:6868)
	at metabase.sync.field_values$fn__69012$update_field_values_for_table_BANG___69017$fn__69018.invoke(field_values.clj:48)
	at metabase.sync.field_values$fn__69012$update_field_values_for_table_BANG___69017.invoke(field_values.clj:45)
	at clojure.core$map$fn__5935.invoke(core.clj:2770)
	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:1790)
	at clojure.lang.RestFn.applyTo(RestFn.java:130)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.sync.field_values$fn__69040$update_field_values_for_database_BANG___69045$fn__69046.invoke(field_values.clj:60)
	at metabase.sync.field_values$fn__69040$update_field_values_for_database_BANG___69045.invoke(field_values.clj:57)
	at metabase.sync.field_values$make_sync_field_values_steps$fn__69118.invoke(field_values.clj:102)
	at clojure.lang.AFn.applyToHelper(AFn.java:154)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:669)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.sync.util$fn__42819$run_step_with_metadata__42824$fn__42828$fn__42830.doInvoke(util.clj:388)
	at clojure.lang.RestFn.invoke(RestFn.java:397)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
	at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:119)
	at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:116)
	at metabase.sync.util$fn__42819$run_step_with_metadata__42824$fn__42828.invoke(util.clj:383)
	at metabase.sync.util$fn__42819$run_step_with_metadata__42824.invoke(util.clj:378)
	at metabase.sync.util$fn__43040$run_sync_operation__43045$fn__43046$fn__43054.invoke(util.clj:495)
	at metabase.sync.util$fn__43040$run_sync_operation__43045$fn__43046.invoke(util.clj:493)
	at metabase.sync.util$fn__43040$run_sync_operation__43045.invoke(util.clj:487)
	at metabase.sync.field_values$fn__69126$update_field_values_BANG___69131$fn__69132$fn__69133.invoke(field_values.clj:112)
	at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:160)
	at metabase.sync.util$do_with_error_handling.invoke(util.clj:153)
	at clojure.core$partial$fn__5910.invoke(core.clj:2647)
	at metabase.driver$fn__33693.invokeStatic(driver.clj:626)
	at metabase.driver$fn__33693.invoke(driver.clj:626)
	at clojure.lang.MultiFn.invoke(MultiFn.java:239)
	at metabase.sync.util$sync_in_context$fn__42740.invoke(util.clj:138)
	at metabase.sync.util$with_db_logging_disabled$fn__42737.invoke(util.clj:129)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:102)
	at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:96)
	at metabase.sync.util$with_start_and_finish_logging$fn__42726.invoke(util.clj:114)
	at metabase.sync.util$with_sync_events$fn__42721.invoke(util.clj:88)
	at metabase.sync.util$with_duplicate_ops_prevented$fn__42712.invoke(util.clj:67)
	at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:181)
	at metabase.sync.util$do_sync_operation.invoke(util.clj:178)
	at metabase.sync.field_values$fn__69126$update_field_values_BANG___69131$fn__69132.invoke(field_values.clj:109)
	at metabase.sync.field_values$fn__69126$update_field_values_BANG___69131.invoke(field_values.clj:105)
	at metabase.api.database$fn__78707$fn__78708$fn__78709.invoke(database.clj:924)
	at clojure.core$binding_conveyor_fn$fn__5823.invoke(core.clj:2047)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.mchange.v2.resourcepool.ResourcePoolException: Attempted to use a closed or broken resource pool
	at com.mchange.v2.resourcepool.BasicResourcePool.ensureNotBroken(BasicResourcePool.java:1824)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:611)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
	... 126 more

Is there an option to manually 'unfold' it somehow?

This is an example of the data in the JSON field:

{"onLine": true, "languages": ["en-GB"], "connection": {}, "userAgentData": {"brands": [{"brand": "Not?A_Brand", "version": "8"}, {"brand": "Chromium", "version": "108"}, {"brand": "Google Chrome", "version": "108"}], "mobile": true, "platform": "Android"}, "software_version": "5.2"}

@staff

Hi @shrey
I cannot reproduce on latest 0.45.1 with the example you've provided. It looks like there's a connection issue rather than it being related to unfolding specifically.

Hi @flamber , i'm not really sure how to diagnose this further on my end.
As, i've tried resyncing the database and 're-scanning the field values' again, just now, with the same result and nothing new in the logs.

Could use any guidance regarding it.
Thanks.

@shrey Post "Diagnostic Info" from Admin > Troubleshooting.

Create a separate table (or database), which contains as little as needed to reproduce the issue.

@flamber

{
  "browser-info": {
    "language": "en-GB",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.17+8-post-Ubuntu-1ubuntu222.04",
    "java.vendor": "Ubuntu",
    "java.vendor.url": "https://ubuntu.com/",
    "java.version": "11.0.17",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.17+8-post-Ubuntu-1ubuntu222.04",
    "os.name": "Linux",
    "os.version": "5.15.0-56-generic",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.12 (Ubuntu 12.12-1.pgdg20.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-12-07",
      "tag": "v0.45.1",
      "branch": "release-x.45.x",
      "hash": "019d31c"
    },
    "settings": {
      "report-timezone": "Asia/Calcutta"
    }
  }
}

@shrey Yes, I cannot reproduce, so there's something else going on.

@flamber
So, i have now created a clone of the original table and removed all the records where the json column was either null or {}.
The remaining data with the new column is now being interpreted correctly.

The issue now remains that how to make it work for a table where the JSON field contains null as well as {} data?

@shrey I don't understand. Please provide enough JSON to reproduce.

@flamber
can i share the data with you via PM?

@shrey Try doing a bit more debugging to figure out where the problem comes from. Then sanitize your data and make it as simple as possible to reproduce.

@flamber

This is the exact table in the Postgres DB that is not working:

Columns:

  • id: int8
  • device_meta: jsonb

@flamber is this working on your end?

@flamber
This table does not work either:

The jsonb column here contains only 2 types of values: null and the json data example shared above.

Have finally figured out what works: Set all null values to {}.

But, this is isn't really an option for a live database.

Where to report this bug?

@shrey You are hitting this issue:
https://github.com/metabase/metabase/issues/11700 - upvote by clicking :+1: on the first post

Thanks for the link.

Although, that issue might not get resolved in time.
Is there any other manual procedure to fix such an issue, in the meanwhile (besides editing the data in the database) ?

@shrey Create a view on the database, which replaces null with {}