JSON is a DriveWorks array with a bad attitude

I'll admit it: I've been wrestling with JSON lately.

And I finally figured out why. It's basically the evil twin of a DriveWorks array.

In DriveWorks, arrays are polite. There are rows, columns, nice and orderly. You can loop through them without feeling like you're defusing a bomb.

JSON? JSON is that cousin at Thanksgiving who insists on telling you their "truth." Keys, values, brackets, commas… and if one is out of place? Boom. Whole thing breaks.

At one point I broke a perfectly good API call six different ways only to discover I was missing a single comma. A comma!

So here's my current working understanding:

• DriveWorks array: Friendly spreadsheet that does what you ask.
• JSON: Same spreadsheet, but every row insists it's special, and half of them lie about whether they're null.

Eventually, it clicked for me when I just pictured JSON as a DriveWorks array with a bad attitude.

Until then, I'll keep pretending I "meant" to spend my afternoon debugging punctuation.

What I'd tell myself now

I posted that on a Friday the 13th, and people who know JSON better than I did were kind enough to straighten me out in the comments. The short version: the comparison got me unstuck, and it is also wrong in a useful way.

A DriveWorks array is a grid. JSON is a tree. An array is rows and columns and nothing else:

{"Name","Qty";"Bracket",4;"Bolt",16}

JSON is built from objects, which are named values, and any value can be another object or a list:

{ "order": "1042", "lines": [ { "name": "Bracket", "qty": 4 }, { "name": "Bolt", "qty": 16 } ] }

A list of flat objects, like lines above, is the one shape that maps cleanly onto an array. My first JSON happened to be exactly that, which is why the comparison felt right. The first time a value had something nested inside it, the spreadsheet picture fell apart.

The flexibility is the point, and the punctuation is the price. A grid can only be a grid. JSON can describe an order with lines, each line with options, each option with whatever it needs. More freedom means more syntax, and one missing comma still breaks the whole thing.

DriveWorks will do the translating. I spent that afternoon picking JSON apart by hand. I did not need to:

• XPathJsonGet pulls one value out by its path, for example XPathJsonGet(DWConstantResponse, "lines[0].name"). Lists start at zero.
• SppConvertJsonToTable turns a JSON list or object straight into a table. Anything nested deeper than rows and columns comes back as text in its cell, which tells you exactly where the tree did not fit the grid.
• SppConvertTableToJson goes the other way.
• The JSON Document builds outgoing JSON from a schema, so DriveWorks places the commas.

Validate before you debug. Paste the JSON into any validator before touching the rules. It finds the missing comma in a second, which beats breaking an API call six different ways.

I still think of JSON as an array with a bad attitude. I just know now that it has a reason for the attitude.