How to parse json with jq where the structure is non-linear

Question

I hope I have represented my problem clearly. Need help querying and them parsing multiple json files using JQ where the structure is non-linear within each file. The application produces config data that can look like this example. There can be zero or many of the DualEndPoint or Local objects per file. I need to be able to query for a specific user in the "User" attribute and insert a new password for resubmission back to the api. For DualEndPoints, the nested object names are variable so one cannot code those values in looking for the "User" attribute.

Where a match for a specific user is found, return the entire structure with only that user's new password inserted. In the example, querying for user1 would return the entire PROFILE1 and PROFILE2, but not PROFILE3 as it doesn't contain user1 credentials.

{
  "PROFILE1": {
    "Type": "ConnectionProfile:FileTransfer:DualEndPoint",
    "WorkloadAutomationUsers": [
      "*"
    ],
    "VerifyBytes": true,
    "TargetAgent": "sqlrptvmjhbpr01",
    "TargetCTM": "Production",
    "Endpoint:Src:Local_0": {
      "Type": "Endpoint:Src:Local",
      "User": "user1",
      "Port": "0",
      "OsType": "Windows",
      "HostName": "Local",
      "Password": "*****",
      "HomeDirectory": "/user1homedir"
    },
    "Endpoint:Dest:SFTP_1": {
      "Type": "Endpoint:Dest:SFTP",
      "User": "user2",
      "HostName": "server2",
      "Password": "*****",
      "HomeDirectory": "/user2homedir"
    }
  },
  "PROFILE2": {
    "Type": "ConnectionProfile:FileTransfer:Local",
    "WorkloadAutomationUsers": [
      "*"
    ],
    "VerifyBytes": true,
    "User": "user1",
    "VerifyDestination": true,
    "OsType": "Windows",
    "HostName": "Local",
    "Password": "*****",
    "TargetAgent": "server1",
    "TargetCTM": "Production"
  },
  "PROFILE3": {
    "Type": "ConnectionProfile:FileTransfer:Local",
    "WorkloadAutomationUsers": [
      "*"
    ],
    "VerifyBytes": true,
    "User": "user3",
    "OsType": "Windows",
    "HostName": "Local",
    "Password": "*****",
    "HomeDirectory": "/user3hoemdir",
    "TargetAgent": "server2",
    "TargetCTM": "Production"
  }
}

Answer 1

With jq 1.6 you can use the following :

jq --arg newPwd "newPassword" \
     'walk(if type == "object" and .User == "user1" then .password |= $newPwd else . end)  
        | map_values(select(.. | select(type == "object") and .User == "user1"))' 

This will recurse over your JSON input and set the password field of objects that have a User : "user1" key/value pair to your desired value.

You can try it here.

In anterior versions you can use this equivalent :

jq --arg newPwd "newPassword" \
   'def rec :
      if type == "object" and .User == "user1" then 
        .password = $newPwd
      elif type == "object" then
        map_values(rec)
      elif type == "array" then
        map(rec)
      else
        .
      end
    ; 
    rec  | map_values(select(.. | select(type == "object") and .User == "user1"))'

You can try it here.

Answer 2

In the following solution to the stated problem, there are two steps. The first step uses with_entries to select the relevant "PROFILE" objects, and the second step uses walk to update the password provided there is a password. It is easy enough to parameterize everything, so for simplicity let's assume (as in the Q) that the user is "user1":

with_entries(select( .value
    | any(paths(. == "user1");
          .[-1] == "User" )))
| walk( if type == "object" and .User == "user1" and has("Password")
        then .Password = "newpassword"
        else .end)

The use of any here complicates things a bit but is for efficiency.

Note on walk/1

If your jq does not have walk/1, then now would be a good time to update your jq, but if that's not an option, simply google for its def (search terms: jq def walk builtin.jq) and copy the def to the beginning of your jq program.

Answer 3

Welcome to StackOverflow!

The full example was not a valid JSON object, and there is no attempt to solve the problem, or an example of what a desired outcome should look like. So the following answer comes with a bit of guesswork. It would probably increase the odds of getting a good answer greatly if the example was trimmed down to a minimal, reproducible example in which all the clutter is removed.

For example, "TargetAgent": "sqlrptvmjhbpr01" appears to be irrelevant information. The only effect this line has is to increase the cognitive load of the reader in trying to decipher if it's relevant to the problem task, which it appears not to be.

There can be zero or many of the DualEndPoint or Local objects per file.

You don't say exactly what a DualEndPoint or Local object is.

Since the text DualEndPoint only occurs in the context of

"Type": "ConnectionProfile:FileTransfer:DualEndPoint"

I assume that a DualEndPoint object is one that contains a key-value pair with the format

"Type": "...:DualEndPoint"

and that a Local object is the same, but with DualEndPoint substituted with Local. If that interpretation is correct, then there would be three examples of Local objects in your first code snippet at two different levels of nesting (which is what I understand as the "non-linear" part).

One example of a Local object would then be:

{
  "Type": "Endpoint:Src:Local",
  "User": "user1",
  "Port": "0",
  "OsType": "Windows",
  "HostName": "Local",
  "Password": "newpassword",
  "HomeDirectory": "/user1homedir"
}

There aren't any examples of similar objects that, in spite of containing a "User" attribute, should not be updated. So it appears that to answer the question, the distinction between these types of objects is also entirely unnecessary?

I need to be able to query for a specific user in the "User" attribute and insert a new password for resubmission back to the api.

So a sub-problem to your main problem could be to update an object with a new password if it's the right User. Asuming you've scoped the object down to such an object, a sub-part of the program could look like:

$ jq 'if .User == "user1" then .Password = "derp" else . end' local1.json
{
  "Type": "Endpoint:Src:Local",
  "User": "user1",
  "Port": "0",
  "OsType": "Windows",
  "HostName": "Local",
  "Password": "derp",
  "HomeDirectory": "/user1homedir"
}

For DualEndPoints, the nested object names are variable so one cannot code those values in looking for the "User" attribute.

So it sounds like you want to arbitrarily recurse looking for objects with "User" attributes. Some of jq's recursion combinators are .., the more general recurse and what appears to be more appropriate in this context, walk:

$ jq 'walk(if type == "object" and .User == "user1"
           then .Password = "derp"
           else . end)' full.json

(This is also what Aaron posted, except he uses |= and I use =.)

See his jqplay example or this jqplay example.

Where a match for a specific user is found, return the entire structure with only that user's new password inserted. In the example, querying for user1 would return the entire PROFILE1 and PROFILE2, but not PROFILE3 as it doesn't contain user1 credentials.

This sounds like an extra condition in the expression we walk with:

$ jq 'walk(if type == "object" and has("User")
           then (if .User == "user1"
                 then .Password = "derp"
                 else null end)
           else . end)' full.json

This appears to almost work (see this jqplay example), except it leaves "foo": null values as the result of having walked. This is a by-product of already having recursed down into the object containing the "User" property, which makes it hard to express that the parent key-value pair should be deleted.

Fixing this we need to either look ahead in walk/1's filter or create a placeholder and walk a second time with the perspective of the parent object. The latter of these two strategies is demonstrated here:

$ jq 'walk(if type == "object" and has("User")
      then (if .User == "user1" then .Password = "derp" else "wat" end)
      else . end)
      | walk(if type == "object"
             then with_entries(select(.value != "wat"))
             else . end)' full.json

This appears to work. See this jqplay example.