I created a simple select to test the result of a MySQL installation in an ansible playbook:
- name: Simple select query to db playground
community.mysql.mysql_query:
login_db: {{ database name }}
login_user: {{ login_user }}
login_password: {{ login_password }}
query: SELECT * FROM equipment;
register: r_query
I register the result of that task into a variable r_query.
When I debug the query_result of this variable I get the following:
ok: [db01] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
ok: [db02] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
ok: [db03] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
I already checked some explanations how to extract the single values but nothing worked yet.
How can I access the second color value of the result of the first machine db01?
I created a simple select to test the result of a MySQL installation in an ansible playbook:
- name: Simple select query to db playground
community.mysql.mysql_query:
login_db: {{ database name }}
login_user: {{ login_user }}
login_password: {{ login_password }}
query: SELECT * FROM equipment;
register: r_query
I register the result of that task into a variable r_query.
When I debug the query_result of this variable I get the following:
ok: [db01] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
ok: [db02] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
ok: [db03] => {
"msg": [
[
{
"color": "white",
"id": 2,
"quant": 1,
"type": "shark"
},
{
"color": "grey",
"id": 4,
"quant": 2,
"type": "whale"
}
]
]
}
I already checked some explanations how to extract the single values but nothing worked yet.
How can I access the second color value of the result of the first machine db01?
1 Answer
Reset to default 1Let's create the inventory below and change the second colors to red, green, and blue for testing
shell> cat hosts
all:
hosts:
db01:
r_query:
- - {color: white, id: 2, quant: 1, type: shark}
- {color: red, id: 4, quant: 2, type: whale}
db02:
r_query:
- - {color: white, id: 2, quant: 1, type: shark}
- {color: green, id: 4, quant: 2, type: whale}
db03:
r_query:
- - {color: white, id: 2, quant: 1, type: shark}
- {color: blue, id: 4, quant: 2, type: whale}
See hostvars. This dictionary keeps all variables for all hosts. For example,
- debug:
var: hostvars[inventory_hostname].r_query.0.1.color
gives
ok: [db01] =>
hostvars[inventory_hostname].r_query.0.1.color: red
ok: [db02] =>
hostvars[inventory_hostname].r_query.0.1.color: green
ok: [db03] =>
hostvars[inventory_hostname].r_query.0.1.color: blue
To "access the second color value of the result of the first machine db01"
- debug:
var: hostvars.db01.r_query.0.1.color
gives
ok: [db01] =>
hostvars.db01.r_query.0.1.color: red
ok: [db02] =>
hostvars.db01.r_query.0.1.color: red
ok: [db03] =>
hostvars.db01.r_query.0.1.color: red
A host doesn't need hostvars to access its variables, of course
- debug:
var: r_query.0.1.color
gives
ok: [db01] =>
r_query.0.1.color: red
ok: [db02] =>
r_query.0.1.color: green
ok: [db03] =>
r_query.0.1.color: blue
Example of a complete playbook for testing
- hosts: all
tasks:
- debug:
var: hostvars[inventory_hostname].r_query.0.1.color
- debug:
var: hostvars.db01.r_query.0.1.color
- debug:
var: r_query.0.1.color
hostvars also help you to create reports for all hosts in a play. For example,
- debug:
msg: |
{% for h in ansible_play_hosts %}
{{ h }}: {{ hostvars[h].r_query.0.1.color }}
{% endfor %}
run_once: true
gives
msg: |-
db01: red
db02: green
db03: blue
You can extract certain attributes and declare a dictionary. For example,
colors_2nd: "{{ dict(ansible_play_hosts |
zip(ansible_play_hosts |
map('extract', hostvars, ['r_query', 0, 1, 'color']))) }}"
makes the access to the second color value trivial
colors_2nd:
db01: red
db02: green
db03: blue
To create the dictionary, you can also use Jinja. The declaration below gives the same result
colors_2nd: |
{% filter from_yaml %}
{% for h in ansible_play_hosts %}
{{ h }}: {{ hostvars[h].r_query.0.1.color }}
{% endfor %}
{% endfilter %}