# Issue with isolating returned values of a \`postgresql\_query\` task **URL:** \ **Category:** Get Help\ **Created:** [July 5, 2024, 8:38am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071 "2024-07-05T08:38:18Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [July 5, 2024, 8:38am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/1 "2024-07-05T08:38:18Z")
I want to get the result of a query (the value of `log_hostname` paramter) returned back from a playbook like the below ```auto --- - 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 ```auto 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 ```auto msg: "{{ test_result }}" ``` with ```auto 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 ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [July 5, 2024, 11:30am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/2 "2024-07-05T11:30:36Z")
There are many ways this can be done, I’d probably use the [community.general.json\_query filter](https://docs.ansible.com/ansible/latest/collections/community/general/json_query_filter.html), this is fairly easy to test and debug since [jpterm](https://github.com/jmespath/jmespath.terminal) can be used (best use the [community fork](https://github.com/jmespath-community/jmespath.terminal) as it is being maintained): ![jpterm](https://us1.discourse-cdn.com/ansible/original/2X/2/281359fcff6abd3649e89ae813b745331ae2172a.png) ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [July 5, 2024, 1:27pm UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/3 "2024-07-05T13:27:56Z")
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: ```auto # 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 ``` ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [July 9, 2024, 8:57am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/4 "2024-07-09T08:57:53Z")
we are moving into the right direction. plaid a bit with what you suggest but am not getting more flat then the first suggetion ```auto - 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 ```auto 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='>')" ``` ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [July 9, 2024, 8:59am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/5 "2024-07-09T08:59:20Z")
> [@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` ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [July 9, 2024, 12:04pm UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/6 "2024-07-09T12:04:49Z")
Ansible is written in python. Jinja is a template engine for Python. ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [July 9, 2024, 12:11pm UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/7 "2024-07-09T12:11:18Z")
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? ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [July 9, 2024, 1:53pm UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/8 "2024-07-09T13:53:49Z")
I provide `SHOW log_hostname` into the `{{ query }}` variable with my execution command ```auto 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` ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [July 9, 2024, 2:26pm UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/9 "2024-07-09T14:26:53Z")
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 }}” ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [July 12, 2024, 7:32am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/10 "2024-07-12T07:32:14Z")
> [@binbashroot](#): > > ```auto > 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: ```auto "{{ 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 ? ---
**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\ **Post date:** [August 11, 2024, 7:32am UTC](https://forum.ansible.com/t/issue-with-isolating-returned-values-of-a-postgresql-query-task/7071/11 "2024-08-11T07:32:19Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.