JSON

What is JSON?

JSON stands for JavaScript Object Notation. It is a lightweight text format for exchanging structured data between systems. If an API returns invoices, customers, orders, or events, there is a good chance the response is JSON.

JSON grew out of JavaScript syntax, but it is no longer tied to JavaScript. Almost every programming language can read and write it, and the format is standardised in RFC 8259 and ECMA-404.

Think of JSON as a form where every field has a name and a value. A value can also contain another form or a list of forms, which is how JSON represents nested data.

What JSON looks like

A JSON object contains name-value pairs between curly braces. Names are strings in double quotes. Values can be strings, numbers, true, false, null, arrays, or other objects.

For example: {"customer": "Peeters BV", "openAmount": 1250.50, "invoices": ["F2026-041", "F2026-057"]}.

There are only a few building blocks:

• String: text in double quotes.

• Number: a numeric value, without a separate integer or decimal type in the JSON grammar.

• Boolean: true or false.

• Null: an explicit empty value.

• Array: an ordered list between square brackets.

• Object: a set of name-value pairs between curly braces.

That small vocabulary is why JSON travelled so far. It is simple enough to parse and flexible enough for most web integration.

Where you meet JSON

API responses
Most modern web APIs return JSON. A connector that reads customers from a CRM or orders from a webshop usually receives JSON payloads.

Webhooks
When a system pushes an event to your endpoint, the message body is often JSON.

Configuration files
Many tools store settings as JSON. Power BI report and theme files, application manifests, and deployment templates often use JSON or a close variant.

Logs and telemetry
Applications often write one JSON object per line so log tools can parse fields such as timestamp, user ID, event type, and error code.

Document databases
MongoDB, Azure Cosmos DB, and similar systems store document-shaped records that map naturally to JSON.

JSON as semi-structured data

A relational table has a fixed set of columns. JSON carries its shape with the data. One customer object may contain a VAT number and another may not. One order may have two lines, another may have twenty. Nested objects and arrays can represent that naturally.

That flexibility is useful at the edge of a data platform. You can store raw API responses in a data lake before deciding which fields matter. It is also why JSON is called semi-structured: there is structure, but it is not enforced like a database schema unless you add validation.

For analytics, semi-structured data eventually needs discipline. Reports want rows, columns, stable field names, and known data types.

From JSON to tables

Reporting tools work with rows and columns, while JSON is a tree. Flattening JSON means turning nested objects into columns and arrays into rows.

An address object might become columns such as address_city and address_country. An order with five lines becomes five rows in an order-line table, with the order ID repeated on each row.

Power Query can expand JSON objects and arrays through its connector and transform steps. SQL Server and Microsoft Fabric support OPENJSON, which turns JSON text into a rowset that can be queried like a table. For large analytical storage, pipelines often convert JSON into Parquet because Parquet is columnar, compressed, and much faster for scans.

JSON versus CSV, XML, and Parquet

JSON versus CSV
CSV is a flat table. It is compact and useful for simple rows and columns, but it has no nesting and weak type information. JSON handles nested structures better.

JSON versus XML
XML can represent nested structured data too, but it is more verbose. JSON became the default for web APIs because it is lighter and easier to work with in web applications. XML still appears in formal standards, government exchange, and e-invoicing formats.

JSON versus Parquet
JSON is mainly an exchange format. Parquet is an analytical storage format. A common pattern is JSON at the edge of the platform and Parquet inside the lakehouse or warehouse.

What to watch out for

Changing structure
A vendor can rename a field, move it deeper, or stop sending it. Your parser may still run while the column becomes empty. Monitor expected fields.

No date type
JSON has strings and numbers, not a native date type. Agree on a date and timezone format, such as ISO 8601, before systems exchange timestamps.

Large numbers
Many parsers map JSON numbers to IEEE 754 double precision. Very large IDs can lose precision. Send identifiers as strings when exactness matters.

Duplicate object names
JSON specifications recommend unique names within an object. Duplicate names lead to inconsistent parser behaviour.

Validation
Use JSON Schema or a data contract when the shape matters. Flexible input is helpful until a silent schema change breaks a dashboard.