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

Question

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

Answer 1

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