http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I got a question, if in starRocks n...
# questions-and-troubleshooting
e
@Rocky I got a question, if in starRocks native table I got a column of type Json and in s3 iceberg its stored as a struct. when fetching data that also in starRocks and also in s3 iceberg , how can we query these json column (arrow notation for json in starRocks and dot notation for struct column in iceberg) - variant type is problematic since its new in starRocks and we are scared that the support is not great and might have some bugs. do you have any advice for us ?
r
e
@Rocky I got a question, if in starRocks native table I got a column of type Json and in s3 iceberg its stored as a struct. when fetching data that also in starRocks and also in s3 iceberg , how can we query these json column (arrow notation for json in starRocks and dot notation for struct column in iceberg) - variant type is problematic since its new in starRocks and we are scared that the support is not great and might have some bugs. do you have any advice for us ?
r
To query a StarRocks native table (
JSON
type) and an Iceberg table (
STRUCT
type) using a consistent syntax without using the 
VARIANT
type, you should use Views to normalize the data types. Since 
JSON
and 
STRUCT
have different access patterns (arrow vs. dot notation), you have two primary options for unification: Option 1: Unify on JSON Syntax (Arrow Notation 
->
) This is usually the easiest approach because StarRocks can easily convert a 
STRUCT
to a 
JSON
string. 1. For the Iceberg Table: Create a view that converts the 
STRUCT
column to 
JSON
using the 
to_json()
function.
Copy code
sql
    CREATE VIEW iceberg_unified_vw AS
    SELECT
        id,
        to_json(iceberg_struct_col) AS unified_col
    FROM iceberg_catalog.db.table;
2. Querying: Now you can use arrow notation for both:
Copy code
sql
    -- Native Table
    SELECT json_col->'field' FROM native_table;
    -- Iceberg View
    SELECT unified_col->'field' FROM iceberg_unified_vw;
Option 2: Unify on STRUCT Syntax (Dot Notation 
.
) If you prefer dot notation, you must normalize the native 
JSON
column into a 
STRUCT
. Note that 
STRUCT
requires a fixed schema, so you must know the fields in advance. 1. For the Native Table: Create a view that extracts the JSON fields into a 
named_struct
.
Copy code
sql
    CREATE VIEW native_unified_vw AS
    SELECT
        id,
        named_struct(
            'field1', cast(json_col->'field1' as INT),
            'field2', cast(json_col->'field2' as STRING)
        ) AS unified_col
    FROM native_table;
2. Querying: Now you can use dot notation for both:
Copy code
sql
    -- Iceberg Table
    SELECT iceberg_struct_col.field1 FROM iceberg_table;
    -- Native View
    SELECT unified_col.field1 FROM native_unified_vw;
Recommended Strategy: Generated Columns If you frequently query specific fields from the native JSON column, the best practice in StarRocks is to use Generated Columns. This avoids the performance overhead of parsing JSON at query time and allows you to query the fields as standard columns (neither dot nor arrow needed).
Copy code
sql
ALTER TABLE native_table 
ADD COLUMN field1 INT AS (cast(json_col->'field1' as INT));
You can then query 
field1
directly, and StarRocks will automatically use the optimized pre-extracted value. Summary Comparison Feature | Native JSON | Iceberg STRUCT Access Syntax | Arrow 
->
or 
json_query()
| Dot 
.
Schema | Dynamic | Fixed Unification Tool | 
named_struct()
| 
to_json()
Advice: Use Option 1 (Unify on JSON) via a view if your schema changes often. Use Generated Columns if performance is a priority for a few specific fields. References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/data-types/semi_structured/STRUCT.md
e
@Rocky if we go with this approach: Option 1: Unify on JSON Syntax (Arrow Notation 
->
)
Copy code
CREATE VIEW iceberg_unified_vw AS
    SELECT
        id,
        to_json(iceberg_struct_col) AS unified_col
    FROM iceberg_catalog.db.table;
is it possible do say select * to_json(iceberg_struct_col) AS unified_col ? another question if the column in starRocks is of type Json, is saving it in iceberg v2 in struct the best format or do you have another recommendation ?
r
Yes, you can use 
SELECT *
with additional expressions in StarRocks. To answer your specific questions: *1. Using 
SELECT *
with `to_json()`* You can definitely use 
SELECT *
alongside your expression. However, if you want the view to have the exact same column names as your native table (e.g., both tables having a column named 
data_col
), you can use the 
EXCLUDE
syntax (available since StarRocks v3.3) to drop the original 
STRUCT
and replace it with the 
JSON
version. Example:
Copy code
sql
CREATE VIEW iceberg_unified_vw AS
SELECT 
    * EXCLUDE (iceberg_struct_col), 
    to_json(iceberg_struct_col) AS iceberg_struct_col 
FROM iceberg_catalog.db.table;
This gives you a view where 
iceberg_struct_col
is now of type 
JSON
, allowing you to use arrow notation (
->
) just like your native tables. ────────── 2. Is 
STRUCT
the best format for Iceberg v2? If you are avoiding the 
VARIANT
type for now, 
STRUCT
is indeed the best recommendation for storing JSON-like data in Iceberg for the following reasons: * Performance (Pruning): Because Iceberg stores 
STRUCT
fields as individual columns in the underlying Parquet files, StarRocks can perform field-level pruning. If you query 
struct_col.a
, StarRocks only reads the data for 
a
, rather than the whole JSON blob. * Storage Efficiency: 
STRUCT
stores values in their native binary types (e.g., integers as 4-8 bytes), whereas a 
JSON
string stores everything as text, which is significantly larger and slower to parse. * Stability: 
STRUCT
has been supported in Iceberg and StarRocks for a long time and is much more mature than the newer 
VARIANT
type. The "Blob" Alternative: The only other alternative is storing the JSON as a 
VARCHAR
(String). * Pros: Total schema flexibility (no 
ALTER TABLE
needed when new fields arrive). * Cons: Extremely slow for large datasets because StarRocks must read and parse the entire string for every row just to find one field. Summary Recommendation: * Stick with 
STRUCT
 if your JSON schema is relatively stable or if query performance is a priority. * Use the 
EXCLUDE
view trick above to provide a unified 
JSON
interface (arrow notation) to your users so they don't have to care if the underlying storage is native StarRocks or S3 Iceberg. Important Version Note If you are on StarRocks v3.3 or later, definitely use the 
SELECT * EXCLUDE (...)
syntax as it makes managing these "unification views" much easier when tables have many columns. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/data-types/semi_structured/JSON.md • data_source/catalog/iceberg/iceberg_catalog.md References • data_source/catalog/maxcompute_catalog.md • data_source/catalog/paimon_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/data-types/semi_structured/VARIANT.md
e
@Rocky when moving a json column value into iceberg struct column -> if the json has some values that are not defined in the struct column definition we will an error, right ? does variant type solve this ? and is starRocks support for variant type mature enough for production use ?
r
To answer your concerns about schema mismatches, the 
VARIANT
type, and production readiness: 1. Does inserting JSON into a STRUCT cause an error? When you move data from a StarRocks 
JSON
column into an Iceberg 
STRUCT
column using a 
CAST
or `INSERT`: * Extra Fields: If your JSON contains keys not defined in the target 
STRUCT
, those fields are silently dropped. They are not stored in the Iceberg table, but the operation does not fail. * Missing Fields: If the 
STRUCT
expects a field that is missing from the JSON, StarRocks will insert a 
NULL
for that field. * Data Type Mismatch: If a field exists in both but the types are incompatible (e.g., a JSON string 
{"id": "abc"}
being cast to a struct field 
id INT
), the row will either become 
NULL
or the transaction will fail if 
enable_insert_strict
is set to 
true
. 2. Does the 
VARIANT
type solve this? Yes. The 
VARIANT
type (available from StarRocks v4.1 for Iceberg) is designed specifically to solve the "rigid schema" problem of 
STRUCT
. * It stores the entire JSON-like object in a binary format without requiring you to define sub-fields. * It preserves all "extra" fields automatically. _ You query it using a hybrid approach (using 
get_variant__
functions or 
variant_query
), which is much more flexible than the fixed dot-notation of a 
STRUCT
. 3. Is 
VARIANT
mature enough for production? According to the StarRocks documentation: * Maturity Level: In StarRocks v4.1, the 
VARIANT
type does not carry a "Beta" or "Experimental" tag in the documentation. Per StarRocks' own policy, features without these tags are considered Generally Available (GA) and production-ready. * The Catch: 
VARIANT
is currently only supported for Iceberg tables (Parquet format). It is not supported for StarRocks native (OLAP) tables yet. Recommendation for your scenario If you are hesitant to use 
VARIANT
but want to avoid data loss from extra JSON fields, here is the recommended path: 1. If the schema is mostly stable: Stick with STRUCT. It provides the best performance because StarRocks can use "field-level pruning" (reading only the sub-column you need). To handle "extra" fields, you can add a "catch-all" column in your Iceberg table called 
unstructured_data
(type 
STRING
or 
JSON
) to store fields that don't fit the struct. 2. If the schema is highly dynamic: Use the Iceberg 
VARIANT
type if you are on v4.1+. While it is new, it is the official GA solution for this exact problem. 3. Unified Querying (View approach): Regardless of the storage (Struct or Variant), use a View to present the data to your users. This allows you to switch the underlying storage format later without breaking user queries. Summary Comparison: Feature | Iceberg STRUCT | Iceberg VARIANT (v4.1+) Extra Fields | Dropped | Preserved Performance | Best (Sub-column pruning) | High (Optimized binary) Flexibility | Rigid (Needs 
ALTER TABLE
) | Fully Flexible Maturity | Very High | GA (but new in v4.1) References • sql-reference/data-types/semi_structured/VARIANT.md • introduction/maturity.md • sql-reference/data-types/semi_structured/STRUCT.md References • introduction/maturity.md • sql-reference/data-types/semi_structured/VARIANT.md • release_notes/release-2.4.md
e
@Rocky what about having it as string string in iceberg and doing ?
Copy code
sql
CREATE VIEW iceberg_unified_vw AS
SELECT 
    * EXCLUDE (iceberg_string_json_col), 
    to_json(iceberg_string_json) AS iceberg_struct_col 
FROM iceberg_catalog.db.table;
r
Using a 
STRING
column in Iceberg and a View to convert it to 
JSON
is a valid way to achieve "unified syntax" while preserving all fields. However, there are significant performance trade-offs you should consider. 1. Correct Syntax for your View If the data is stored as a 
STRING
(VARCHAR) in Iceberg, you must use 
parse_json()
 to convert it to a StarRocks 
JSON
type. The 
to_json()
function is meant for converting complex types like 
STRUCT
or 
ARRAY
into JSON. Your view would look like this:
Copy code
sql
CREATE VIEW iceberg_unified_vw AS
SELECT 
    * EXCLUDE (iceberg_string_col), 
    parse_json(iceberg_string_col) AS data_col -- Now both tables have 'data_col' as JSON
FROM iceberg_catalog.db.table;
With this view, you can use arrow notation (
data_col->'field'
) just like your native table. ────────── 2. The Trade-off: Performance vs. Flexibility While this approach solves the "missing fields" issue of 
STRUCT
, it introduces a performance penalty for Iceberg queries: * No Column Pruning: In a 
STRUCT
, StarRocks only reads the specific sub-field from S3. In a 
STRING
, StarRocks must download the entire JSON string for every single row, even if you only need one small field. * Parsing Overhead: 
parse_json()
is a CPU-intensive operation. StarRocks has to parse the text into a JSON object for every row during query execution. * No "Flat JSON" Optimization: StarRocks has a feature called "Flat JSON" that automatically speeds up JSON queries, but this optimization currently only works for native StarRocks tables, not for external Iceberg tables. ────────── 3. Comparison of Approaches Approach | Storage in Iceberg | Extra Fields? | Performance | Unified Syntax? STRUCT | 
STRUCT
| Dropped | Best (Pruning) | Via View (Option 2) STRING | 
VARCHAR
| Preserved | Slowest (No Pruning) | Via View (Option 1) VARIANT | 
VARIANT
| Preserved | Fast (Optimized) | Different Syntax* _\_Note: 
VARIANT
currently uses 
get_variant__
functions or its own path syntax rather than the 
->
arrow notation used for the native 
JSON
type._ Rocky's Advice 1. If performance is critical: Stick with the 
STRUCT
 approach. If you are worried about losing data, add an additional 
overflow_fields
column as a 
STRING
to catch anything that doesn't fit the strict struct schema. 2. If data flexibility is more important than speed: Use the 
STRING
+ 
parse_json
 view approach. It is the most stable and compatible way to ensure 100% of your data is preserved without using "scary" new types. 3. Regarding `VARIANT`: While it is technically GA (Generally Available) in v4.1 for Iceberg, your fear is understandable if you are on an older version or have strict stability requirements. It is a very new storage format for the ecosystem. Summary: Your proposed View approach with 
parse_json
will work perfectly for syntax unification, just be prepared for higher S3 scan costs and slower query times compared to native tables or structs. References • sql-reference/sql-functions/json-functions/json-constructor-functions/parse_json.md • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md References • using_starrocks/Flat_json.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md • sql-reference/data-types/semi_structured/JSON.md
e
@Rocky is struct and variant the same performance ? and is variant only supported in iceberg but no native starRocks table ? what happens when we query with starRocks federated query engine data in starRocks and s3 iceberg ? does it work the same (arrow notation) ?
r
Regarding your follow-up questions on performance, the 
VARIANT
type, and federated query consistency: 1. Performance: 
STRUCT
vs. 
VARIANT
 No, they do not have the same performance characteristics in StarRocks currently. * Iceberg 
STRUCT
(Faster for specific fields): Because 
STRUCT
fields are stored as separate columns in the underlying Parquet files, StarRocks performs sub-column pruning. If you query 
struct_col.sub_field
, StarRocks only reads the data for that specific sub-field from S3. * Iceberg 
VARIANT
(Slower but more flexible): As of StarRocks v4.1, the 
VARIANT
type support is for unshredded data. This means the entire binary blob for the variant is stored in one column. To extract a single field, StarRocks must read the entire blob for that row. While this is much faster than parsing a raw 
STRING
, it is slower than the surgical precision of 
STRUCT
pruning. * Summary: Use 
STRUCT
if you know your schema and want the absolute best performance. Use 
VARIANT
(v4.1+) if your schema changes frequently. 2. Is 
VARIANT
only supported in Iceberg? Yes. Currently (v4.1), the 
VARIANT
data type is only supported for external Iceberg tables (Parquet format). It is not yet supported for StarRocks native (OLAP) tables. For native tables, the high-performance semi-structured option remains the 
JSON
type (which uses a "Flat JSON" optimization under the hood). 3. Federated Queries and Arrow Notation (
->
) The arrow notation (
->
) is not a universal syntax for all semi-structured types in StarRocks. Each type has its own primary access method: Storage Type | Access Syntax | Arrow Notation Support? Native JSON | 
json_col->'field'
| Yes Iceberg STRUCT | 
struct_col.field
| No (unless converted to JSON) Iceberg VARIANT | 
get_variant_string(v_col, '$.field')
| No (uses getters or 
variant_query
) 4. How to unify them for Federated Queries To make your Iceberg data and Native data look identical to your users (so they can use 
->
everywhere), you must use a View to cast the external types to the StarRocks native 
JSON
type. Example Unified View:
Copy code
sql
CREATE VIEW iceberg_unified_vw AS
SELECT 
    -- If it's a STRUCT in Iceberg:
    to_json(iceberg_struct_col) AS unified_col,
    
    -- OR If it's a VARIANT in Iceberg (v4.1+):
    CAST(iceberg_variant_col AS JSON) AS unified_col,
    
    -- OR If it's a STRING in Iceberg:
    parse_json(iceberg_string_col) AS unified_col
FROM iceberg_catalog.db.table;
Once the view is created, users can query both your native StarRocks tables and your Iceberg tables using the exact same syntax: 
SELECT unified_col->'id' FROM iceberg_unified_vw;
Final Recommendation for Iceberg v2 Since you specifically mentioned Iceberg v2, the 
VARIANT
type is not an option (it requires Iceberg v3). For v2: 1. Storage: Use 
STRUCT
for performance or 
VARCHAR
(String) for total flexibility. 2. User Layer: Use the 
CREATE VIEW ... to_json(iceberg_struct_col)
approach to provide the arrow notation interface. This gives you the performance of 
STRUCT
pruning with the modern syntax of 
JSON
. References • sql-reference/data-types/semi_structured/VARIANT.md • sql-reference/sql-functions/json-functions/json-constructor-functions/to_json.md • sql-reference/data-types/semi_structured/STRUCT.md References • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/data-types/semi_structured/VARIANT.md
e
@Rocky are you sure that if we say put this json into a nested struct column in iceberg it ignores unknown fields and does not fail the whole operation ?
r
Yes, I am certain. When you insert data from a StarRocks 
JSON
type into a defined 
STRUCT
(whether it's at the top level or nested), StarRocks uses a schema-on-write mapping approach. Here is exactly what happens when the JSON schema doesn't match the Iceberg 
STRUCT
definition: 1. Unknown/Extra Fields (The "Ignore" Behavior) If your JSON contains keys that are not defined in your Iceberg 
STRUCT
, StarRocks silently ignores them. * Why? The 
STRUCT
type has a fixed set of sub-columns. During the 
INSERT
, StarRocks only extracts the fields it has a "slot" for. * Does it fail? No. This is not considered a data type error or an "unqualified row." It is simply a projection of the source data onto a smaller schema. The operation will proceed normally for the entire batch. 2. Missing Fields If your JSON is missing a field that the 
STRUCT
expects: * StarRocks will insert a 
NULL
into that specific sub-field. * If the 
STRUCT
sub-field is marked as 
NOT NULL
in Iceberg (rare for JSON-sourced data), only then would the operation fail. 3. Data Type Mismatches (The "Error" Behavior) The operation will only fail (or produce 
NULL
rows) if there is a type incompatibility for a field that does exist in both. _ _Example:* Your struct defines 
id
as an 
INT
, but your JSON has 
{"id": "some_string"}
. * Behavior: * If 
enable_insert_strict
is 
false
(default for many sessions): The 
id
sub-field becomes 
NULL
, and the row is still inserted. * If 
enable_insert_strict
is `true`: The row will be rejected as an "unqualified row." If the number of rejected rows exceeds your 
max_filter_ratio
, the whole job fails. Summary of Risks Scenario | Result | Failure? JSON has extra keys | Extra keys are dropped | No JSON misses a key | Sub-field becomes 
NULL
| No Type mismatch (e.g. String to Int) | Depends on 
strict_mode
| Maybe (Depends on 
max_filter_ratio
) Recommendation for "Unknown" Fields If you are worried about losing those unknown fields, the 
VARIANT
type (available in StarRocks v4.1 for Iceberg) is indeed the solution because it stores the entire object without dropping anything. If you are on an older version and must use 
STRUCT
, you can add one extra column to your Iceberg table called 
raw_json_backup
(type 
STRING
) to store the original JSON string as a safety net. References • loading/load_concept/strict_mode.md • sql-reference/data-types/semi_structured/STRUCT.md • loading/Json_loading.md • sql-reference/data-types/semi_structured/JSON.md References • sql-reference/data-types/semi_structured/JSON.md • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/STRUCT.md • loading/Json_loading.md
10 Views
Open in Slack
PreviousNext
Powered by