JSON Data Types Explained: A Developer's Quick Reference

JSON supports exactly six data types. Understanding them is essential for anyone working with APIs, configuration files, or data interchange. This reference covers each type with practical examples and explains how they map to Excel during conversion.

The Six JSON Data Types

1. Strings

Strings in JSON are sequences of characters enclosed in double quotes. They support Unicode characters and escape sequences.

Valid String Examples

"Simple text"
"Line 1\nLine 2"          // newline escape
"Tab\there"                // tab escape
"Quote: \"hello\""         // escaped quotes
"Unicode: \u00e9"          // é character
""                          // empty string

Important Rules

• Strings must use double quotes. Single quotes ('hello') are invalid JSON.
• Backslashes must be escaped as \\.
• Control characters (tabs, newlines) must use escape sequences.

Excel Behavior

Strings convert directly to text cells. Watch out for strings that look like numbers (e.g., "001234") — Excel may auto-convert them to numbers, stripping leading zeros. Also, date-like strings (e.g., "1/2/2026") may be auto-parsed as dates.

2. Numbers

JSON numbers can be integers or floating-point values. They do not have quotes around them.

Valid Number Examples

42                 // integer
-17                // negative
3.14159            // decimal
2.5e10             // scientific notation
-1.23e-4           // negative scientific

Important Rules

• No leading zeros: 042 is invalid JSON.
• No NaN, Infinity, or -Infinity — these are not valid JSON values.
• No hexadecimal notation: 0xFF is invalid.
• JavaScript's Number type uses IEEE 754 double-precision, so integers larger than 2^53 may lose precision.

Excel Behavior

Numbers map naturally to Excel number cells. Scientific notation is preserved. Very large integers (like 64-bit IDs) may lose precision in both JSON parsing and Excel — consider storing them as strings if exact values matter.

3. Booleans

JSON has two boolean literals: true and false (lowercase, no quotes).

{
  "isActive": true,
  "isDeleted": false
}

Excel Behavior

Booleans become Excel boolean cells (TRUE/FALSE). They can be used directly in Excel formulas like COUNTIF, IF, and SUMPRODUCT.

4. Null

The literal null represents an intentional absence of a value. It is different from an empty string, zero, or a missing key.

{
  "middleName": null,
  "nickname": "",
  "age": 0
}

In this example, middleName explicitly has no value, nickname is an empty string (it has a value — the empty string), and age is zero (also a value).

Excel Behavior

Null values typically become empty cells in Excel. Some converters write the text "null" instead. Our converter uses empty cells, which allows Excel formulas like ISBLANK() to detect them.

5. Arrays

Arrays are ordered lists of values, enclosed in square brackets. Array elements can be any JSON type, including other arrays and objects.

Array Types

// Array of strings
["red", "green", "blue"]

// Array of numbers
[10, 20, 30, 40]

// Array of objects
[{"name": "Alice"}, {"name": "Bob"}]

// Mixed types (valid but not recommended)
["hello", 42, true, null]

// Nested arrays
[[1, 2], [3, 4]]

Excel Behavior

• Simple arrays (strings, numbers) → joined with semicolons in a single cell
• Arrays of objects → expanded into indexed columns (items[0].name, items[1].name)
• Nested arrays → serialized as JSON strings

6. Objects

Objects are unordered collections of key-value pairs, enclosed in curly braces. Keys must be strings; values can be any JSON type.

{
  "name": "Widget",
  "price": 9.99,
  "inStock": true,
  "tags": ["sale", "new"],
  "dimensions": {
    "width": 10,
    "height": 5
  },
  "metadata": null
}

Excel Behavior

Objects are flattened into columns using dot notation. The object above would produce columns: name, price, inStock, tags, dimensions.width, dimensions.height, metadata.

Common JSON Validation Errors