What causes "Cannot convert '%s' to JSON" in prestodb/presto?

Locate the offending row: SELECT id FROM t WHERE json_typeof(col) IS NULL or validate values with col::text::jsonb

prestodb/presto · error · PrestoException

INVALID_FUNCTION_ARGUMENT

INVALID_FUNCTION_ARGUMENT

Error message

Cannot convert '%s' to JSON

What it means

jsonReadMapping converts PostgreSQL json/jsonb values from the wire format into Presto JSON slices using a Jackson streaming parser. If any exception occurs while parsing/rewriting the value (including trailing characters after a valid JSON document), jsonParse throws a PrestoException with function code INVALID_FUNCTION_ARGUMENT naming the offending bytes. The original exception is deliberately not chained, only its message is surfaced via the value itself.

Source

Thrown at presto-postgresql/src/main/java/com/facebook/presto/plugin/postgresql/PostgreSqlClient.java:219

    {
        return createSliceReadMapping(
                jsonType,
                (resultSet, columnIndex) -> jsonParse(utf8Slice(resultSet.getString(columnIndex))));
    }

    public static Slice jsonParse(Slice slice)
    {
        try (JsonParser parser = createJsonParser(JSON_FACTORY, slice)) {
            byte[] in = slice.getBytes();
            SliceOutput dynamicSliceOutput = new DynamicSliceOutput(in.length);
            SORTED_MAPPER.writeValue((OutputStream) dynamicSliceOutput, SORTED_MAPPER.readValue(parser, Object.class));
            // nextToken() returns null if the input is parsed correctly,
            // but will throw an exception if there are trailing characters.
            parser.nextToken();
            return dynamicSliceOutput.slice();
        }
        catch (Exception e) {
            throw new PrestoException(INVALID_FUNCTION_ARGUMENT, format("Cannot convert '%s' to JSON", slice.toStringUtf8()));
        }
    }

    public static JsonParser createJsonParser(JsonFactory factory, Slice json)
            throws IOException
    {
        // Jackson tries to detect the character encoding automatically when using InputStream
        // so we pass an InputStreamReader instead.
        return factory.createParser(new InputStreamReader(json.getInput(), UTF_8));
    }

    private ReadMapping uuidReadMapping()
    {
        return createSliceReadMapping(
                uuidType,
                (resultSet, columnIndex) -> uuidSlice((UUID) resultSet.getObject(columnIndex)));
    }

View on GitHub (pinned to 55bb57d202)

Solutions

Locate the offending row: SELECT id FROM t WHERE json_typeof(col) IS NULL or validate values with col::text::jsonb
Fix/clean the stored JSON data in PostgreSQL (re-insert valid JSON)
Cast the column to text and read it as VARCHAR instead of JSON if the values are not guaranteed valid
Check client encoding settings on the writing application (UTF-8 end-to-end)

Example fix

// before
SELECT json_col FROM t;  -- fails on malformed row
// after
SELECT json_col::text AS json_col FROM t WHERE jsonb_typeof(json_col) = 'object';

Defensive patterns

Strategy: validation

Validate before calling

sql
SELECT id FROM my_table
WHERE jsonb_typeof(col::jsonb) IS NULL OR col::text NOT LIKE '{%';
-- rows returned hold invalid JSON

Try / catch

java
try (ResultSet rs = stmt.executeQuery("SELECT json_col FROM t")) {
    while (rs.next()) {
        try {
            consume(rs.getObject("json_col"));
        } catch (PrestoException e) {
            if (e.getErrorCode() == INVALID_FUNCTION_ARGUMENT.toErrorCode().getCode()) {
                log.warn("Skipping invalid JSON row");
                continue;
            }
            throw e;
        }
    }
}

Prevention

Use jsonb columns with constrained inserts so PostgreSQL rejects invalid JSON at write time
Validate JSON in the writing application before insert
Enforce UTF-8 encoding end-to-end
Schedule data-quality checks scanning json columns for parse failures

When it happens

Trigger: Reading a json/jsonb column whose value Jackson cannot parse in the expected format — corrupt/malformed JSON stored by a non-Presto writer, invalid UTF-8 bytes, or a value written with extensions Jackson's configured JsonFactory rejects.

Common situations: Data inserted into PostgreSQL from applications that stored invalid JSON in text columns cast to json, encoding mismatches (latin1 vs UTF-8) corrupting multi-byte characters, or manually edited rows with trailing garbage.

Related errors

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