JQ: Extract value from object in nested array based on object name

Question

I have tried learning jq, but I can't really seem to wrap my head properly around it. (JSON and jq Newb here)

I need to extract some values from "Devices", and I need to get one value from the "CustomAttributes" array. The big problem is that the position of the data I need isn't fixed within this array, so I have to find the right object based on the name.

I need to export this as a CSV.

The expected output is :

SerialNumber: "aaabbbccc" 

EnrollmentUserName:"Donald"
 
os_vers: "10.15.7"

Does anyone have a recipe for this?

{
  "Devices": [
    {
      "DeviceId": 111,
      "Udid": "a813bf407aa55ed686f3d3ade3e6d3c4",
      "SerialNumber": "aaabbbccc",
      "EnrollmentUserName": "Donald",
      "AssetNumber": "A813BF407AA55ED686F3D3ADE3E6D3C4",
      "CustomAttributes": [
        {
          "Name": "reinstall",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "reinstall_keys",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "msc_version",
          "Value": "4.1.4.3949",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "reinstall_ssh_log",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "cert_date",
          "Value": "20210422",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "model_id",
          "Value": "iMac14,2",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "cfengine",
          "Value": "disabled",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "os_vers",
          "Value": "10.15.7",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "xprotect_vers",
          "Value": "2149",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "powerpoint",
          "Value": "true",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        }
      ]
    },
    {
      "DeviceId": 178,
      "Udid": "72893dbe48755ac99641ab2a3b8a5228",
      "SerialNumber": "CCCBBBAAA",
      "EnrollmentUserName": "Mickey",
      "AssetNumber": "",
      "CustomAttributes": [
        {
          "Name": "MobileAccount",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "reinstall",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "reinstall_keys",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "reinstall_ssh_log",
          "Value": "false",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "cert_date",
          "Value": "20210422",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "model_id",
          "Value": "MacBookPro12,1",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "dep_check",
          "Value": "checked",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "cfengine",
          "Value": "disabled",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        },
        {
          "Name": "os_vers",
          "Value": "11.5.2",
          "Source": "Device Sourced",
          "Application": "AirWatchAgent"
        }
      ]
    }
  ],
  "Page": 0,
  "PageSize": 5,
  "Total": 1831
}

Answer 1

This lists all triplets and a header row formatted as CSV:

jq --raw-output '
  [ "SerialNumber", "EnrollmentUserName", "os_vers" ], # header
  ( .Devices[]
    | [ .SerialNumber, .EnrollmentUserName,
        (.CustomAttributes[] | select(.Name == "os_vers").Value)
      ]
  )
  | @csv
' input.json

"SerialNumber","EnrollmentUserName","os_vers"
"aaabbbccc","Donald","10.15.7"
"CCCBBBAAA","Mickey","11.5.2"

Demo

If you only need one specific line, leave out the header (marked with #header) and apply another select after .Devices[] according to your criterion (e.g. .EnrollmentUserName == "Donald").