导入嵌套JSON到Amazon Redshift时如何解决‘Invalid JSONPath Format’错误？

问题场景 #

将嵌套JSON导入Redshift时触发错误：Invalid JSONPath format: Member is not an object.，核心原因是处理嵌套数组时的JSONPath写法不符合Redshift的解析规则。

原始JSON数据 #

{"UserId": 78910,"UserName": "johndoe123","AccountDetails": {"AccountType": "Premium","CreatedDate": "2023-01-15T08:30:00"},"RecentActivities": [{"Activity": "Logged In","Timestamp": "2023-10-10T14:20:00+00:00"},{"Activity": "Updated Profile","Timestamp": "2023-10-11T11:45:00+00:00"},{"Activity": "Purchased Item","ItemId": 45678,"Timestamp": "2023-10-12T09:00:00+00:00"}],"Preferences": {"Language": "en-US","NotificationsEnabled": true}}

原JSONPaths配置（错误写法） #

{"jsonpaths": ["$.UserId","$.UserName","$.AccountDetails.AccountType","$.AccountDetails.CreatedDate","$.RecentActivities[*].Activity","$.RecentActivities[*].Timestamp"]}

期望输出 #

解决方案 #

Redshift不允许在多个JSONPath表达式中重复使用[*]展开数组，这会导致解析器无法正确映射多值到单行。正确做法是通过父级遍历或指定数组展开根节点配置JSONPaths：

修改后的JSONPaths配置 #

{"jsonpaths": [
  "$..UserId",
  "$..UserName",
  "$..AccountDetails.AccountType",
  "$..AccountDetails.CreatedDate",
  "$.Activity",
  "$.Timestamp"
]}

原理说明 #

• $.. 是JSONPath递归下降运算符，会自动遍历所有父级节点，获取UserId、AccountType等顶层字段的值，确保数组展开后每一行都能继承这些字段。
• 数组内的Activity和Timestamp直接用$.引用，Redshift会自动以RecentActivities数组的每个元素作为当前上下文展开。

替代方案：使用JSON 'auto' #

如果不需要自定义字段映射，也可以在COPY命令中直接用JSON 'auto'参数，让Redshift自动推断JSON结构并展开数组：

COPY your_table_name
FROM 's3://your-bucket/path/to/json/files/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
JSON 'auto';

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