Errors due to Models with Unfolded JSON Columns and a GROUP BY Clause

[FilmonK]

Describe the bug

Issues using the Group By clause in conjunction with aggregate functions for JSON columns that have been unfolded.

To Reproduce

Using a postgres table with JSON unfolded.

Save it as a question and Summarize with a Group By clause

This works as expected

The SQL that is generated

SELECT
  (
    "public"."job_data_json"."job_info" # >> array [ 'state' ] :: text [ ]
  ) :: text AS "job_info → state",
  COUNT(*) AS "count"
FROM
  "public"."job_data_json"
GROUP BY
  "job_info → state"
ORDER BY
  "job_info → state" ASC

You are also able to write a native query against the table to get the same results as above.

SELECT
    job_info->>'state' AS state,
    COUNT(*) AS state_count
FROM job_data_json
GROUP BY(job_info->>'state')
ORDER BY 1

Convert that same question into a model and create the same aggregation as above.

The SQL that is generated

SELECT
  ("source"."job_info" # >> array [ 'state' ] :: text [ ]) :: text AS "job_info → state",
  COUNT(*) AS "count"
FROM
  (
    SELECT
      "public"."job_data_json"."id" AS "id",
      (
        "public"."job_data_json"."job_info" # >> array [ 'city' ] :: text [ ]
      ) :: text AS "job_info → city",
      (
        "public"."job_data_json"."job_info" # >> array [ 'salary' ] :: text [ ]
      ) :: double precision AS "job_info → salary",
      (
        "public"."job_data_json"."job_info" # >> array [ 'state' ] :: text [ ]
      ) :: text AS "job_info → state",
      "public"."job_data_json"."job_info" AS "job_info"
    FROM
      "public"."job_data_json"
  ) AS "source"
GROUP BY
  "job_info → state"
ORDER BY
  "job_info → state" ASC

Expected behavior

The expectation is for the aggregation in the model to behavior similar to the question

Logs

No response

Information about your Metabase installation

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "21+35-LTS",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "21",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "21+35-LTS",
    "os.name": "Mac OS X",
    "os.version": "13.5.1",
    "user.language": "en",
    "user.timezone": "America/Chicago"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "mongo",
      "presto-jdbc",
      "athena",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "15.3"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.4"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-10-11",
      "tag": "v0.47.4",
      "branch": "?",
      "hash": "c96dc65"
    },
    "settings": {
      "report-timezone": "UTC"
    }
  }
}

Severity

P2 - It's a block for those bringing in JSON with the intention to build models based on that data

Additional context

No response

[kulyk]

Can still reproduce on master. Steps:

1. Connect a PSQL database with a JSON column
2. New > Model > Notebook editor > Raw Data > PSQL > Table with JSON column
3. Add Count aggregation
4. Breakout by an unfolded JSON column
5. Save the model
6. Reload the page
7. The question errors

ERROR: column source.jobInfo does not exist
  Position: 125

[bshepherdson]

I can reproduce this. The trouble seems to be that the nfc_path for the column is set for the field proper, but not present on the result_metadata for a model. This is the field that indicates JSON unfolding; see metabase.driver.postgres calling lib.field/json-field?.