Redshift导入JSON数据失败：JSONPaths格式错误（err_code:1216）求助

The error you're hitting (err_code: 1216) stems from a mismatch between Redshift's required JSONPaths format and your current file structure. Redshift expects each path entry to be wrapped in an object with a jsonpath key—your original file uses raw strings, which violates this schema.

Corrected JSONPaths File #

Replace your existing JSONPaths content with this properly formatted version:

{
  "jsonpaths": [
    { "jsonpath": "$.id" },
    { "jsonpath": "$.costs[0].blue" },
    { "jsonpath": "$.costs[0].location" },
    { "jsonpath": "$.costs[0].sport" }
  ]
}

Why This Fix Works #

Redshift’s COPY command parses JSONPaths files with strict expectations: every element in the jsonpaths array must be an object containing the jsonpath property. Your initial file used direct string values, which Redshift can’t interpret correctly—hence the "Invalid JSONPath format" error.

Additional Tips for Smooth Import #

1. Validate JSON Syntax: Ensure your JSONPaths file has no trailing commas or syntax typos. Even a tiny mistake like an extra comma can break parsing.
2. Verify Path Expressions: Double-check that your JSONPath targets match your source data. For example, $.costs[0] correctly accesses the first element in the costs array, which aligns with your sample JSON.
3. Sample COPY Command: Here’s a working example of the COPY command to use with your corrected file (adjust bucket paths and IAM role to match your setup):

COPY your_target_table
FROM 's3://your-bucket/path/to/your-data.json'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-import-role'
JSON 's3://your-bucket/path/to/corrected-jsonpaths.json';

This should resolve the 1216 error and import your data into the Redshift table as intended.

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