如何用jq命令提取指定JSON字段，缺失时返回默认值？

核心jq实现命令 #

针对多层JSON结构，使用jq的默认值运算符//处理字段缺失场景，直接构造包含目标字段的对象，缺失字段返回"not-exist"：

# 假设设备数据在.devices数组中，若为单个对象则去掉.devices[]
.devices[] | {
  ICCID: .iccid // "not-exist",
  MDN: .mdn // "not-exist",
  MSISDN: .msisdn // "not-exist",
  MCC: .network.mcc // "not-exist",
  MNC: .network.mnc // "not-exist",
  FeatureTypes: (.plan.featureTypes // ["not-exist"]) | tostring,
  RatePlanCode: .plan.ratePlanCode // "not-exist",
  RatePlanDescription: .plan.ratePlanDescription // "not-exist",
  DeviceState: .status.deviceState // "not-exist",
  BillingCycleStartDate: .billing.billingCycleStartDate // "not-exist",
  BillingCycleEndDate: .billing.billingCycleEndDate // "not-exist",
  CurrentBillCycleDataUnRatedUsage: .billing.currentBillCycleDataUnRatedUsage // "not-exist"
}

关键逻辑说明 #

1. 遍历数据：.devices[]用于遍历JSON中的设备数组，若你的数据是单个设备对象，直接删除这部分即可。
2. 缺失值处理：// "not-exist"是jq的默认值运算符——当左侧字段不存在、为null或未定义时，自动返回右侧的"not-exist"。即使中间嵌套层级缺失（比如某设备没有network节点），链式访问会返回null，此时//依然生效。
3. 数组字段处理：针对FeatureTypes这类数组字段，先给默认数组["not-exist"]，再用tostring转为字符串（若数据库支持数组类型，可去掉tostring）。

示例验证 #

模拟输入JSON（多层结构）

{
  "devices": [
    {
      "iccid": "8912345678901234567",
      "mdn": "1234567890",
      "msisdn": "1234567890",
      "network": {
        "mcc": "310",
        "mnc": "410"
      },
      "plan": {
        "featureTypes": ["DATA", "VOICE"],
        "ratePlanCode": "RP001",
        "ratePlanDescription": "Unlimited Data Plan"
      },
      "status": {
        "deviceState": "ACTIVE"
      },
      "billing": {
        "billingCycleStartDate": "2024-01-01",
        "billingCycleEndDate": "2024-01-31",
        "currentBillCycleDataUnRatedUsage": "15.2GB"
      }
    },
    {
      "iccid": "8976543210987654321",
      "network": {
        "mcc": "310"
      },
      "plan": {
        "ratePlanCode": "RP002"
      },
      "status": {},
      "billing": {
        "billingCycleStartDate": "2024-01-01"
      }
    }
  ]
}

执行jq命令后输出

{
  "ICCID": "8912345678901234567",
  "MDN": "1234567890",
  "MSISDN": "1234567890",
  "MCC": "310",
  "MNC": "410",
  "FeatureTypes": "[\"DATA\",\"VOICE\"]",
  "RatePlanCode": "RP001",
  "RatePlanDescription": "Unlimited Data Plan",
  "DeviceState": "ACTIVE",
  "BillingCycleStartDate": "2024-01-01",
  "BillingCycleEndDate": "2024-01-31",
  "CurrentBillCycleDataUnRatedUsage": "15.2GB"
}
{
  "ICCID": "8976543210987654321",
  "MDN": "not-exist",
  "MSISDN": "not-exist",
  "MCC": "310",
  "MNC": "not-exist",
  "FeatureTypes": "[\"not-exist\"]",
  "RatePlanCode": "RP002",
  "RatePlanDescription": "not-exist",
  "DeviceState": "not-exist",
  "BillingCycleStartDate": "2024-01-01",
  "BillingCycleEndDate": "not-exist",
  "CurrentBillCycleDataUnRatedUsage": "not-exist"
}

适配数据库导入（CSV格式） #

如果需要直接导入数据库，可将输出转为CSV格式，调整后的jq命令：

.devices[] | [
  .iccid // "not-exist",
  .mdn // "not-exist",
  .msisdn // "not-exist",
  .network.mcc // "not-exist",
  .network.mnc // "not-exist",
  (.plan.featureTypes // ["not-exist"]) | join(","),
  .plan.ratePlanCode // "not-exist",
  .plan.ratePlanDescription // "not-exist",
  .status.deviceState // "not-exist",
  .billing.billingCycleStartDate // "not-exist",
  .billing.billingCycleEndDate // "not-exist",
  .billing.currentBillCycleDataUnRatedUsage // "not-exist"
] | @csv

内容的提问来源于stack exchange，提问作者Mr.Simple