prestodb/presto · error · JsonCastException

INVALID_CAST_ARGUMENT

INVALID_CAST_ARGUMENT

Error message

Expected a json array, but got %s

What it means

Thrown when casting a JSON value to an ARRAY type: the parser expects the top-level JSON to be an array ([...]), but encountered a different token (object, scalar, etc.). JsonToArrayCast.toArray reads the first token and, if it is neither null nor START_ARRAY, raises JsonCastException with the offending text, later wrapped as INVALID_CAST_ARGUMENT. A JSON value of a mismatched shape simply cannot become an array.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/JsonToArrayCast.java:94

        BlockBuilderAppender elementAppender = BlockBuilderAppender.createBlockBuilderAppender(arrayType.getElementType());
        MethodHandle methodHandle = METHOD_HANDLE.bindTo(arrayType).bindTo(elementAppender);
        return new BuiltInScalarFunctionImplementation(
                true,
                ImmutableList.of(valueTypeArgumentProperty(RETURN_NULL_ON_NULL)),
                methodHandle);
    }

    @UsedByGeneratedCode
    public static Block toArray(ArrayType arrayType, BlockBuilderAppender elementAppender, SqlFunctionProperties properties, Slice json)
    {
        try (JsonParser jsonParser = createJsonParser(JSON_FACTORY, json)) {
            jsonParser.nextToken();
            if (jsonParser.getCurrentToken() == JsonToken.VALUE_NULL) {
                return null;
            }

            if (jsonParser.getCurrentToken() != START_ARRAY) {
                throw new JsonCastException(format("Expected a json array, but got %s", jsonParser.getText()));
            }
            BlockBuilder blockBuilder = arrayType.getElementType().createBlockBuilder(null, 20);
            while (jsonParser.nextToken() != JsonToken.END_ARRAY) {
                elementAppender.append(jsonParser, blockBuilder, properties);
            }
            if (jsonParser.nextToken() != null) {
                throw new JsonCastException(format("Unexpected trailing token: %s", jsonParser.getText()));
            }

            return blockBuilder.build();
        }
        catch (PrestoException | JsonCastException e) {
            throw new PrestoException(INVALID_CAST_ARGUMENT, format("Cannot cast to %s. %s%n%s", arrayType, e.getMessage(), truncateIfNecessaryForErrorMessage(json)), e);
        }
        catch (Exception e) {
            throw new PrestoException(INVALID_CAST_ARGUMENT, format("Cannot cast to %s.%n%s", arrayType, truncateIfNecessaryForErrorMessage(json)), e);
        }
    }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Extract the correct array subfield first: CAST(json_extract(json_col, '$.items') AS ARRAY<...>) instead of casting the whole document.
2. Guard with json_typeof/JSONEquality checks, e.g. CASE WHEN json_typeof(json_col) = 'array' THEN CAST(...) END.
3. Fix upstream data to always emit arrays for this field.
4. Wrap in try_cast(...) to get NULL instead of a query failure for heterogeneous data.

Example fix

// before
SELECT CAST(payload AS ARRAY<BIGINT>) FROM t; -- payload = '{"vals":[1,2]}'

// after
SELECT CAST(json_extract(payload, '$.vals') AS ARRAY<BIGINT>) FROM t;

-- Validate top-level JSON type before casting
SELECT CASE
    WHEN json_typeof(payload) = 'array'
    THEN CAST(payload AS ARRAY<BIGINT>)
    ELSE NULL
END AS vals
FROM t;

// SQL: use try_cast for graceful degradation
SELECT try_cast(payload AS ARRAY<BIGINT>) FROM t;

When it happens

Trigger: Executing CAST(json_col AS ARRAY<T>) where json_col contains a JSON object ('{"a":1}'), a bare scalar ('42', '"text"', 'true'), or any non-array top-level value; toArray in JsonToArrayCast.java rejects the first non-START_ARRAY token.

Common situations: Casting a column that is usually an array but occasionally holds an object or scalar; querying JSON from external systems where the schema is not guaranteed; accidental passing of the whole JSON document instead of a subfield that is an array.

Related errors

• INVALID_CAST_ARGUMENT
• INVALID_CAST_ARGUMENT
• INVALID_CAST_ARGUMENT
• NOT_SUPPORTED
• createArrayOf

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/dce5210452162020. Report an issue: GitHub.