Issue with isolating returned values of a `postgresql_query` task

[dulhaver (Gunnar)]

I want to get the result of a query (the value of log_hostname paramter) returned back from a playbook like the below

---

- name: get postgres settings
  hosts: "{{ targets | default('postgres') }}"
  vars:
   setting: "{{ pg_setting }}"

  tasks:

    - name: get a setting
      become: true
      become_user: postgres
      community.postgresql.postgresql_query:
        login_unix_socket: "/tmp"
        db: postgres
        query: "{{ query }}"
      loop:
        - "SHOW {{ setting }}"
      loop_control:
        loop_var: query
      register: test_result

    - name: debug test_result
      debug:
        msg: "{{ test_result }}"

what I am getting back (no surprise) is

TASK [debug test_result] *********************************************************************************************************************************************************
ok: [dvzsn-rd5108.efa-bauen.portal.cn-mv.de] => {
    "msg": {
        "changed": true,
        "msg": "All items completed",
        "results": [
            {
                "ansible_loop_var": "query",
                "changed": true,
                "failed": false,
                "invocation": {
                    "module_args": {
                        "as_single_query": true,
                        "autocommit": false,
                        "ca_cert": null,
                        "connect_params": {},
                        "db": "postgres",
                        "encoding": null,
                        "login_host": "",
                        "login_password": "",
                        "login_unix_socket": "/tmp",
                        "login_user": "postgres",
                        "named_args": null,
                        "path_to_script": null,
                        "port": 5432,
                        "positional_args": null,
                        "query": "SHOW log_hostname",
                        "search_path": null,
                        "session_role": null,
                        "ssl_mode": "prefer",
                        "trust_input": true
                    }
                },
                "query": "SHOW log_hostname",
                "query_all_results": [
                    [
                        {
                            "log_hostname": "on"
                        }
                    ]
                ],
                "query_list": [
                    "SHOW log_hostname"
                ],
                "query_result": [
                    {
                        "log_hostname": "on"
                    }
                ],
                "rowcount": 1,
                "statusmessage": "SHOW"
            }
        ],
        "skipped": false
    }
}

to get deeper into the deatils the first step I am taking is to replace

msg: "{{ test_result }}"

with

msg: "{{ test_result.results }}"

this peels off the outer layer of the output, but I can’t mange to get into more detail then that.

I tried adding more . like msg: "{{ test_result.results.query_result }}", but that fails with some

‘list object’ has no attribute ‘query_result’

error

I guess knowing the logic of parsing json with jq would be the way to go, but I can not get that going quite yet

[chris (Chris Croome)]

There are many ways this can be done, I’d probably use the community.general.json_query filter, this is fairly easy to test and debug since jpterm can be used (best use the community fork as it is being maintained):

[binbashroot]

You don’t need to parse with jq. You have a dict inside a list so you have to drill down a bit more to get the info you want

You can use native jinja filters to get the info:

# Get query_result
- debug:
    var: test_result['results'] | flatten |map(attribute='query_result')

# flatten even more
- debug:
    var: test_result['results'] | flatten |map(attribute='query_result')  | flatten | first

[dulhaver (Gunnar)]

we are moving into the right direction.

plaid a bit with what you suggest but am not getting more flat then the first suggetion

    - name: debug test_result
      debug:
        msg: "{{ test_result }}"

    - name: debug test_result flat
      debug:
        msg: "{{ test_result }}['results'] | flatten | map(attribute='query_result')"

    - name: debug test_result - even flatter-1
      debug:
        msg: "{{ test_result }}['results'] | flatten | map(attribute='query_result') | flatten | first"

    - name: debug test_result - even flatter-2
      debug:
        msg: "{{ test_result }}['results'] | flatten | map(attribute='query_result') | flatten | map(attribute='{{ query }}')"

all flat and even-flatter variants provide me with the same exect output

TASK [debug test_result - even flatter-2] ****************************************************************************************************************************************************
ok: [rd5108.efa-hauen.cn-mv.de] => {
    "msg": "{'results': [{'changed': True, 'query': 'SHOW log_hostname', 'query_list': ['SHOW log_hostname'], 'statusmessage': 'SHOW', 'query_result': [{'log_hostname': 'off'}], 'query_all_results': [[{'log_hostname': 'off'}]], 'rowcount': 1, 'invocation': {'module_args': {'login_unix_socket': '/tmp', 'db': 'postgres', 'query': 'SHOW log_hostname', 'login_user': 'postgres', 'login_password': '', 'login_host': '', 'port': 5432, 'ssl_mode': 'prefer', 'connect_params': {}, 'autocommit': False, 'trust_input': True, 'as_single_query': True, 'ca_cert': None, 'positional_args': None, 'named_args': None, 'session_role': None, 'path_to_script': None, 'encoding': None, 'search_path': None}}, 'failed': False, 'ansible_loop_var': 'query'}], 'skipped': False, 'changed': True, 'msg': 'All items completed'}['results'] | flatten | map(attribute='query_result') | flatten | map(attribute='<bound method Templar._query_lookup of <ansible.template.Templar object at 0x7fdf15343cb0>>')"

[dulhaver (Gunnar)]

@binbashroot

[…] You have a dict inside a list so you have to drill down a bit more to get the info you want

what are the skills needed to understand the mechanics behind this?
To me it smells jq but it might be as well python

[binbashroot]

Ansible is written in python. Jinja is a template engine for Python.

[binbashroot]

For your “even-flatter” you’re using an Ansible variable of map=(attribute=‘{{query}}’) which would be wrong. Which field are you looking for specifically? Are you looking to make it a flat string or a list item?

[dulhaver (Gunnar)]

I provide SHOW log_hostname into the {{ query }} variable with my execution command

ansible-playbook pg_get_setting.yml -e '{ "pg_setting" : "log_hostname" }' -l [some_fqdn]

The (return) value should be for log_hostname should either be 'on' or 'off'.

we see this into the complete output as "log_hostname": "on"
inside the "query_result": item (not sure whether that is the right term)

so what I want to isolate is the value 'on' in order to use it i.e. inside a condition like when: log_hostname = on

[binbashroot]

Your syntax is wrong. Your 'results and flatten…etc should all be encased in the {{}}. I think this is what you’re looking for but someone else may have a better way of drilling down to your desired output.

• debug:
  msg: “{{ test_results[‘results’] | flatten | map(attribute=‘query_result’) | flatten | first | dict2items |map(attribute=‘value’) | first }}”

[dulhaver (Gunnar)]

@binbashroot

debug:
  msg: "{{ test_result['results'] | flatten | map(attribute='query_result') | flatten | first | dict2items |map(attribute='value') | first }}"

after fixing the format of the quotes this works. thx.

now back to my other question from a litte above:

"{{ test_result['results'] | flatten | map(attribute='query_result') | flatten | first | dict2items |map(attribute='value') | first }}"

is which kind of syntax and/or logic? python, json, jinja ?