Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How do I represent multiple host data into a single JSON or a table?

mahen1812
April 8, 2023
181 views
0 votes
2 Answers

I am getting the v_data from 2 different hosts: HOST-1, HOST-2.

TASK [Print v_data] **************************************************************************************************
ok: [HOST-1] => {
    "v_data": [
        {
            "vrf": "vrf-11",
            "ip": "10.10.10.1",
            "policy": "RP-11",
            "max-pref": "15",
            "thresh": "15%"
        },
        {
            "vrf": "vrf-12",
            "ip": "10.10.10.2",
            "policy": "RP-12",
            "max-pref": "15",
            "thresh": "15%"
        }
    ]
}
ok: [HOST-2] => {
    "v_data": [
        {
            "vrf": "vrf-21",
            "ip": "20.20.20.1",
            "policy": "RP-21",
            "max-pref": "15",
            "thresh": "15%"
        },
        {
            "vrf": "vrf-22",
            "ip": "20.20.20.2",
            "policy": "RP-22",
            "max-pref": "25",
            "thresh": "25%"
        }
    ]
}

I get the data into a table format if I convert it into a table (single host).

And I am doing the below to get the data into a singe json/table:

- name: Get v_data
  shell: echo "{{ v_data }}"
  register: v_data
  loop: "{{ ansible_play_hosts }}"
  vars:
    vd: "{{ hostvars[item]['v_data'] }}"

- name: Create data structure
  set_fact:
    host_data: "{{ host_data | default([]) + [{'host': item.item, 'v_data': item.stdout | from_json }] }}"
  with_items: "{{ v_data.results }}"

- name: Print data structure
  debug:
    var: host_data

But I want to get the data as below and hence I can convert the same to a CSV or a table as below:

{
  "data": [
    {
      "host": "HOST-1",
      "v_data": [
        {
          "vrf": "vrf-11",
          "ip": "10.10.10.1",
          "policy": "RP-11",
          "max-pref": "15",
          "thresh": "15%"
        },
        {
          "vrf": "vrf-12",
          "ip": "10.10.10.2",
          "policy": "RP-12",
          "max-pref": "15",
          "thresh": "15%"
        }
      ]
    },
    {
      "host": "HOST-1",
      "v_data": [
        {
          "vrf": "vrf-21",
          "ip": "20.20.20.1",
          "policy": "RP-21",
          "max-pref": "15",
          "thresh": "15%"
        },
        {
          "vrf": "vrf-22",
          "ip": "20.20.20.2",
          "policy": "RP-22",
          "max-pref": "25",
          "thresh": "25%"
        }
      ]
    }
  ]
}

Even if I have hosts, HOST-1, HOST-2, .....HOST-n,
how to represent/convert the data from each inventory_hostname and combine/merge to a single JSON in order to further convert to a table.
not sure I can use the term combine/merge. final output should be as shown in data and screenshot

Tags: ansible json

Answers

In a nutshell. You can debug intermediate variables in the task to better understand the process but the different steps and corresponding variable names should be enough for the first idea.

Note: I created the following fake inventories/demo/hosts.yml inventory to run against your example data:

all:
  vars:
    ansible_connection: local
  hosts:
    HOST-1:
      "v_data": [
        {
            "vrf": "vrf-11",
            "ip": "10.10.10.1",
            "policy": "RP-11",
            "max-pref": "15",
            "thresh": "15%"
        },
        {
            "vrf": "vrf-12",
            "ip": "10.10.10.2",
            "policy": "RP-12",
            "max-pref": "15",
            "thresh": "15%"
        }
      ]
    HOST-2:
      "v_data": [
        {
            "vrf": "vrf-21",
            "ip": "20.20.20.1",
            "policy": "RP-21",
            "max-pref": "15",
            "thresh": "15%"
        },
        {
            "vrf": "vrf-22",
            "ip": "20.20.20.2",
            "policy": "RP-22",
            "max-pref": "25",
            "thresh": "25%"
        }
      ]

Using the above, the merge_json.yml demo playbook:

---
- hosts: all
  gather_facts: false

  tasks:
    - name: Print v_data
      ansible.builtin.debug:
        var: v_data

    - name: print a single json containing all data as per OP requirements
      vars:
        list_of_hosts: "{{ ansible_play_hosts }}"
        list_of_v_data: "{{ ansible_play_hosts | map('extract', hostvars, 'v_data') }}"
        data_dict: "{{ dict(list_of_hosts | zip(list_of_v_data)) }}"
        data: "{{ data_dict | dict2items('host', 'v_data') }}"
      ansible.builtin.debug:
        var: v_data_list
      run_once: true

returns:

$ ansible-playbook -i inventories/demo/ merge_json.yaml 

PLAY [all] *****************************************************************************************************************************************************************************************************************************

TASK [Print v_data] ********************************************************************************************************************************************************************************************************************
ok: [HOST-1] => {
    "v_data": [
        {
            "ip": "10.10.10.1",
            "max-pref": "15",
            "policy": "RP-11",
            "thresh": "15%",
            "vrf": "vrf-11"
        },
        {
            "ip": "10.10.10.2",
            "max-pref": "15",
            "policy": "RP-12",
            "thresh": "15%",
            "vrf": "vrf-12"
        }
    ]
}
ok: [HOST-2] => {
    "v_data": [
        {
            "ip": "20.20.20.1",
            "max-pref": "15",
            "policy": "RP-21",
            "thresh": "15%",
            "vrf": "vrf-21"
        },
        {
            "ip": "20.20.20.2",
            "max-pref": "25",
            "policy": "RP-22",
            "thresh": "25%",
            "vrf": "vrf-22"
        }
    ]
}

TASK [print a single json containing all data as per OP requirements] ******************************************************************************************************************************************************************
ok: [HOST-1] => {
    "data": [
        {
            "host": "HOST-1",
            "v_data": [
                {
                    "ip": "10.10.10.1",
                    "max-pref": "15",
                    "policy": "RP-11",
                    "thresh": "15%",
                    "vrf": "vrf-11"
                },
                {
                    "ip": "10.10.10.2",
                    "max-pref": "15",
                    "policy": "RP-12",
                    "thresh": "15%",
                    "vrf": "vrf-12"
                }
            ]
        },
        {
            "host": "HOST-2",
            "v_data": [
                {
                    "ip": "20.20.20.1",
                    "max-pref": "15",
                    "policy": "RP-21",
                    "thresh": "15%",
                    "vrf": "vrf-21"
                },
                {
                    "ip": "20.20.20.2",
                    "max-pref": "25",
                    "policy": "RP-22",
                    "thresh": "25%",
                    "vrf": "vrf-22"
                }
            ]
        }
    ]
}

PLAY RECAP *****************************************************************************************************************************************************************************************************************************
HOST-1                     : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
HOST-2                     : ok=1    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Given the inventory

shell> cat hosts
host_1
host_2
host_3

and the host_vars with simplified variables v_data for testing

shell> tree host_vars
host_vars
├── host_1
│   └── vdata.yml
├── host_2
│   └── vdata.yml
└── host_3
    └── vdata.yml

3 directories, 3 files

shell> find host_vars -type f | xargs cat
v_data: data for host_1
v_data: data for host_3
v_data: data for host_2

The playbook

- hosts: all

  vars:

    data: "{{ dict(hostvars|dict2items|
                   json_query('[].[key, value.v_data]')) }}"

  tasks:

    - set_fact:
        v_data: "{{ v_data }}"
    - debug:
        var: data
      run_once: true

combines the data into a single dictionary

  data:
    host_1: data for host_1
    host_2: data for host_2
    host_3: data for host_3

Fit the query to your needs. For example,

  data: "{{ dict(hostvars|dict2items|
                 json_query('[].[key, {vdata: value.v_data}]')) }}"

gives

  data:
    host_1:
      vdata: data for host_1
    host_2:
      vdata: data for host_2
    host_3:
      vdata: data for host_3

To display the dictionary in JSON use the filter to_json or to_nice_json

    - debug:
        msg: |
          data:
            {{ data|to_nice_json(indent=2)|indent(2) }}

gives

  msg: |-
    data:
      {
        "host_1": {
          "vdata": "data for host_1"
        },
        "host_2": {
          "vdata": "data for host_2"
        },
        "host_3": {
          "vdata": "data for host_3"
        }
      }

You can select the values only

    - debug:
        msg: |
          data:
            {{ data.values()|list|to_nice_json(indent=2)|indent(2) }}

gives

  msg: |-
    data:
      [
        {
          "vdata": "data for host_1"
        },
        {
          "vdata": "data for host_2"
        },
        {
          "vdata": "data for host_3"
        }
      ]

Create csv files

Given the data

shell> find host_vars -type f | xargs cat
v_data: [
        {
            "ip": "10.10.10.1",
            "max-pref": "15",
            "policy": "RP-11",
            "thresh": "15%",
            "vrf": "vrf-11"
        },
        {
            "ip": "10.10.10.2",
            "max-pref": "15",
            "policy": "RP-12",
            "thresh": "15%",
            "vrf": "vrf-12"
        }
    ]
v_data: [
        {
            "ip": "30.30.30.1",
            "max-pref": "15",
            "policy": "RP-31",
            "thresh": "15%",
            "vrf": "vrf-31"
        },
        {
            "ip": "30.30.30.2",
            "max-pref": "25",
            "policy": "RP-32",
            "thresh": "35%",
            "vrf": "vrf-32"
        }
    ]
v_data: [
        {
            "ip": "20.20.20.1",
            "max-pref": "15",
            "policy": "RP-21",
            "thresh": "15%",
            "vrf": "vrf-21"
        },
        {
            "ip": "20.20.20.2",
            "max-pref": "25",
            "policy": "RP-22",
            "thresh": "25%",
            "vrf": "vrf-22"
        }
    ]

the play

- hosts: all

  vars:

    data: "{{ dict(hostvars|dict2items|
                   json_query('[].[key, {vdata: value.v_data}]')) }}"
    keys: [ip, max-pref, policy, thresh, vrf]

  tasks:

    - set_fact:
        v_data: "{{ v_data }}"

    - copy:
        dest: /tmp/test/data.csv
        content: |
          host,{{ keys|join(',') }}
          {% for k,v in data.items() %}
          {% for i in v.vdata %}
          {{ k }},{{ keys|map('extract', i)|join(',') }}
          {% endfor %}
          {% endfor %}
      run_once: true
      delegate_to: localhost

will create the csv file

shell> cat /tmp/test/data.csv 
host,ip,max-pref,policy,thresh,vrf
host_1,10.10.10.1,15,RP-11,15%,vrf-11
host_1,10.10.10.2,15,RP-12,15%,vrf-12
host_2,20.20.20.1,15,RP-21,15%,vrf-21
host_2,20.20.20.2,25,RP-22,25%,vrf-22
host_3,30.30.30.1,15,RP-31,15%,vrf-31
host_3,30.30.30.2,25,RP-32,35%,vrf-32

Please signup or login to give your own answer.

Click here to cancel reply.