固定长度字段文本转JSON遇阻：jq转换语法配置求助

Hey there! Sounds like you’ve got a rock-solid workflow already for handling those fixed-length files—nice job getting the SFTP download, decompression, and awk conversion all sorted out. Let’s nail down that jq syntax issue so you can turn those pipe-separated lines into properly structured JSON with numbered fields ready for your database mapping.

First, Let’s Define the Input/Output Expectation #

Let’s assume your pipe-separated input (one record per line) looks like this:

1001|Smith|New York|555-1234
1002|Johnson|Chicago|555-5678

And you want JSON output where each field is keyed by its numeric position (1, 2, 3, etc.), like:

{
  "1": "1001",
  "2": "Smith",
  "3": "New York",
  "4": "555-1234"
}

Solution 1: Manual Mapping (Good for Small, Fixed Field Counts) #

If you know exactly how many fields you have, you can explicitly map each position to a numeric key. Use jq’s -R flag to treat input as raw strings, then split on pipes and build your object:

# For a single line
echo "1001|Smith|New York|555-1234" | jq -R 'split("|") | {
  "1": .[0],
  "2": .[1],
  "3": .[2],
  "4": .[3]
}'

# For multiple lines (use -n to read all input lines)
cat your_pipe_file.txt | jq -Rn 'inputs | split("|") | {
  "1": .[0],
  "2": .[1],
  "3": .[2],
  "4": .[3]
}'

Solution 2: Dynamic Mapping (Perfect for Variable/Large Field Counts) #

Since your fields are numbered sequentially, you don’t need to hardcode every key. Use jq’s reduce function to loop through the split fields and build the object automatically:

# Process multiple lines, with optional whitespace trimming
cat your_pipe_file.txt | jq -Rn '
  inputs | 
  split("|") | 
  map(trim) |  # *Optional but useful*—cleans up accidental whitespace around fields
  reduce range(0; length) as $i ({}; 
    .[(($i + 1) | tostring)] = .[$i]
  )
'

Let’s break this down:

• -R: Reads input lines as raw strings (not pre-parsed JSON)
• -n: Enables "null input" mode, letting us use inputs to process every line in the file
• split("|"): Splits each line into an array using pipes as delimiters
• map(trim): Trims leading/trailing spaces from each field (skip this if your data is already clean)
• reduce: Iterates over each index in the split array, adding a key-value pair where the key is the index + 1 (converted to a string) and the value is the field content

Integrate This Into Your Existing Workflow #

You can pipe the output of your awk command directly into this jq command to keep your entire workflow streamlined:

# Example full pipeline (adjust your awk command to match your script)
gawk -f your_fixed_length_script.awk decompressed_file.txt | jq -Rn '
  inputs | 
  split("|") | 
  map(trim) | 
  reduce range(0; length) as $i ({}; 
    .[(($i + 1) | tostring)] = .[$i]
  )
' > final_json_output.json

Edge Case Notes #

• If your pipe-separated fields might contain literal pipes (unlikely with fixed-length conversions, but just in case), you’d need a more robust splitting method—but fixed-length parsing usually avoids this scenario.
• If some lines have missing fields, jq will set those keys to null automatically. You can add a check (like if .[$i] != "" then ...) if you need to handle empty fields differently.

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