All Products
Search

This Product

    E-MapReduce:Import JSON data

    Document Center

    E-MapReduce:Import JSON data

    Last Updated:Mar 26, 2026

    Doris supports importing JSON data through Stream Load and Routine Load. This topic covers the supported JSON formats, parameters, and how JSON paths, columns, and JSON root work together.

    Supported import methods

    JSON data can be imported using only these two methods:

    • Stream Load: imports a local JSON file

    • Routine Load: subscribes to and consumes JSON-formatted messages from Kafka

    Other import methods do not support JSON format.

    Supported JSON formats

    Choose a JSON format based on your use case:

    Format Best for Required parameter
    Array as root node Stream Load batch imports with multiple rows strip_outer_array: true
    Object as root node Routine Load (one Kafka message = one row) None
    Newline-delimited objects Stream Load batch imports in NDJSON style read_json_by_line: true

    Array as root node

    An array is the root node. Each element represents one row:

    [
    { "id": 123, "city": "beijing" },
    { "id": 456, "city": "shanghai" },
    ...
]

    Set strip_outer_array to true. Doris expands the array and parses each object as a row.

    Object as root node

    A single object represents one row:

    { "id": 123, "city": "beijing" }

    Nested objects are also valid:

    { "id": 123, "city": { "name": "beijing", "region": "haidian" } }

    This format is typically used with Routine Load, where each Kafka message maps to one row.

    Newline-delimited objects

    Multiple objects separated by a delimiter, one object per line:

    { "id": 123, "city": "beijing" }
{ "id": 456, "city": "shanghai" }
...

    Set read_json_by_line to true. Configure line_delimiter to specify the delimiter — the default is \n. Doris splits the input at each delimiter and parses each object as a row.

    JSON parameters

    The following parameters control JSON import behavior.

    Parameter Default Stream Load Routine Load Description
    streaming_load_json_max_mb 100 streaming_load_json_max_mb Not applicable Maximum size (MB) of JSON data per Stream Load job. See Configuration items of backend nodes.
    strip_outer_array false strip_outer_array strip_outer_array Expand an array root node and parse each element as a row. Required for array-format JSON.
    read_json_by_line false read_json_by_line Not supported Parse each line as a separate JSON object. Required for newline-delimited format.
    jsonpaths None jsonpaths jsonpaths JSON path expressions that specify which fields to extract, and in what order.
    json_root None json_root json_root JSONPath expression pointing to the root node to extract before further parsing.
    fuzzy_parse false fuzzy_parse Not supported Speeds up import for array-format JSON. All rows must have fields in the same order.
    num_as_string — num_as_string num_as_string Parse JSON numeric values as strings.
    line_delimiter \n line_delimiter Not applicable Delimiter for newline-delimited format. Used with read_json_by_line: true.

    fuzzy_parse

    Purpose: Improves import efficiency for array-format JSON.

    Default: false

    How it works: Doris parses field order from the first row only, then accesses all subsequent rows using subscript positions instead of field name lookups. This improves import speed by 3 to 5 times.

    Constraints:

    • Stream Load only — not supported in Routine Load.

    • All rows in the array must have fields in the same order.

    • Set strip_outer_array to true when using fuzzy_parse.

    JSON path

    Use jsonpaths to extract specific fields from JSON data. Without jsonpaths, Doris matches fields by column name.

    For array-format JSON, Doris expands the array first and then processes each element. The examples below use single-object format.

    Without jsonpaths

    Doris matches fields by column name. Given a table with columns id and city:

    { "id": 123, "city": "beijing" }

    Doris matches id → 123, city → "beijing".

    If a field is absent:

    { "id": 123, "name": "beijing" }

    Doris matches id → 123, city → null (no match).

    With jsonpaths

    Specify an ordered list of JSONPath expressions. Each expression maps to one column:

    ["$.id", "$.name"]
    ["$.id.sub_id", "$.name[0]", "$.city[0]"]

    Doris extracts fields in the order specified and maps them to table columns by position.

    Non-primitive data types

    Doris does not support Array or Map as column types. When a matched value is an object or array, Doris converts it to a JSON string:

    Given:

    { "id": 123, "city": { "name": "beijing", "region": "haidian" } }

    With jsonpaths: ["$.city"], the match is:

    { "name": "beijing", "region": "haidian" }

    This is stored as the string:

    "{'name':'beijing','region':'haidian'}"

    Match failures

    When a field path does not exist, Doris returns null. Doris does not distinguish between a null value in the data and a failed match. Given:

    { "id": 123, "name": null }

    Both ["$.id", "$.name"] and ["$.id", "$.info"] return 123 and null.

    Exact match failure: If all columns fail to match, Doris marks the entire row as an error row rather than writing all-null values. Given:

    { "id": 123, "city": "beijing" }

    With an invalid path like ["$.ad", "$.infa"], or if neither id nor city exists in the table schema, Doris marks the row as an error row.

    JSON path and columns

    jsonpaths and columns serve different roles and are applied sequentially:

    1. `jsonpaths` extracts fields from the JSON source and reorders them into a positional dataset.

    2. `columns` maps that positional dataset to the target table columns, with optional transformations.

    This separation lets you extract fields in any order from the JSON and map them to table columns by name.

    Example data:

    {"k1": 1, "k2": 2}

    Table schema:

    k2 int, k1 int

    Import statement 1 — jsonpaths only, no columns:

    curl -v --location-trusted -u root: \
  -H "format: json" \
  -H "jsonpaths: [\"$.k2\", \"$.k1\"]" \
  -T example.json \
  http://127.0.0.1:8030/api/db1/tbl1/_stream_load

    jsonpaths extracts k2 then k1 in that order. Without columns, Doris maps by position to the table schema (k2 int, k1 int), so the first extracted value goes to k2 and the second to k1. Result:

    +------+------+
| k1   | k2   |
+------+------+
|    2 |    1 |
+------+------+

    The JSON value of k2 (2) ended up in the k1 column because positional mapping does not account for name differences. Use columns to fix this.

    Import statement 2 — jsonpaths with columns:

    curl -v --location-trusted -u root: \
  -H "format: json" \
  -H "jsonpaths: [\"$.k2\", \"$.k1\"]" \
  -H "columns: k2, k1" \
  -T example.json \
  http://127.0.0.1:8030/api/db1/tbl1/_stream_load

    jsonpaths extracts [$.k2, $.k1] → positions [0, 1]. columns: k2, k1 assigns position 0 to column k2 and position 1 to column k1 by name. Result:

    +------+------+
| k1   | k2   |
+------+------+
|    1 |    2 |
+------+------+

    Import statement 3 — column transformation:

    curl -v --location-trusted -u root: \
  -H "format: json" \
  -H "jsonpaths: [\"$.k2\", \"$.k1\"]" \
  -H "columns: k2, tmp_k1, k1 = tmp_k1 * 100" \
  -T example.json \
  http://127.0.0.1:8030/api/db1/tbl1/_stream_load

    The columns expression assigns position 1 to a temporary column tmp_k1, then derives k1 as tmp_k1 * 100. Result:

    +------+------+
| k1   | k2   |
+------+------+
|  100 |    2 |
+------+------+

    JSON root

    Use json_root to specify the root node from which Doris starts parsing. This is useful when the JSON payload wraps the actual data inside a nested structure.

    For array-format JSON, Doris expands the array first and then processes each element. The examples below use single-object format.

    Without json_root: Given a table with columns id and city:

    { "id": 123, "name": { "id": "321", "city": "shanghai" } }

    Doris searches the top-level object and matches id → 123, city → null.

    With json_root: Set -H "json_root: $.name". Doris extracts the name node:

    { "id": "321", "city": "shanghai" }

    This becomes the new input. Doris matches id → 321, city → "shanghai".

    NULL and default values

    When a column is absent from a JSON row, Doris writes null — not the column's default value. To apply a default value, use jsonpaths and columns with an explicit expression.

    Example data:

    [
    {"k1": 1, "k2": "a"},
    {"k1": 2},
    {"k1": 3, "k2": "c"}
]

    Table schema: k1 int null, k2 varchar(32) null default "x"

    Without explicit column mapping:

    curl -v --location-trusted -u root: \
  -H "format: json" \
  -H "strip_outer_array: true" \
  -T example.json \
  http://127.0.0.1:8030/api/db1/tbl1/_stream_load

    Result — the missing k2 value is NULL, not the default "x":

    +------+------+
| k1   | k2   |
+------+------+
|    1 |    a |
+------+------+
|    2 | NULL |
+------+------+
|    3 |    c |
+------+------+

    Doris cannot determine which table column is missing from context alone, so it writes null. To apply the default value, explicitly reference the column and use ifnull:

    curl -v --location-trusted -u root: \
  -H "format: json" \
  -H "strip_outer_array: true" \
  -H "jsonpaths: [\"$.k1\", \"$.k2\"]" \
  -H "columns: k1, tmp_k2, k2 = ifnull(tmp_k2, 'x')" \
  -T example.json \
  http://127.0.0.1:8030/api/db1/tbl1/_stream_load

    jsonpaths extracts k2 into tmp_k2. When tmp_k2 is null (field absent), ifnull substitutes 'x'. Result:

    +------+------+
| k1   | k2   |
+------+------+
|    1 |    a |
+------+------+
|    2 |    x |
+------+------+
|    3 |    c |
+------+------+

    What's next