Ansible double loop in hostvars

I want to loop over the variables of all the hosts of a specific group.

With the following data in hostvars (simplified):

{
    "server1": {
        "group_names": ["web"],
        "sites": {
            "website1": { "id": "site1", "domain": "..." }
            "website2": { "id": "site2", "domain": "..." }
         },
     }
     "server2": {
        "group_names": ["web"],
        "sites": {
            "website3": { "id": "site3", "domain": "..." }
        }
     }
}

I want on an third server (the database server) create all the db:

- name: create databases
- mysql_db:
      name: "{{ item.1.id }}"
- with_subelements:
      - "{{ groups["web"] }}"
      - "{{ hostvars[item.0]['sites']

This doesn't work, I have the error: {"failed": true, "msg": "'item' is undefined"}

How should I write the role ?

Here are some jinja magic for you:

inventory:

localhost ansible_ssh_host=127.0.0.1 ansible_connection=local

[web]
host1 sites="{'web1':{'id':'z1'},'web2':{'id':'z2'}}"
host2 sites="{'web3':{'id':'z3'}}"

demo:

---
- hosts: localhost
  gather_facts: no
  tasks:
    - debug:
        msg: "Site name – {{ item.key }}, id - {{ item.value.id }}"
      with_dict: "{{ dict(groups['web'] | map('extract',hostvars,'sites') | map('dictsort') | sum(start=[]) | list) }}"

I do dictsort->sum->dict trick, because your sites object is a dictionary (not list) and it's not that easy to iterate over nested dicts.