JSON formatting troubleshooting

Common issues and solutions for JSON formatting in ClickHouse and Tinybird.

Common errors

CANNOT_PARSE_TEXT

Error: CANNOT_PARSE_TEXT: Cannot parse JSON

Cause: Malformed JSON data

Solution: Validate JSON before ingestion

-- Valid JSON
{"id": 123, "user_id": "456", "value": 10.5}

-- Invalid JSON (missing quotes)
{id: 123, user_id: "456", value: 10.5}

UNKNOWN_TYPE

Error: UNKNOWN_TYPE: Unknown type for JSON field

Cause: ClickHouse can't infer type from JSON data

Solution: Use explicit schema or consistent data types

-- Use explicit schema
SCHEMA >
  id UInt32,
  user_id String,
  value Float64

JSON structure issues

Nested JSON objects

Issue: Complex nested JSON structures

Solution: Use JSON functions to extract data

-- Extract from nested JSON
SELECT 
  JSONExtractString(data, 'user', 'id') as user_id,
  JSONExtractFloat(data, 'metrics', 'value') as metric_value
FROM events

Array handling

Issue: JSON arrays causing parsing issues

Solution: Handle arrays properly

-- Extract array elements
SELECT 
  JSONExtractArrayRaw(data, 'items') as items_array,
  arrayJoin(JSONExtractArrayRaw(data, 'items')) as item
FROM events

Data type mismatches

Mixed types in JSON

Issue: Same field has different types in different records

Solution: Use consistent types or handle in schema

-- Handle mixed types
SELECT 
  CASE 
    WHEN JSONHas(data, 'numeric_field') THEN JSONExtractUInt(data, 'numeric_field')
    ELSE 0
  END as safe_numeric_field
FROM events

String vs numeric fields

Issue: JSON fields that should be numeric but are strings

Solution: Convert types during extraction

-- Convert string to numeric
SELECT toUInt32(JSONExtractString(data, 'id')) as numeric_id
FROM events

Best practices

  1. Validate JSON - Check JSON format before ingestion
  2. Use consistent types - Keep field types consistent across records
  3. Handle nested data - Use JSON functions for complex structures
  4. Convert types explicitly - Don't rely on automatic type conversion
  5. Use schema hints - Specify expected types in schema