@Rocky I am using Metabase to connect and query starrocks. both are on K8s. In metabase I had to configure the connection to starrocks as a

mysql

connection. When trying to view data in a table which has a JSON column, I get this error in metabase:

Getting analyzing error from line 2, column 43 to line 2, column 105. Detail message: No matching function with signature: json_extract(json, varchar).

How can I overcome this problem ?

The error occurs because Metabase, when using the standard MySQL driver, expects the database to support the

json_extract(json_doc, path)

function. While StarRocks is highly compatible with MySQL, its native

JSON

data type uses different function signatures (primarily

json_query

and the arrow operator

->

) and may not have a

json_extract

function that accepts a native

JSON

type argument in your specific version. To overcome this, you can use one of the following solutions: 1. Use Generated Columns (Recommended) This is the most efficient and "Metabase-friendly" way. You can create a virtual or persisted column that extracts the specific value from the JSON. Metabase will see this as a regular

VARCHAR

or

INT

column and won't attempt to use

json_extract

.

sql
-- Example: Extracting 'user_id' from a JSON column named 'data'
ALTER TABLE your_table 
ADD COLUMN user_id VARCHAR 
GENERATED ALWAYS AS (data->'$.user_id');

* Pros: High performance (StarRocks optimizes these), and Metabase treats them like any other field. * Cons: Requires a schema change. 2. Create a View If you cannot change the base table schema, create a view that flattens the JSON fields using StarRocks-native syntax. Point Metabase to this view instead of the raw table.

sql
CREATE VIEW your_table_view AS
SELECT 
    id,
    data->'$.field1' AS field1,
    data->'$.field2' AS field2
FROM your_table;

Inside the view, you use the arrow operator (

->

) or

json_query()

, which StarRocks supports for its native JSON type. 3. Use Native SQL Queries in Metabase If you are writing custom SQL in Metabase, avoid the standard "Simple Question" builder for JSON columns. Instead, write a Native Query (SQL) and use the StarRocks-supported syntax: * Arrow Operator:

SELECT data->'$.name' FROM table

* JSON Query:

SELECT json_query(data, '$.name') FROM table

4. Cast JSON to VARCHAR (If using older StarRocks versions) If your StarRocks version has a

json_extract

function but it only accepts

VARCHAR

inputs (and fails on the native

JSON

type), you can try casting the column in your SQL:

sql
SELECT json_extract(CAST(data AS VARCHAR), '$.path') FROM table;

Note: This is less efficient than using native

JSON

operators. Summary of Differences Feature | MySQL Syntax | StarRocks Native Syntax Extraction |

json_extract(doc, '$.path')

|

doc->'$.path'

or

json_query(doc, '$.path')

String Value |

json_unquote(...)

|

get_json_string(doc, '$.path')

Rocky's Tip: When connecting Metabase to StarRocks, ensure you've added

tinyInt1isBit=false

to your JDBC connection string options in Metabase to avoid other common type-type mapping issues! References • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/json_query.md

How about adding the starrocks driver to metabase ? Would that work? Do you have documentation on how one would go about something like that ?

Yes, adding a dedicated StarRocks driver to Metabase is possible and is often the best long-term solution for compatibility issues. Because Metabase is open-source, you can use community-built drivers to handle the specific SQL dialect differences between StarRocks and MySQL. 1. Does it work? Yes. While the official StarRocks documentation suggests using the MySQL driver, a community-maintained StarRocks Metabase Driver exists (specifically the one by Carbon-Arc). This driver addresses several issues that the standard MySQL driver has with StarRocks: * Metadata Sync: It fixes errors like

No viable statement for input 'SHOW GRANTS FOR CURRENT_USER'

. * Multi-Catalog Support: It allows you to browse external catalogs (Hive, Iceberg, etc.) which the MySQL driver cannot handle. * Dialect Compatibility: It bypasses MySQL-specific assumptions that often cause the

json_extract

or

time_format

errors you are seeing. 2. How to install it on Kubernetes Since your Metabase is running on K8s, you need to get the driver's

.jar

file into the Metabase

plugins

directory. Step A: Get the Driver 1. Go to the Carbon-Arc/metabase-starrocks-driver GitHub repository. 2. Download the latest release (e.g.,

starrocks.metabase-driver.jar

). Step B: Deploy to Kubernetes You have three common ways to add this file to your Metabase pod: Option 1: Custom Docker Image (Recommended for Production) Create a simple Dockerfile that wraps the official Metabase image:

dockerfile
FROM metabase/metabase:latest
# Copy the driver into the plugins folder
ADD starrocks.metabase-driver.jar /plugins/

Update your K8s deployment to use this new image. Option 2: Init Container (Easier for testing) If you use the Helm chart or a standard Deployment, you can use an

initContainer

to download the driver into a shared volume before Metabase starts:

yaml
spec:
  initContainers:
  - name: install-driver
    image: alpine
    command: ["sh", "-c", "wget -O /plugins/starrocks.metabase-driver.jar <https://github.com/Carbon-Arc/metabase-starrocks-driver/releases/download/v1.0.2/starrocks.metabase-driver.jar%22|https://github.com/Carbon-Arc/metabase-starrocks-driver/releases/download/v1.0.2/starrocks.metabase-driver.jar">]
    volumeMounts:
    - name: plugins-vol
      mountPath: /plugins
  containers:
  - name: metabase
    image: metabase/metabase:latest
    env:
    - name: MB_PLUGINS_DIR
      value: /plugins
    volumeMounts:
    - name: plugins-vol
      mountPath: /plugins
  volumes:
  - name: plugins-vol
    emptyDir: {}

3. Will this fix the JSON error? By using a dedicated StarRocks driver, Metabase's query engine understands it is talking to StarRocks. However, if the Metabase "Simple Question" builder still attempts to use

json_extract

, the error may persist because StarRocks prefers the arrow operator

->

for native JSON types. If the error remains after installing the driver: 1. Use a View: Create a view in StarRocks that extracts the JSON fields (e.g.,

SELECT data->'$.key' AS key_name FROM table

). 2. Native Query: Use the "Native Query" (SQL) editor in Metabase instead of the GUI builder. 3. Generated Columns: (Best performance) Add a generated column to your StarRocks table:

sql
    ALTER TABLE my_table ADD COLUMN my_field AS (data->'$.my_field');

Rocky's Tip: When you add the database in Metabase after installing the driver, look for "StarRocks" in the "Database type" dropdown instead of "MySQL". If you don't see it, double-check your

MB_PLUGINS_DIR

path and check the Metabase logs for "Loading plugins..." section" during startup. References • integrations/BI_integrations/Metabase.md • release_notes/release-3.2.md • integrations/BI_integrations/Querybook.md