http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> , does the `flat_json` feature also...
# questions-and-troubleshooting
j
@Rocky , does the 
flat_json
feature also work with nested jsons?
r
Yes, the 
flat_json
feature works with nested JSON structures, but it has specific limitations on how deep the "flattening" goes: * Top-level Extraction: 
flat_json
detects common fields at the top level of your JSON object and extracts them into separate columnar storage. If a top-level key contains a nested object (e.g., 
{"metadata": {"id": 1, "owner": "admin"}}
), the entire 
metadata
object is extracted as a sub-column of type 
JSON
. * No Recursive Flattening: It does not currently perform recursive flattening. In the example above, it will not create separate scalar columns for 
metadata.id
or 
metadata.owner
. Those inner fields remain inside the extracted 
metadata
JSON sub-column. * Array Limitation: The feature only supports materializing common keys within JSON objects. It cannot extract or flatten elements or keys inside JSON arrays. * Type Inference (v3.3.3+): Starting from version 3.3.3, StarRocks automatically infers types for the extracted fields (like 
BIGINT
, 
DOUBLE
, or 
STRING
). If a field's type is complex (like a nested object), it is inferred as a 
JSON
type and stored accordingly. How to verify You can check which sub-fields have been extracted and their inferred types by running:
Copy code
sql
SELECT flat_json_meta(your_json_column) FROM your_table[_META_];
If you see a result like 
["user_id(BIGINT)", "info(JSON)"]
, it means 
user_id
was flattened into a scalar column, but the nested 
info
object was extracted as a single JSON sub-column. Summary of support: Feature | Supported? Top-level Object Keys | Yes (extracted as scalar or JSON) Deeply Nested Keys | No (stay inside their parent JSON sub-column) JSON Arrays | No (keys inside arrays are not flattened) For scenarios where you need maximum performance on a specific deeply nested field, you should use Generated Columns to manually extract that field into its own typed column. References • using_starrocks/Flat_json.md • administration/management/BE_configuration.md
j
@Rocky Is there a source for this?
No Recursive Flattening: It does not currently perform recursive flattening.
r
The statement regarding the lack of recursive flattening is derived from the official Flat JSON documentation and its technical specifications. While the term "recursive" isn't used in a negative sense in the docs, the behavior is explicitly defined by the following points: 1. The "Feature Limitations" Section The official documentation explicitly states:
"Flat JSON can only support materializing common keys in JSON Objects, not keys in JSON Arrays."
This confirms that the flattening logic does not descend into arrays to extract nested structures. 2. Inferred Types for Nested Objects (v3.3.3+) According to the "Version Notes," StarRocks performs type inference during extraction. If a top-level key contains a nested object, it cannot be mapped to a scalar type (like 
BIGINT
or 
STRING
):
"Unrecognized types will be inferred as JSON types, and reserved field columns will be stored as JSON types."
3. Official Usage Examples In the official Flat JSON guide, StarRocks provides a specific example where a JSON contains a nested object: * Input JSON: 
{"Object":{"c":"d"}, ...}
* Result of `flat_json_meta`: 
["Object(JSON)", ...]
If recursive flattening were supported, the meta-information would show the inner path (e.g., 
Object.c(VARCHAR)
). Instead, it shows that the entire 
Object
is treated as a single sub-column of type 
JSON
. Summary Because StarRocks treats nested objects as a single 
JSON
-typed sub-column rather than breaking them down into further scalar columns, the feature is effectively limited to top-level field extraction. For fields nested deeper than the first level, StarRocks recommends using Generated Columns, which allow you to manually specify a path (e.g., 
data->'metadata'->'id'
) to be materialized into a dedicated, say, 
BIGINT
column for maximum performance. References • using_starrocks/Flat_json.md
Since the 
flat_json
feature does not support recursive flattening of nested structures, the standard and most effective alternative is to use Generated Columns. This approach allows you to manually "promote" a specific nested field to its own typed column, which StarRocks can then index and optimize like a regular column. 1. The Strategy: Generated Columns A Generated Column materializes a specific path from your JSON data into a dedicated column. Because this column has a fixed type (e.g., 
INT
, 
STRING
), StarRocks can apply standard optimizations. Example: If you have a JSON column 
data
with a nested field `{"user": {"id": 123}}`:
Copy code
sql
ALTER TABLE your_table 
ADD COLUMN user_id BIGINT AS get_json_int(data, '$.user.id');
2. Indexing the Nested Field Once you have created a Generated Column for your nested field, you can apply several types of indexes to it for performance: * Bloom Filter Index: Ideal for high-cardinality nested fields (like 
id
or 
uuid
) where you perform equality checks (
=
, 
IN
). _ _Command:* 
ALTER TABLE your_table SET ("bloom_filter_columns" = "user_id");
* Bitmap Index: Best for low-cardinality nested fields (like 
category
or 
status
). _ _Command:* 
CREATE INDEX idx_status ON your_table (status_col) USING BITMAP;
* Inverted Index (v3.3+): If the nested field contains text that you need to perform keyword searches on, you can apply an Inverted Index to the generated 
STRING
column. * Prefix Index: If you create the table with the generated column as part of the 
ORDER BY
(Sorting Key), StarRocks will use its primary sparse index to skip data. 3. Key Advantages * Automatic Query Rewrite: You don't need to change your SQL. If you query 
SELECT * FROM table WHERE get_json_int(data, '$.user.id') = 123
, the StarRocks optimizer will automatically detect the Generated Column and read from the materialized 
user_id
column instead. * Vectorized Execution: Unlike raw JSON extraction which is row-based and slow, Generated Columns are stored columnarly and support StarRocks' vectorized execution engine. * Storage Efficiency: Since version 3.1+, adding a Generated Column is a "fast" operation that doesn't always require rewriting the entire data file immediately (it can be computed on the fly until the next compaction/load). Summary Recommendation If you have a high-performance requirement for a deeply nested field, do not rely on 
flat_json
. Instead: 1. Extract the field with a Generated Column. 2. Index that column with a Bloom Filter (for IDs) or Inverted Index (for text). References • sql-reference/sql-statements/generated_columns.md • using_starrocks/Flat_json.md • table_design/indexes/inverted_index.md References • using_starrocks/Flat_json.md • table_design/indexes/inverted_index.md • sql-reference/data-types/semi_structured/JSON.md
j
@Rocky Reference for this?
Automatic Query Rewrite: You don't need to change your SQL. If you query 
SELECT * FROM table WHERE get_json_int(data, '$.user.id') = 123
, the StarRocks optimizer will automatically detect the Generated Column and read from the materialized 
user_id
column instead.
r
The reference for this behavior is the official StarRocks documentation on Generated Columns, specifically the section on Query Rewrites. According to the documentation:
"If the expression in a query matches the expression of a generated column, the optimizer automatically rewrites the query to directly read the values of the generated column."
Key Details from the Docs: * Version Support: This feature was introduced in StarRocks v3.1. * Mechanism: The Cost-Based Optimizer (CBO) analyzes the query. If it finds a function call (like 
get_json_int(data, '$.user.id')
) that exactly matches the expression used to define a generated column, it transparently substitutes that part of the plan to read from the pre-computed column on disk. * Verification: You can verify this by running 
EXPLAIN
on your query. In the 
OlapScanNode
section of the execution plan, you will see the generated column name in the 
OUTPUT EXPRS
instead of the raw JSON function. Example provided in the documentation: If you create a table with a generated column:
Copy code
sql
CREATE TABLE test_tbl (
    data_json JSON,
    user_id String AS json_string(json_query(data_json, "$.a"))
) ...;
A query using the raw expression:
Copy code
sql
SELECT json_string(json_query(data_json, "$.a")) FROM test_tbl;
Will be automatically rewritten to:
Copy code
sql
SELECT user_id FROM test_tbl;
References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_int.md References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_int.md
5 Views
Open in Slack
PreviousNext
Powered by