Help with json_query filtering

Get Help
,
1

I’m pulling some information from Active Directory and trying to use json_query to filter it, without success. I’ve written up equivalent code and mock data that reproduces my issue.

using data from here with the following playbook, I’d expect one instance of the with_items loop would have 1/2 the results (Group Name 1) and then the second would have the other 1/2 (Group Name 2)

---
- name: "JSON Query MVP Test"
  hosts: localhost
 
  tasks:
    - name: "Setup Loop Var"
      ansible.builtin.set_fact:
        looper: [
          { "ou": "OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
            "group": "AD Group Name 1",
          },
          { "ou": "OU=AD Group Name 2,OU=Location,DC=domain,DC=org",
            "group": "AD Group Name 2"
          }
        ]
 
    - name: "Read in JSON"
      include_vars:
        file: data.json
        name: data
 
    - name: "Debug JSON"
      ansible.builtin.debug:
        msg: "{{ data }}"
 
    - name: "Test Query"
      ansible.builtin.debug:
        msg: "{{ data | community.general.json_query('results[?item.ou=='  'item.ou'  '].objects[].DistinguishedName')}}"
      with_items: "{{ looper }}"

But instead I am getting this

issue_repro
issue_repro1105×542 100 KB

This matches the issue I am having with my real data (the json_query filter isn’t actually filtering my data).

Can anyone help me spot either my logical or syntactical error here?

UPDATE
when I run this query using jpterm (manually subbing in the string I’m expecting) it produces the results I’m looking for, but running in a playbook does not. Either that means my variable substitution isn’t working how I think it is (I’ll verify that later) or the query isn’t working the same

2

Could you clarify exactly what data you want to extract, sorry I haven’t understood this.

3

The data returned is what I want, but unfiltered. I’m trying to return the objects[] only for the item.ou that matches the item.ou from my looper variable (in the example code).

So each iteration of the loop should only return the objects that match, instead of all of them.

(I also have an update to the post to clarify something)

4

Sorry I still don’t get it, perhaps because I’m tired, could you post the required result?

5

I’m not a huge fan of json_query (I avoid it like the plague). If I’ve read your thread correctly, this may be along the lines of what you’re trying to do.

---
- name: "JSON Query MVP Test"
  hosts: localhost
  connection: local
  gather_facts: false
  vars:
    looper:
      - ou: "OU=AD Group Name 1,OU=Location,DC=domain,DC=org"
        group: "AD Group Name 1"
      - ou: "OU=AD Group Name 2,OU=Location,DC=domain,DC=org"
        group: "AD Group Name 2"

  tasks:

    - name: "Read in JSON"
      include_vars:
        file: results.json
        name: data

    - name: "Debug JSON"
      ansible.builtin.debug:
        var: data

    - name: "Test Query"
      ansible.builtin.debug:
        var: data['results']|
             selectattr('item.ou','match',mylooper.ou) |
             map(attribute='objects') | list
      loop: "{{ looper }}"
      loop_control:
        loop_var: mylooper
1 Like
6

This is what I’d expect the results to be. The objects from the entry where item.ou matches the item.ou from the loop variable.

TASK [Test Query] **************************************************************************************************************
ok: [localhost] => (item={'ou': 'OU=AD Group Name 1,OU=Location,DC=domain,DC=org', 'group': 'AD Group Name 1'}) => {
    "msg": [
        "CN=01,OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
        "CN=02,OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
        "CN=03,OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
        "CN=04,OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
        "CN=05,OU=AD Group Name 1,OU=Location,DC=domain,DC=org",
    ]
}
ok: [localhost] => (item={'ou': 'OU=AD Group Name 2,OU=Location,DC=domain,DC=org', 'group': 'AD Group Name 2'}) => {
    "msg": [
        "CN=06,OU=AD Group Name 2,OU=Location,DC=domain,DC=org",
        "CN=07,OU=AD Group Name 2,OU=Location,DC=domain,DC=org",
        "CN=08,OU=AD Group Name 2,OU=Location,DC=domain,DC=org",
        "CN=09,OU=AD Group Name 2,OU=Location,DC=domain,DC=org",
        "CN=10,OU=AD Group Name 2,OU=Location,DC=domain,DC=org"
    ]
}

PLAY RECAP *********************************************************************************************************************
localhost                  : ok=5    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
7

This is closer, but its returning the whole object, and what I need is just the DistinguishedName bit.

8

Try this:

    - name: "Test Query"
      ansible.builtin.debug:
        var: data['results']|
             selectattr('item.ou','match',mylooper.ou) |
             map(attribute='objects') | flatten |
             map(attribute='DistinguishedName') |
             list
      loop: "{{ looper }}"
      loop_control:
        loop_var: mylooper
1 Like
9

This appears to work on my test data. I’m going to try it out on live data to see if it also works. Thank you.

1 Like
10

To update this, in case anyone else needs something similar in the future:

I needed to feed this list into microsoft.ad.group members.set.

I initially tried copying the noted code exactly, after my debugs showed that it was producing the list I was expecting, but found that despite the ending | list filter, it wasn’t being read as I expected. So, I had to wrap the whole block (from above, thanks @binbashroot) in quotes and mustaches, "{{ }}", like this.

- name: "Set Group Membership"
  microsoft.ad.group:
    name: "{{ item.group }}"
    path: "OU=Group Location,DC=domain,DC=org"
    members:
        lookup_failure_action: warn 
        set: "{{ data['results']|
             selectattr('item.ou','match',item.ou) |
             map(attribute='objects') | flatten |
             map(attribute='DistinguishedName') |
             list }}"
    loop: "{{ looper }}"

After that, its been working as expected.

Thanks for all the help

1 Like
11

When using the var attribute with the debug module, you don’t need the curly braces {{}}. Otherwise you’ll always need to wrap the var with “{{}}” .

3 Likes