title: How to filter and retrieve data from MySQL query result with ansible modules
body: <p>So, Im trying to filter two dataframes into a same structure as a type host:user, in order to make an host:user comparison. The first one is from my ansible server and the second one is from MySQL server. But I got any issues to filter json data from MySQL query result with <a href="https://docs.ansible.com/ansible/latest/user_guide/playbooks_filters.html" rel="nofollow noreferrer">Ansible modules</a>.</p>
  <p>Firstly, I tried to retrieve MySQL informations with concat as a JSON:</p>
  <pre><code>## Compte MySQL
  - name: &quot;Get all MySQL accounts&quot;
    community.mysql.mysql_query:
      login_db: mysql
      query: 
      - SELECT user,JSON_ARRAYAGG(concat(host)) as host from user group by user
    register: sql
    tags: [never,test]
  
  - name: Show all MySQL accounts
    debug:
      var: sql
    tags: [never,test] 
  </code></pre>
  <p>Here is the output:</p>
  <pre><code>ok: [staging1-sql0] =&gt; {
      &quot;sql&quot;: {
          &quot;changed&quot;: false,
          &quot;executed_queries&quot;: [
              &quot;SELECT user,JSON_ARRAYAGG(concat(host)) as host from user group by user&quot;
          ],
          &quot;failed&quot;: false,
          &quot;query_result&quot;: [
              [
                  {
                      &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                      &quot;user&quot;: &quot;username1&quot;
                  },
                  {
                      &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                      &quot;user&quot;: &quot;username2&quot;
                  },
                  {
                      &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                      &quot;user&quot;: &quot;username3&quot;
                  },
                  {
                      &quot;host&quot;: &quot;[\&quot;xx.xxx.xx.xxx\&quot;, \&quot;xx.x.x.xxx\&quot;]&quot;,
                      &quot;user&quot;: &quot;username4&quot;
                  },
                  {
                      &quot;host&quot;: &quot;[\&quot;xx.x.x.xxx\&quot;]&quot;,
                      &quot;user&quot;: &quot;username5&quot;
  </code></pre>
  <p>Secondly, I converted the output of MySQL into an item, and select the query_result.</p>
  <pre><code>- name: Set the MySQL result as an item.
    debug:
      var: item
    loop: &quot;{{ sql | dict2items(key_name='key') }}&quot;
    register: sql_items
    no_log: False
    tags: [never,test]
  
  - name: &quot;Set the dataframe in the same structure host:user&quot;
    set_fact:
      new_sql: &quot;{{ sql_items | community.general.json_query('results[3].{resultats: item.value[0]}') }}&quot;
    tags: [never,test]
  
  - name: Affichage de new_sql
    debug:
      var: new_sql
    tags: [never,test]
  
  </code></pre>
  <p>Here is the output:</p>
  <pre><code>ok: [staging1-sql0] =&gt; {
      &quot;new_sql&quot;: {
          &quot;resultats&quot;: [
              {
                  &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                  &quot;user&quot;: &quot;username1&quot;
              },
              {
                  &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                  &quot;user&quot;: &quot;username2&quot;
              },
              {
                  &quot;host&quot;: &quot;[\&quot;x.xxx.xx.xxx\&quot;]&quot;,
                  &quot;user&quot;: &quot;username3&quot;
              },
              {
                  &quot;host&quot;: &quot;[\&quot;xx.xxx.xx.xxx\&quot;, \&quot;xx.x.x.xxx\&quot;]&quot;,
                  &quot;user&quot;: &quot;username4&quot;
              },
              {
                  &quot;host&quot;: &quot;[\&quot;xx.x.x.xxx\&quot;]&quot;,
                  &quot;user&quot;: &quot;username5&quot;
              },
  </code></pre>
  <p>Finally, after fitered, I had some problems trying to display data from my <em><strong>resultats</strong></em>  dataframe with ansible modules. For example, in order to display only hosts of each user, I tried this :</p>
  <pre><code>- name: Display all host names
    debug:
      var: item
    loop: &quot;{{ new_sql | community.general.json_query('resultats[*].host') }}&quot;
    register: query
    tags: [never,test]
  </code></pre>
  <p>But it doesnt work, here is the error result :</p>
  <pre><code>fatal: [staging1-sql0]: FAILED! =&gt; {&quot;msg&quot;: &quot;Invalid data passed to 'loop', it requires a list, got this instead: . Hint: If you passed a list/dict of just one element, try adding wantlist=True to your lookup invocation or use q/query instead of lookup.&quot;}
  </code></pre>
  <p>Does anyone of have any idea of how to display those datas with another ansible modules ?</p>
  <p><strong>UPDATE</strong> :</p>
  <p>I finally tried this with <em>map filter</em> :</p>
  <pre><code>    - name: New filter with mapping
    debug:
      msg: &quot;{{ new_sql | map(attribute='host') | list }}&quot;
    register: query
    tags: [never,test]
  </code></pre>
  <p>But, I got this output :</p>
  <pre><code>ok: [staging1-sql0] =&gt; {
      &quot;msg&quot;: &quot;[AnsibleUndefined, AnsibleUndefined, AnsibleUndefined,  AnsibleUndefined, AnsibleUndefined, AnsibleUndefined, AnsibleUndefined....
  </code></pre>
  <p>Anyone can help me fix it?</p>