I'm a JQ newb, and have been wrestling with this task for while. Our source JSON file looks like this...

[
{
  "id": "xxxx",
  "title": "xxxx",
  "created_at": "xxxx",
  "updated_at": "xxxx",
  "fields": [
    {
      "usernamevalue": "xxxx",
      "reference": "xxxx"
    },
    {
      "passwordvalue": "xxxx",
      "reference": "xxxx"
    },
    {
      "otherlabel": "xxxx",
      "otherreference": "xxxx"
    }
  ]
},
{
  "id": "xxxx",
  "title": "xxxx",
  "created_at": "xxxx",
  "updated_at": "xxxx",
  "fields": [
    {
      "usernamevalue": "xxxx",
      "reference": "xxxx"
    },
    {
      "passwordvalue": "xxxx",
      "reference": "xxxx"
    },
    {
      "otherlabel": "xxxx",
      "otherreference": "xxxx"
    }
  ]
}
]

We are trying to end up with...

[
{
  "title": "xxxx",
  "updated_at": "xxxx",
  "usernamevalue": "xxxx",
  "passwordvalue": "xxxx"
},
{
  "title": "xxxx",
  "updated_at": "xxxx",
  "usernamevalue": "xxxx",
  "passwordvalue": "xxxx"
}
]

As such, the goals are... End up with one, "flat" item for each entry -- so that we can then convert it to a CSV. Only get the "title" and "updated_at" fields from the top level. Only get fields with keys = "usernamevalue" and "passwordvalue" from the subordinate "fields" array, and move those fields to the top level.

Any guidance would be GREATLY appreciated! (I've already spent more hours on this than I care to admit.) THANK YOU

CodePudding user response：

If usernamevalue and passwordvalue have only one occurrence as in your sample file, you can add the fields object, and then extract from it as you would with the outside keys:

jq 'map({id, updated_at}   (.fields | add | {usernamevalue, passwordvalue}))' 

[
  {
    "id": "xxxx",
    "updated_at": "xxxx",
    "usernamevalue": "xxxx",
    "passwordvalue": "xxxx"
  },
  {
    "id": "xxxx",
    "updated_at": "xxxx",
    "usernamevalue": "xxxx",
    "passwordvalue": "xxxx"
  }
]

Demo

If your ultimate goal is a CSV output of the values, you'd rather go for an array than an object, which could be something like:

jq -r '.[] | [.id, .updated_at, (.fields | add | .usernamevalue, .passwordvalue)] | @csv'

"xxxx","xxxx","xxxx","xxxx"
"xxxx","xxxx","xxxx","xxxx"

Demo

CodePudding user response：

You could parse the JSON object through their keys.

var jsonObject = [
{
    "foo": "bar",
    "foo1": "bar1",
    "field": [
        {
            "username": "[email protected]",
            "context": "foob"
        }, 
        {
            "password": "kek",
            "context": "bar1"
        },
        {
            "other": "foobar",
            "context": "foobarbar"
        }
    ]
}
]

for (var i = 0; i < jsonObject.field.length; i  )
{
    for(var key in jsonObject.field[i])
    {
        if(key == "username") 
        {
            jsonObject.usernamevalue = jsonObject.field[i].username;
        } else if(key == "password")
        {
            jsonObject.passwordvalue = jsonObjecct.field[i].password;
        }
    }
}

//Afterwards, you can delete the field 
delete jsonObject.field;