Fetching JSON data with JSONiq

Question

Let be those two simple JSON files

      jsoniq version "1.0";

    let $tweets := 
    { 
    { "screen_name": "ifp_tuebingen"
    },
    { "screen_name": "ifp_tuebingen"
    },
    { "screen_name": "ifp_reutlingen"
    }
    }

    let $users := 
    {
    { "screen_name": "ifp_tuebingen"
    },
    { "screen_name": "ifp_reutlingen"
    }

}

I want to write a JSONiq query that determines for each user, how many tweets does he have based on his screen_name, an expected output for this example is

{ "2" : "ifp_tuebingen" }{ "1" : "ifp_reutlingen" }

I wrote the following code

for $u in $users
let $counter := 0
for $t in $tweets
where $u.screen_name eq $t.screen_name
let $counter := $counter+1
return {$counter : $u.screen_name}

it produces the following output instead

{ "1" : "ifp_tuebingen" }{ "1" : "ifp_tuebingen" }{ "1" : "ifp_reutlingen" }

Answer 1

JSONiq is a declarative language, meaning that it can group and count automagically, unlike imperative languages in which one must manually increase a counter:

let $tweets := (
  { "screen_name": "ifp_tuebingen" },
  { "screen_name": "ifp_tuebingen" },
  { "screen_name": "ifp_reutlingen" }
)
return
for $t in $tweets
group by $screen-name := $t.screen_name
let $count := count($t)
return { $count : $screen-name }

In general, it would be more common practice to have the count as a value and the screen name as the key in the results, i.e., swap $count and $u.screen_name:

...
return { $screen-name : $count }

JSONiq can then re-assemble these results into a single object, since the screen names after grouping will be unique keys:

...
return
{|
  for $t in $tweets
  group by $screen-name := $t.screen_name
  let $count := count($t)
  return { $screen-name : $count }
|}

Note that the $users data is not needed in this case. It would only be needed if there were extra metadata in the users, in which case one could do a join.