json-and-csv-data-transformation

# JSON and CSV Data Transformation

Transform data between JSON, CSV, and other formats. Filter, map, flatten nested objects, and reshape data for analysis, reporting, and API integration.

## When to use

- Use case 1: When the user asks to convert data between JSON and CSV formats
- Use case 2: When you need to filter, extract, or transform specific fields from data
- Use case 3: For flattening nested JSON structures into tabular format
- Use case 4: When processing API responses for analysis or reporting

## Required tools / APIs

- **jq** — Command-line JSON processor (essential for JSON manipulation)
- **csvkit** — Suite of CSV tools (csvjson, csvcut, csvgrep, etc.)
- No external API required

Install options:

```bash
# Ubuntu/Debian
sudo apt-get install -y jq csvkit

# macOS
brew install jq csvkit

# Node.js (native support, no packages needed for basic operations)
# For advanced CSV parsing: npm install csv-parse csv-stringify
```

## Skills

### json_to_csv

Convert JSON array to CSV format.

```bash
# Simple JSON array to CSV
echo '[{"name":"Alice","age":30},{"name":"Bob","age":25}]' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, (map([.[ $keys[] ]]) | .[] | @csv)'

# JSON file to CSV file
jq -r '(.[0] | keys_unsorted) as $keys | $keys, (map([.[ $keys[] ]]) | .[] | @csv)' data.json > output.csv

# JSON to CSV with specific fields
jq -r '.[] | [.id, .name, .email] | @csv' users.json

# Using csvkit (simpler syntax)
cat data.json | in2csv -f json > output.csv
```

**Node.js:**

```javascript
function jsonToCSV(jsonArray) {
  if (!Array.isArray(jsonArray) || jsonArray.length === 0) {
    return '';
  }
  
  // Get headers from first object
  const headers = Object.keys(jsonArray[0]);
  
  // Escape CSV values
  const escape = (val) => {
    if (val === null || val === undefined) return '';
    const str = String(val);
    if (str.includes(',') || str.includes('"') || str.includes('\n')) {
      return `"${str.replace(/"/g, '""')}"`;
    }
    return str;
  };
  
  // Build CSV
  const headerRow = headers.join(',');
  const dataRows = jsonArray.map(obj =>
    headers.map(header => escape(obj[header])).join(',')
  );
  
  return [headerRow, ...dataRows].join('\n');
}

// Usage
// const data = [
//   { name: 'Alice', age: 30, city: 'New York' },
//   { name: 'Bob', age: 25, city: 'San Francisco' }
// ];
// console.log(jsonToCSV(data));
```

### csv_to_json

Convert CSV to JSON array.

```bash
# CSV to JSON
csvjson data.csv

# CSV to JSON with pretty printing
csvjson data.csv | jq '.'

# CSV to JSON array of objects
csvjson --stream data.csv

# CSV file to JSON file
csvjson input.csv > output.json

# Using pure jq (if headers are in first row)
jq -Rsn '[inputs | split(",") | {name: .[0], age: .[1], city: .[2]}]' < data.csv
```

**Node.js:**

```javascript
function csvToJSON(csvString) {
  const lines = csvString.trim().split('\n');
  if (lines.length < 2) return [];
  
  // Parse CSV value (handle quotes)
  const parseCSVValue = (val) => {
    val = val.trim();
    if (val.startsWith('"') && val.endsWith('"')) {
      return val.slice(1, -1).replace(/""/g, '"');
    }
    return val;
  };
  
  // Split CSV line (basic implementation)
  const splitCSVLine = (line) => {
    const result = [];
    let current = '';
    let inQuotes = false;
    
    for (let i = 0; i < line.length; i++) {
      const char = line[i];
      
      if (char === '"') {
        inQuotes = !inQuotes;
        current += char;
      } else if (char === ',' && !inQuotes) {
        result.push(parseCSVValue(current));
        current = '';
      } else {
        current += char;
      }
    }
    result.push(parseCSVValue(current));
    return result;
  };
  
  const headers = splitCSVLine(lines[0]);
  const data = lines.slice(1).map(line => {
    const values = splitCSVLine(line);
    const obj = {};
    headers.forEach((header, i) => {
      obj[header] = values[i] || '';
    });
    return obj;
  });
  
  return data;
}

// Usage
// const csv = `name,age,city
// Alice,30,New York
// Bob,25,"San Francisco"`;
// console.log(JSON.stringify(csvToJSON(csv), null, 2));
```

### filter_and_extract_json

Filter and extract specific fields from JSON.

```bash
# Extract specific fields
jq '.[] | {name: .name, email: .email}' users.json

# Filter by condition
jq '.[] | select(.age > 25)' users.json

# Filter and extract
jq '[.[] | select(.active == true) | {id: .id, name: .name}]' data.json

# Extract nested fields
jq '.[] | {name: .name, street: .address.street, city: .address.city}' data.json

# Get array of single field
jq '.[].name' users.json

# Filter with multiple conditions
jq '.[] | select(.age > 20 and .country == "USA")' users.json

# Map and transform values
jq '.[] | .price = (.price * 1.1)' products.json
```

**Node.js:**

```javascript
function filterAndExtractJSON(data, options) {
  const { filter, extract } = options;
  
  let result = Array.isArray(data) ? data : [data];
  
  // Apply filter function
  if (filter) {
    result = result.filter(filter);
  }
  
  // Extract specific fields
  if (extract) {
    result = result.map(item => {
      const extracted = {};
      extract.forEach(field => {
        // Support nested fields with dot notation
        const value = field.split('.').reduce((obj, key) => obj?.[key], item);
        extracted[field] = value;
      });
      return extracted;
    });
  }
  
  return result;
}

// Usage
// const users = [
//   { id: 1, name: 'Alice', age: 30, address: { city: 'NYC' } },
//   { id: 2, name: 'Bob', age: 25, address: { city: 'SF' } },
//   { id: 3, name: 'Charlie', age: 35, address: { city: 'LA' } }
// ];
// 
// const result = filterAndExtractJSON(users, {
//   filter: user => user.age > 25,
//   extract: ['name', 'age', 'address.city']
// });
// console.log(result);
```

### flatten_nested_json

Flatten nested JSON objects into flat structure.

```bash
# Flatten nested JSON with jq
jq '[.[] | {id: .id, name: .name, street: .address.street, city: .address.