---
title: JSON formatting troubleshooting
meta:
  description: Common issues and solutions for JSON formatting in ClickHouse and Tinybird.
---

# 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
```json
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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

## Related documentation

- [JSON functions](/sql-reference/functions/json-functions)
- [Data types](/sql-reference/data-types)
- [Data source configuration](/forward/get-data-in/data-sources)