使用JQ将AQL输出的JSON文件转换为CSV格式的咨询

Got it, let's figure out how to turn that nested AQL JSON output into a readable CSV file. Here's a practical, step-by-step approach that works for your structure:

1. Understand the JSON Structure First #

Your JSON has a top-level results array, where each item includes:

• Top-level fields: file, path, type, created
• A nested properties array (key-value pairs with tracker as the column name and value as the cell content)
• A nested stats array (with download-related data)

The goal is to flatten this nested structure so every piece of data maps to a single column in the CSV.

2. Choose the Right Tool #

For flexibility (especially if your properties or stats might have varying fields), Python is a great choice—it has built-in modules to handle JSON and CSV without extra setup. If you prefer command-line tools, jq works too (but requires knowing all possible fields upfront).

3. Python Implementation (Flexible for Variable Fields) #

This code will automatically detect all possible columns from your JSON, including any new tracker values in properties:

import json
import csv

# Load the JSON file
with open('file.json', 'r') as json_file:
    data = json.load(json_file)

# Collect all unique column names from the JSON structure
all_columns = {'file', 'path', 'type', 'created', 'downloaded', 'downloads'}
for result in data['results']:
    # Add all tracker names from properties as columns
    for prop in result['properties']:
        all_columns.add(prop['tracker'])

# Convert the set to a sorted list (adjust order if needed)
sorted_columns = sorted(all_columns)

# Write to CSV
with open('output.csv', 'w', newline='', encoding='utf-8') as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=sorted_columns)
    writer.writeheader()

    for result in data['results']:
        # Initialize row with top-level fields
        row_data = {
            'file': result.get('file', ''),
            'path': result.get('path', ''),
            'type': result.get('type', ''),
            'created': result.get('created', ''),
            # Handle stats (assuming 1 entry per result; adjust if multiple)
            'downloaded': result['stats'][0].get('downloaded', '') if result.get('stats') else '',
            'downloads': result['stats'][0].get('downloads', '') if result.get('stats') else ''
        }

        # Add properties as key-value pairs to the row
        for prop in result['properties']:
            row_data[prop['tracker']] = prop.get('value', '')

        # Write the row to CSV
        writer.writerow(row_data)

How This Works: #

• Automatic Column Detection: It scans all results to capture every possible tracker from properties, so you don't have to hardcode them.
• Error Handling: Uses .get() and conditional checks to handle missing fields (e.g., empty file.number values or missing stats entries) without crashing.
• Clean Output: Writes a properly formatted CSV with headers matching your JSON fields.

4. Command-Line Alternative with jq #

If you know all possible fields upfront, you can use jq (a lightweight JSON processor) for a quick one-liner:

jq -r '
  ["file","path","type","created","file.name","file.number","downloaded","downloads"],
  (.results[] | [
    .file, .path, .type, .created,
    (.properties[] | select(.tracker == "file.name").value),
    (.properties[] | select(.tracker == "file.number").value),
    .stats[0].downloaded, .stats[0].downloads
  ]) | @csv
' file.json > output.csv

Note: This requires you to list every tracker from properties explicitly. If your JSON has dynamic tracker values, the Python approach is better.

内容的提问来源于stack exchange，提问作者newtocoding