Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JOLT spec to perform group by ID and condition check on nested json

Please help me to provide JOLT spec for the below input JSON. tried multiple approach but still not working. there is an condition check on risk attribute if risk is "0" then risklevel low .if it is "1" then High if it is null then it should be null only i got a solution but for null value output json attribute is not showing.

Input JSON :

[
  {
    "id": "12",
    "name": "Manu",
    "age": 26,
    "location": "New York",
    "risk": "0"
  },
  {
    "id": "12",
    "name": "Manju",
    "age": 29,
    "location": "New York",
    "risk": null
  },
  {
    "id": "123",
    "name": "sanju",
    "age": 24,
    "location": "New York city",
    "risk": "1"
  },
  {
    "id": "1234",
    "name": "Ramesh",
    "age": 23,
    "location": "India",
    "risk": null
  }
]

Expected JSON :

[
  {
    "id": "12",
    "location": "New York",
    "user": [
      {
        "name": "Manu",
        "age": 26,
        "riskLevel": "Low"
      },
      {
        "name": "Manju",
        "age": 29,
        "riskLevel": null
      }
    ]
  },
  {
    "id": "123",
    "location": "New York city",
    "user": [
      {
        "name": "sanju",
        "age": 24,
        "riskLevel": "High"
      }
    ]
  },
  {
    "id": "123",
    "location": "India",
    "user": [
      {
        "name": "Ramesh",
        "age": 23,
        "riskLevel": null
      }
    ]
  }
]
Tags:

2

Answers


  1. 0

    You can use the following transformation

    [ //set an arbitrary value(say "NUll") to a null valued "risk"
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "~risk": "NUll" //occures whnever risk is null due to the ~ operator 
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@1,id.&[]", //the attributes other than the ones below
        "name|age": "@1,id.&1.&",
        "risk": {
          "1": { "#High": "@3,id.&3.riskLevel" }, //hardcode by # operator
          "0": { "#Low": "@3,id.&3.riskLevel" },
          "NUll": { "@": "@3,id.&3.riskLevel" }
        }
      }
    }
  },
  {//nest some attributes with array "user" while pick the first occurence fron the newly formed "id" and "location" arrays
    "operation": "shift",
    "spec": {
      "*": {
        "id|location": {
          "0": "&2.&1"
        },
        "*": "&1.user[]"
      }
    }
  },
  { //get rid of the wrapper keys of "id" values
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]
    Login or Signup to reply.
  2. 0

    Thanks Barbaros for the awesome solution. I’m always learning form you and getting better in jolt because of your solutions. I would never knew that "~" has special meaning in jolt. May I ask how did you find out about it? is there url that you use for jolt guide that is comprehensive and has the latest?

    Also if I may provide a different way for the spec:

    [
  {
    //group user info under id & location
    "operation": "shift",
    "spec": {
      "*": {
        "age": "@(1,id).@(1,location).[#2].age",
        "name": "@(1,id).@(1,location).[#2].name",
        "risk": {
          "0": {
            "#low": "@(3,id).@(3,location).[#4].risklevel"
          },
          "1": {
            "#high": "@(3,id).@(3,location).[#4].risklevel"
          }
        }
      }
    }
  }
  ,
  {
    // create final structure by
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "$1": "[#3].id",
          "$": "[#3].location",
          // dont use array for users yet to avoid getting nulls from above  
          "*": "[#3].user"
        }
      }
    }
  }
  ,
  {
    //ensure user is always an array
    "operation": "cardinality",
    "spec": {
      "*": {
        "user": "MANY"
      }
    }
  }
  ,

  {
    // set risklevel to null by default if doesnt exist
    "operation": "default",
    "spec": {
      "*": {
        "user[]": {
          "*": {
            "risklevel": null
          }
        }
      }
    }
  }
  /**/
]

    Also since I have been experiencing with jslt , I would like to provide a much abbreviated way of solving this using jstl:

    import "http://jslt.schibsted.com/2018/experimental" as exp


    let groupBy= exp:group-by(.,
                                {"id":.id,"location":.location},
                                {"name":.name,"age":.age, "risklevel": if(.risk == "0") "low" else if (.risk=="1") "high" else null }
                              )
    let result = [for ($groupBy) {"id":.key.id,"location":.key.location,"user":.values}]
    
    $result
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search