jsonnosqljsoniq

Storing an object with key value pairs in a variable in JSONiq


This is a sample JSON array

"transcript":[
       {
         "dcode": "CS",
         "cno": 211,
         "ssn": 82,
         "grade": "A"
       },
       {
         "dcode": "CS",
         "cno": 211,
         "ssn": 75,
         "grade": "A"
       },
       {
         "dcode": "MTH",
         "cno": 125,
         "ssn": 82,
         "grade": "F"
       }
 
 ]

I need to create a variable $courses which gives me all the courses taken by a student whose ssn is 82. How do I loop over this and create this variable?

I want the output to be $courses:= {"CS" : 211, "MTH" : 125}

I tried to write the loop in {} brackets but it did not work. I am new to Jsoniq


Solution

  • First a quick comment: you need curly braces to make sure this is well-formed (JSON and JSONiq):

    {
      "transcript": [
        {
          "dcode": "CS",
          "cno": 211,
          "ssn": 82,
          "grade": "A"
        },
        {
          "dcode": "CS",
          "cno": 211,
          "ssn": 75,
          "grade": "A"
        },
        {
          "dcode": "MTH",
          "cno": 125,
          "ssn": 82,
          "grade": "F"
        }
      ]
    }
    

    Assuming this object is stored in a variable $x, you can obtain the desired object with

    $x.transcript[][$$.ssn eq 82]
    

    where [] unboxes the array into a sequence of objects, and [$$.ssn eq 82] filters this sequence keeping only those where ssn is 82.

    So a complete query would be:

    let $x := {
      "transcript":[
        {
          "dcode": "CS",
          "cno": 211,
          "ssn": 82,
          "grade": "A"
        },
        {
          "dcode": "CS",
          "cno": 211,
          "ssn": 75,
          "grade": "A"
        },
        {
          "dcode": "MTH",
          "cno": 125,
          "ssn": 82,
          "grade": "F"
        }
      ]
    }
    return $x.transcript[][$$.ssn eq 82]
    

    There is also the alternative with an explicit FLWOR for clause and where clause (which are extensible at will with more complex use cases):

      let $x := {
        "transcript":[
          {
            "dcode": "CS",
            "cno": 211,
            "ssn": 82,
            "grade": "A"
          },
          {
            "dcode": "CS",
            "cno": 211,
            "ssn": 75,
            "grade": "A"
          },
          {
            "dcode": "MTH",
            "cno": 125,
            "ssn": 82,
            "grade": "F"
          }
        ]
      }
      for $course in $x.transcript[]
      where $course.ssn eq 82
      return $course
    

    Now, we can bind these results to a variable $courses with a let clause -- it is important to understand that this is a functional language, that is, these are bindings and not assignments. In this simple case, the return clause returns the contents of $courses, but it is also possible to do anything else you want with this variable instead.

    let $courses :=
      let $x := {
        "transcript":[
          {
            "dcode": "CS",
            "cno": 211,
            "ssn": 82,
            "grade": "A"
          },
          {
            "dcode": "CS",
            "cno": 211,
            "ssn": 75,
            "grade": "A"
          },
          {
            "dcode": "MTH",
            "cno": 125,
            "ssn": 82,
            "grade": "F"
          }
        ]
      }
      for $course in $x.transcript[]
      where $course.ssn eq 82
      return $course
    return $courses