Most data you interact with is likely stored in spreadsheets, such as Excel or CSV files. Spreadsheets, which organize data in a grid of columns and rows, make it easy for people to read and edit data with simple headers and an easy-to-follow visual structure. 

As data grows more complex, from connecting multiple data sources to merging multiple datasets, a more robust data format is needed.

What is JSON?

JSON (JavaScript Object Notation) is a powerful data structure that organizes information using labels instead of column headers. 

In a CSV (Comma Separated Values) file, data is positional. You know the number in C7 is the "Spend Amount" because it’s under the header “Spend Amount” in Column C. Every value under that header is a “Spend Amount.” If someone inserts a new Column B, your "Spend Amount" shifts to Column D. This means formulas or references might break. The data has no identity of its own; it only has a location. This makes CSVs easy to read as a spreadsheet, but unreliable for automation and complex data tasks.

In JSON, every single piece of data is physically attached to its label. This makes it easier to share data across systems because the fields are standardized and clearly defined. This is why it’s so useful for working within Workiva. 

The Anatomy of JSON

JSON can look intimidating since it’s not as easy to read as a spreadsheet. The good news is you don’t need to know how to write or read JSON. Workiva Chains will help with that. Let’s focus on understanding how JSON is structured and why it’s so helpful for complex data. While a CSV uses commas to separate data, JSON uses four primary characters:

1. The Curly Brackets { } (The Container): These hold a single "Data Object." A single  Data Object can include a single record or row of data. The main way to understand curly brackets is that everything within them belongs together in one relationship, such as a single row from a spreadsheet or an invoice with specific customer details.
2. The Square Brackets [ ] (The List): These hold a list of data objects. Multiple rows of a spreadsheet, each one an individual data object, would be organized as a list within square brackets. In Workiva, your entire dataset is usually wrapped in these, signifying a "List of Records."
3. The Colon : (The Connector): This sits between the label and the data. For example, "Amount": 1500.
4. The Quote Marks " ": These wrap around text. "This is a label" or "This is a piece of text." This separates other data types. 1500 is a number. “1500” is text.
5. The Comma (,): This is the separator between data objects. It appears at the end of the line when there are multiple of the same data object collected together (either within {} or [ ]).
   Note: The last line does not get a comma.

Building JSON

Taking the pieces of JSON, we can start building our datasets. At its most basic, JSON can be a single key/value pair. The Key is the field name or the label for the information. The Value is the data. Think of the key as the header and the value as the cell level data.

{
  "field_name": "Field Value"
}

We can set a field name and value for the key/value pair:

{
  "revenue amount": 1500
}

We can add multiple key/value pairs, each capturing a different data object.

• Strings - any text, wrapped with quotation marks
• Integer - whole numbers, e.g. 1, 2, 3, 4
• Decimal - any number, including fractional, e.g. 1.25
• Boolean - True or False. These are helpful for capturing yes or no values, such as “Product Shipped” or “Invoice Sent.” These make filtering large datasets more efficient.

{
  "string": "Learning JSON is fun",
  "integer": 1500,
  "decimal": 15.83,
  "boolean": true
}

Why JSON over CSV

Here is why JSON is superior for financial integrity, both for organizing complex data and sharing it across multiple systems:

1. "Nested" Data

This is the biggest limitation in a spreadsheet. Imagine you have an invoice. That invoice has a header (Date, Vendor, Total) but it also has product items (Item A, Item B, Item C).

In a CSV, you have to repeat the Header info for every single product and for every individual field related to each product. In JSON, you can "nest" the line items inside the invoice object.

{

  "Invoice Number": "INV-1001",

  "Vendor": "Acme Corp",

  "ProductItems": [

    { "Product Name": "Audit Services", "Cost": 5000 },

    { "Product Name": "Travel Expense", "Cost": 250 }

  ]

}

This hierarchy makes it impossible for "Product Items" to accidentally get attached to the wrong "Invoice Number." It also helps to organize large, complex datasets in a clear, organized structure.

2. Clear data types

In Excel, a cell can be a number in one row, date in another, and text in another. This often breaks formulas, leads to incorrect values and calculations, and a frustrating experience trying to fix it.

JSON sets each field to a specific type, making it predictable and less error prone.

3. Handling missing data

In a CSV, blank data is shown as two commas (,,). This makes it unclear if the data is missing, is it’s meant to be blank, or if it should have some other value like 0.

In JSON, missing data can be shown in specific ways. Two quotes (“”) means blank text, and only applies to text. Alternatively, null means no data is provided (note that null is written without quotes). This can be the difference between including purposefully blank data:

• "tax_id": "" - The customer did not provide or does not have a tax ID
• "tax_id": null - The data is missing and needs to be corrected, avoiding financial reporting issues later.

4. Extensive compatibility across multiple platforms

JSON is widely used across data platforms, and is likely being used to send data through websites you use every day. Wdata imports your CSVs, then when you start processing this data in Chains, it is often necessary to convert it to JSON.

Using JSON in Workiva Chains

When you are building an automation in Chains, you will mainly use the "JSON - CSV to JSON" connector to transform your CSV/spreadsheet into usable JSON data.

Your Payload

In Chains, you will often hear the term Payload. This simply means "the data currently moving through the pipe." If your Chain starts with an SFTP download, the "Payload" is your CSV file. After the conversion step, the "Payload" becomes your JSON.

Common Issues: What to Watch For

JSON is powerful, but it is strict in its structure. If a single comma is missing, the whole file can fail. This is helpful because instead of mapping data incorrectly, you get a fixable error. Workiva’s CSV to JSON node will handle the JSON construction. If you run into issues, or if you edit the JSON yourself at any point, these are some common errors in JSON to be aware of.

• The Trailing Comma: In a list, every item needs a comma except the last one.
• Wrong: ["Apples", "Oranges",]
• Right: ["Apples", "Oranges"]
• “Keys” are always in quotes: Keys in JSON are always text, meaning they should always be between quotes.
• Quote Marks in Data: If a vendor name is "Bob's "Best" Widgets", those extra quotes inside the name will confuse the JSON. Workiva usually handles this ("escaping"), but it's something to watch for in messy source data, especially data with long text strings or user content. Escaping often uses a backslash (\). If you see backslashes in your code, be careful not to delete it. It might be escaping a special character.
• Case Sensitivity: In JSON, "Amount" and "amount" are two completely different things. If your Wdata table expects a capital 'A' and your JSON has a lowercase 'a', the data won't map.
• Large File Sizes: While JSON is smarter than CSV, it can be "wordier" because it repeats labels. If you are handling millions of rows, make sure your Chain is optimized to handle large datasets.

Next Steps

JSON might feel intimidating at first glance, but it provides the structure and safety that financial data requires. By mastering this format within Workiva Chains, you are ensuring that your data isn't just a collection of numbers, but a robust, auditable trail.

• CLP - Simple JSON Data