Native JSON/JSONL Editing with CSV View

[Grant-Keiter-FHR]

TL;DR

Problem: AI generates test data better in JSON format, but humans need tabular CSV views to review large datasets effectively.

Solution: Open JSON files in edit-csv, view/edit as tables, save changes back to JSON automatically.

Key Innovation: Arrays expand vertically (multiple rows) using .0. notation, keeping columns manageable. Schema preserved via special ~DATA_TYPES~ row.

Status: Working prototype with 121 passing tests on JSONToCSV branch. Seeking feedback on this idea

---

Summary

We propose adding native support for editing JSON and JSONL files through edit-csv's visual table interface. This would allow users to leverage the superior readability of CSV tables for reviewing and editing structured JSON data, while preserving JSON's advantages for programmatic access and AI-assisted data generation.

The Problem

Working with AI-Generated Test Data

When generating test data with AI assistants (ChatGPT, Claude, Copilot, etc.), we've consistently encountered issues with CSV format:

• Column Misalignment: AI frequently misplaces columns or corrupts delimiters
• Inconsistent Formatting: Quote handling and escape sequences often broken
• Poor Error Recovery: AI can recognize CSV corruption but struggles to fix it reliably
• Context Loss: Large CSV files are difficult for AI to maintain consistent structure

The JSON Advantage (and Limitation)

JSON works significantly better with AI:

• ✅ Structured format that AI understands natively
• ✅ Self-documenting with clear property names
• ✅ Validation through JSON parsers catches errors immediately
• ✅ Less prone to corruption from AI generation

However, JSON has a critical usability issue for humans:

• ❌ Difficult to review large datasets - vertical scrolling loses context
• ❌ Hard to spot patterns across multiple records
• ❌ Cannot see field values side-by-side for comparison
• ❌ Poor readability for tabular data review

The edit-csv Solution

edit-csv already solves the human readability problem for CSV files beautifully. We can see all our data in a table format, quickly scan across rows, and make decisions based on patterns we observe.

What if we could get the best of both worlds?

• Store data in JSON (AI-friendly, structured, validated)
• View and edit as CSV tables (human-friendly, scannable, contextual)
• Changes automatically sync back to JSON format

Proposed Solution

We've implemented a working prototype in the JSONToCSV branch that demonstrates this concept with the following features:

1. JSON to CSV Conversion with Schema Support

Edit-csv can open JSON files and display them as CSV tables, preserving data type information:

Example JSON Structure (nested.json):

{
  "version": "1.0",
  "schema": {
    "House": "string",
    "Owner": "string",
    "Cars": [
      {
        "Make": "string",
        "Model": "string",
        "Doors": "number"
      }
    ]
  },
  "rows": [
    {
      "House": "101 Main St",
      "Owner": "John Doe",
      "Cars": [
        {
          "Make": "Ford",
          "Model": "F150",
          "Doors": 2
        }
      ]
    },
    {
      "Cars": [
        {
          "Make": "Toyota",
          "Model": "Camry",
          "Doors": 4
        }
      ]
    }
  ]
}

Displayed as CSV (in edit-csv):

2. Schema Preservation with ~DATA_TYPES~ Row

• Schema information is embedded as the first data row with a special ~DATA_TYPES~ marker
• The PK column serves solely as a marker for this special row (empty for data rows)
• When saving, the schema row is detected and converted back to the JSON schema section
• Users can add new columns with types directly in the CSV view
• Changes to data types are automatically reflected in the JSON schema

3. Nested Object Flattening with Dot Notation

Nested objects are flattened using standard dot notation:

{
  "user": {
    "address": {
      "city": "NYC"
    }
  }
}

Becomes CSV columns: user.address.city

4. Array Expansion with .0. Notation

Arrays of objects expand vertically instead of creating endless columns:

Key Design Decision: Always use .0. notation, never .1. or .2.

• The .0. indicates the field is part of an array structure, not an array index
• Each array element becomes a separate CSV row
• Non-array fields only appear in the first row of the expansion

Why Vertical Expansion?

• Keeps column count manageable even with large arrays
• Natural for reviewing lists of sub-items (orders, transactions, addresses, etc.)
• Maintains edit-csv's table-based readability

Example:

{
  "Owner": "John",
  "Cars": [
    {"Make": "Ford", "Model": "F150"},
    {"Make": "Toyota", "Model": "Camry"}
  ]
}

Expands to:

Owner | Cars.0.Make | Cars.0.Model
John  | Ford        | F150
      | Toyota      | Camry

Note: "Owner" only appears in the first row because it's not part of the array.

5. Configurable Data Path Extraction

For complex JSON structures, a configuration file (.vscode/csv-json-config.json) allows pattern-based extraction:

{
  "patterns": {
    "financial": {
      "filePattern": "*.financial.json",
      "dataPath": "data.transactions",
      "schemaPath": "metadata.schema"
    },
    "default": {
      "filePattern": "*",
      "dataPath": "rows",
      "schemaPath": "schema"
    }
  }
}

This handles:

• API response wrappers
• Multi-level nested structures
• Different JSON formats per file pattern
• Custom schema locations

6. Bidirectional Sync

Changes made in the CSV view are converted back to proper JSON structure:

• Schema updates reflected in the JSON schema section
• Multiple CSV rows collapse back into arrays
• Dot notation paths reconstruct nested objects
• Type information preserved (strings, numbers, booleans, etc.)

Use Cases

1. AI-Assisted Test Data Generation

1. Ask AI to generate test data in JSON format (reliable)
2. Open JSON file in edit-csv (visual review)
3. Spot issues, make corrections in table view
4. Save - changes go back to JSON
5. Use JSON file in tests (proper structure guaranteed)

2. API Response Analysis

1. Export API responses as JSON
2. Configure data path to extract relevant array
3. View in table format to analyze patterns
4. Edit/clean data if needed
5. Re-import cleaned JSON

3. Configuration Management

1. Maintain configs in JSON (structured, validated)
2. Review/edit in CSV table (scan multiple entries easily)
3. Version control still works (JSON diff)
4. Non-technical users can edit via CSV view

4. Database Export/Import Workflows

1. Export DB to JSON (preserves types, nested structures)
2. Edit in CSV table view (familiar interface)
3. Save back to JSON
4. Import to DB (type information preserved)

Technical Implementation

The prototype includes:

• New Modules:

  • src/json/jsonEditorModule.ts - Main orchestration
  • src/json/converters/jsonToCsvConverter.ts - JSON → CSV
  • src/json/converters/csvToJsonConverter.ts - CSV → JSON
  • src/json/configLoader.ts - Pattern-based configuration

• Key Features:

  • Schema flattening/unflattening
  • Array expansion (vertical) with .0. notation
  • Nested object handling with dot notation
  • Type preservation and inference
  • Configurable data path extraction
  • Full round-trip conversion

• Test Coverage: 121+ tests covering:

  • Basic conversions
  • Nested structures
  • Array expansion
  • Schema handling
  • Edge cases

Benefits to edit-csv Users

1. Unified Workflow: One tool for both CSV and JSON data
2. Better AI Collaboration: Generate in JSON, review in CSV
3. Type Safety: Schema support prevents type errors
4. Flexibility: Handle complex nested structures
5. Familiarity: Same great edit-csv interface
6. No Lock-in: JSON files remain standard, readable by any tool

Questions for Discussion

1. Interest Level: Does this functionality align with edit-csv's vision and roadmap?

2. Integration Approach:

   • Should this be in the core extension?
   • Better as a separate extension?
   • Optional feature flag?

3. Notation Standards:

   • Is .0. notation intuitive enough for users?
   • Alternative notations to consider?
   • Should we support both horizontal and vertical array expansion?

[janisdd]

Thanks for the input, I appreciate it.

Here are some first thoughts on it:

• There is already an extension to edit JSON files as a table: https://marketplace.visualstudio.com/items?itemName=RandomFractalsInc.vscode-data-preview

  • A horizontal layout is used there...
  • Have you looked at this extension?

• For the array extension, I'm not sure why .0. is needed for the vertical layout?

• Not all .json files have a schema, what happens to the types?

• What happens if type and value do not match?

• Does the column sorting have to ignore the first two columns?

• What happens if the user deletes the first and (or) second row?

• I don't understand Configurable Data Path Extraction, can you give an example?