split_json

SPLIT_JSON

Splits a JSON array or object into a relational table format.

  • Parameters:

    • p_string (str) – The JSON input string (e.g., '[{"a": 1}, {"a": 2}]') or an expression.

    • p_expression (bool) – Set to True if p_string is an expression to be evaluated.

    • p_fieldnames (str) – Space-separated list of Level 1 field names to extract. If None, the attributes of the first record are used automatically.

    • p_root (str) – Path to the root entry if the array is nested (e.g., 'root.first').

    • p_strict_col_count (bool) – If True, rows with missing columns will raise an error.

  • returns: emits – Multiple columns as defined in the EMITS clause.

  • raises RequestException: In case of invalid JSON or parsing errors.

Examples

Basic array splitting with field selection

1select split_json('[{"a": "a","B": "c"}, {"a": "b", "B": "d"}]', false, 'a B', null, true)
2emits (a varchar(10), b varchar(10));

Nested JSON using p_root

1select split_json('{"root": {"first": [{"a": "a"}]}}', false, 'a', 'root.first', true)
2emits (a varchar(10));

Handling special characters in JSON data

1select split_json(json_data, false, 'a B', null, true)
2emits (a varchar(10), b varchar(10))
3from (select '{"a": "a","B": "c\nmm"}' as json_data);

Extracting simple arrays

1select split_json('["a","b","c"]', false, 'DATA_ROW', null, true)
2emits (data_row varchar(10));