Having spent the past half year learning Swift and Xcode, I'm pretty sure that the app I'm planning will mostly rely on JSON.

For anyone who's worked with a lot of JSON, what's your recommended approach: just type everything into a text doc in json format? enter the data into a spreadsheet and then convert csv to json using a conversion app?

There are a gazillion JSON-utility apps at the App Store, but I'm having trouble filtering through them because I don't know what approach to take to keep all the data as organized and accesible-for-editing as possible.

Any thoughts/comments about what's worked for you are appreciated.

Mostly when I have been working with JSON it has come from a server. Where are you expecting to get the original data from?

enter the data into a spreadsheet and then convert csv to json using a conversion app?

This is probably what I would do if I had a lot of custom JSON to create.

Mostly when I have been working with JSON it has come from a server. Where are you expecting to get the original data from?

I'll be writing/typing all the data. The best analogy I can come up with is a textbook: the data would be, like, the title of a chapter, the title of a section, a paragraph of text, an image, a set of review questions, etc.

I would use a spreadsheet, or better still a database, however spreadsheets are more accessible, and output to TSV rather than CSV.

The spreadsheet cells naturally translate to tabs, and you can use commas in your text. Just watch out for spurious double-quotes that can occur, which are easy enough to remove in a text editor.

After working with Numbers for a bit, I found the spreadsheet difficult to read & edit, what with my cells and their various sizes given that some cells take a lot of text. So I found a simple database app on the App Store called Collections that can export in csv and json.

But the json gets disordered (the key pairs don't export in the same order I set their respective fields in the app), and the app can't do sub-fields. Now, reordering's easy enough going the Collections > export .csv > rearrange columns in Numbers > export tab .csv > use the app Boop to convert the csv to pretty json, but it still can't create sublevels of json.

So what I'm trying to do is take something like this:

[
  {
    "id": 1,
    "key1": "value1",
    "keyC": "valueC",
    "key2": "value2",
    "keyA": "valueA",
    "keyB": "valueB",
    "key3": "value3"
  },
  {
    "id": 2,
    ...
  },
  ...
]

and turn it into this:

[
  {
    "id": 1,
    "numberedKeys": [
      {
        "id": 1,
        "key1": "value1"
      },
      {
        "id": 2,
        "key2": "value2"
      },
      {
        "id": 3,
        "key3": "value3"
      }
    ],
    "letteredKeys": [
      {
        "id": 1,
        "keyA": "valueA"
      },
      {
        "id": 2,
        "keyB": "valueB"
      },
      {
        "id": 3,
        "keyC": "valueC"
      }
    ]
  },
  {
    "id": 2,
    ...
  },
  ...
]

without resorting to repetitive cutting/pasting & pattern-find/replacing in TextEdit.

I don't know what this process is referred to as: rearranging json hierarchy? sorting json? What feature of an app is what I'm looking for called? I'm looking at the features offered by the three paid apps that remain (Smart JSON Editor, JSON Editor, and Power JSON Editor--anyone else see a trend here?), but without knowing what I'm trying to do, I can't identify which app or apps do that.

Can anyone fill me in on what it is that I'm trying to do with the original json?

Not sure if there is an official term , but I would call it restructuring, as you are taking a JSON file with one structure and creating a second file with a different JSON structure.

BTW, something I recentlt learnt is that JSON files are not ordered - see here

Dave seeks JSON advice:

I don't know what this process is referred to as: rearranging json hierarchy? sorting json?

In the days before JSON, there existed a common data exchange format called "XML".

A common issue programmers had back then seems similar to what you're now experiencing. Namely, the XML they received was not in the format of the XML that their program needed. Consequently, they resorted to many painful ways to rearrange XML structures to fit their requirements.

This was so common that XML boffins resorted to inventing an entirely NEW language to help programmers transmogrify XML from one format to another. They called this process: Transformation

The language they invented was called XSLT. See -> XSLT in the Wiki

Stack Overflow

Our friend, Stack Overflow may have some answers for ye. See -> Transforming JSON

Thanks for the contributions, guys--based on the info you've provided, it appears that there aren't really any offline options for what I'm looking for and the conversion would likely take just as long as what I trial ran the other day. What I think I'll end up doing is (and I'll put the steps here in case anyone's interested; bonus: it's free)...

1. export CSV from database app
2. open CSV in Numbers, drag columns to rearrange (to prep for step 5), and export again in CSV
3. copy CSV text using TextEdit
4. convert the CSV into pretty JSON with Boop
5. copy the JSON text back into TextEdit and use its (relatively new, I think?) pattern matching find/replace feature to create the sub-keys
6. save as JSON
7. eat M&Ms

Who knows--given how slow I work, a JSON transformer might be created by the time I finish entering all the info into the database.... 8^)p

Dave, still looking for solutions, offers his approach:

1. export CSV from database app
2. open CSV in Numbers
3. drag columns to rearrange
4. export again in CSV
5. copy CSV into TextEdit
6. convert the CSV into pretty JSON with Boop
7. copy the JSON text back into TextEdit
8. use pattern matching find/replace feature to create the sub-keys
9. save as JSON

I think most of this can be done with Swift.

Ask the Collections developers if you can pull CSV data from their application via command line. If no, export the CSV by hand. Better yet, ask them for an Export to JSON command.

Write a Swift command line application to open the CSV or JSON file. See -> Parsing CSV File

The previous step will load your data into an Array of Swift objects.

Process each object in your new array. Use your business logic to rearrange your objects into more complex objects with sub-keys, one-to-many relationships, optional fields, sensible defaut values, etc. This is your logic. You haven't shared the format of your raw data, or the format of your target data. It's hard to help without concrete examples.

Export your new objects as JSON. See --> Writing to Documents Directory

Optional: Pretty print See --> Pretty Print your JSON

Dave,

I've been away for a few days. Just saw this, I thought I'd throw in my approach to the issue. My approach was using Numbers on my Mac for all the data, which was quite large. I then exported the Numbers file to TSV. I also had a little program a friend wrote that converted the TSV into JSON, this was command line driven looking in a certain folder for TSV files then converting them.

This appoach worked well. Sounds l8ke you've got a handle on it. Just wanted you to know my approach and the success I had with it.

Best of luck, Taz