JSON

JSON (JavaScript Object Notation) is a lightweight, human-readable data-interchange format widely used in web applications and APIs.

What You Can Upload

• .json files (single objects or arrays)
• .jsonl or .ndjson files (JSON Lines format)
• .json files with one record per line, as MongoDB's mongoexport and BigQuery write them
• ZIP archives with multiple JSON files

What You Get Out

DataMeans converts your data into multiple modern formats:

How to Export / Obtain Files

1. Export data from your application as JSON
2. Or export from NoSQL database (MongoDB, CouchDB, etc.)
3. Upload directly or in a ZIP archive

Supported Features

• Arrays of objects, {"data": [...]} API responses, and single objects
• Rows given as lists ([["id", "name"], [1, "Alice"]]): a first row of text names the columns, otherwise they are named Column1, Column2 and so on
• Records keyed by id ({"-Mabc": {...}, "-Mdef": {...}}, as a Firebase export or an API keyed by id writes them): one row per record, its id in a key column
• Several collections side by side (a whole Firebase database, {"customers": [...], "orders": [...]}): a table each
• Every layout pandas' to_json writes, the default one included, and columns given as lists ({"id": [1, 2], "name": ["Ann", "Bo"]}, as R's jsonlite writes them)
• GeoJSON feature collections: one row per feature, its properties as columns, then its geometry as GeoJSON text, the geometry's type, and a point's longitude and latitude
• MongoDB exports (mongoexport, relaxed or canonical Extended JSON): an ObjectId as its text, $date as a date, $numberLong and $numberDecimal as exact numbers
• .NET dates written as /Date(1614747967123)/ (ASP.NET, WCF, Windows PowerShell 5's ConvertTo-Json) are read as dates
• All JSON data types (strings, numbers, booleans, null)
• Numbers exactly as written: an id past 16 digits keeps every digit, and 18.50 stays 18.50
• NaN, Infinity and -Infinity, which Python's json module writes, are read as empty values and counted in the conversion report
• Comments and a comma before a closing bracket, which some tools write, are passed over
• Columns in the order the file gives them, including names like 1960
• UTF-8, and UTF-16 with a byte-order mark (Windows PowerShell's Out-File); a file saved as "ANSI" is read as Windows-1252 and the report says so
• JSON Lines (.jsonl, .ndjson), and records one after another in any .json file
• Nested objects become dotted columns (address.city), and arrays become linked child tables
• Records with different fields are combined: a field missing from a record is left empty
• Column types from every record: whole numbers, decimals, yes/no, and dates in ISO form
• The preview reads the start of the file with the same reader as the conversion, so it shows the same columns, types and linked tables, however large the file

Known Limitations

• Dates are read as dates only in ISO form (2024-01-15, 2024-01-15T10:30:00Z); others stay text
• A field holding numbers in some records and text in others is kept as text, every value as written
• Objects nested more than five levels deep are kept as JSON text in one column
• Binary data must be base64-encoded
• In a file of one record per line, a line that is not valid JSON is left out, and the conversion report lists it
• Any other file that is not valid JSON is not converted; the report gives the line and column where it goes wrong
• A file cut short (an interrupted download or export) keeps the complete records before the cut; the report says where the file ends
• Entries of a record list that are not records (null, a lone number) are left out, and the report counts them

Best Practices

• Use arrays of objects for tabular data
• Keep field names consistent across records
• Use ISO 8601 for dates (e.g., "2024-01-15T10:30:00Z")

Note on the Excel formats. An .xlsx sheet holds at most 1,048,575 rows of data and 16,384 columns, and a legacy .xls sheet 65,535 rows and 256 columns. Rather than ship a file that looks complete but is silently cut short, DataMeans leaves a table out of any Excel format it does not fit and notes it in the job report. The .xlsx copy is also left out for tables of more than 8,000,000 cells (1,048,575 rows of 12 columns is 12.6 million), which take too long and too much memory to write, and the .xls copy for tables of more than 150,000 cells, which the legacy format takes far too long to write. The CSV, JSON and SQL outputs always contain every row.

Overview

JSON (JavaScript Object Notation) is a lightweight, text-based data interchange format derived from JavaScript object syntax. It represents structured data using key-value pairs, arrays, and nested objects, making it ideal for web APIs, configuration files, and data exchange between applications. JSON is language-independent and supports Unicode, with optional schema validation through JSON Schema.

History and Background

• 2001: JSON specified by Douglas Crockford at State Software; first JSON message sent in April 2001.
• 2002: JSON.org website launched, promoting JSON as alternative to XML.
• 2005: Yahoo begins offering web services in JSON.
• 2006: RFC 4627 published, registering the application/json media type.
• 2009: ECMAScript 5 adds native JSON support (JSON.parse and JSON.stringify).
• 2013: RFC 6901 (JSON Pointer) and RFC 6902 (JSON Patch) published in April, defining value addressing and a six-operation patch format.
• 2013: JSON standardized by Ecma International as ECMA-404.
• 2015: RFC 7464 (JSON text sequences) and RFC 7493 (I-JSON profile) published.
• 2017: RFC 8259 published as Internet Standard STD 90.
• 2018: JSON Schema draft-07 released for validation.
• 2019: JSON Schema 2019-09 draft published.
• 2020: JSON Schema 2020-12 draft published, adding dynamic references.
• 2024: RFC 9535 published in February, standardizing JSONPath query expressions.
• Present: Ubiquitous in web development and APIs.

File Format Specifications

Basic Structure:

• Plain text format using UTF-8 encoding
• File extension: .json
• MIME type: application/json
• Whitespace allowed for readability (spaces, tabs, newlines)
• Comments not officially supported (though JSON5 adds them)
• No charset parameter defined for the application/json media type
• Byte order mark: must not be added by emitters; parsers may ignore a leading U+FEFF

Syntax Rules:

• Objects: Unordered collections of key-value pairs in curly braces {}
• Arrays: Ordered lists of values in square brackets []
• Keys: Always strings, enclosed in double quotes
• Values: Can be objects, arrays, strings, numbers, booleans, or null
• Strings: Double-quoted with backslash escaping
• Numbers: Decimal notation, no leading zeros except 0
• Escapes: \", \\, \/, \b, \f, \n, \r, \t, and \uXXXX for Basic Multilingual Plane characters
• Characters above U+FFFF: written as twelve-character UTF-16 surrogate pair escapes
• Structural characters: exactly six: begin/end object, begin/end array, name separator (:), value separator (,)
• NaN and Infinity: not representable in the number grammar

Key Specifications:

• Maximum nesting depth: Implementation-dependent; the specification sets no limit
• Maximum string length: Implementation-dependent
• Number precision: Not limited by the grammar; IEEE 754 double precision assumed for interoperability
• Unicode support: Full Unicode character set; UTF-8 required for open interchange
• Integer interoperability: limited to ±9,007,199,254,740,991 (2^53 − 1); RFC 7493 advises string encoding beyond this range

Data Types and Structures

Structural Elements:

• Objects: Key-value containers, keys must be unique strings
• Arrays: Zero-indexed ordered collections
• Nested structures: Objects and arrays can contain each other
• References: No built-in support for references or cycles
• Schema validation: Optional via JSON Schema specification

Version Differences

Compatibility Notes:

• RFC 8259 made no grammar changes from RFC 7159; RFC 7159 broadened RFC 4627 by allowing any top-level value
• Most implementations support full UTF-8
• Some parsers are strict, others lenient with trailing commas
• JSON5 is not universally supported
• No formal versioning in JSON files themselves
• Object member names are only a SHOULD-be-unique requirement; behavior with duplicates varies between implementations
• JSON has no date or binary types; RFC 7493 recommends RFC 3339 timestamps and base64url-encoded binary data

Technical References

• RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format
• ECMA-404: The JSON Data Interchange Syntax
• Introducing JSON
• Wikipedia: JSON
• JSON Schema Specification