Issue with isolating returned values of a `postgresql_query` task

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

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):

1 Like

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
2 Likes

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>>')"

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

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

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?

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

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 }}”

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 ?