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
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