# Help with json\_query filtering **URL:** \ **Category:** Get Help\ **Tags:** json, jmespath\ **Created:** [March 4, 2025, 8:58pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789 "2025-03-04T20:58:43Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 4, 2025, 8:58pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/1 "2025-03-04T20:58:43Z")
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](https://pastebin.com/Ay80JSSV) 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) ```auto --- - 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](https://us1.discourse-cdn.com/ansible/original/2X/3/3ba3901af896acec7464728f29caa1bafbe7fd8b.png) 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 ---
**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:** [March 4, 2025, 9:50pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/2 "2025-03-04T21:50:16Z")
Could you clarify exactly what data you want to extract, sorry I haven’t understood this. ---
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 4, 2025, 10:09pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/3 "2025-03-04T22:09:53Z")
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) ---
**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:** [March 4, 2025, 10:20pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/4 "2025-03-04T22:20:57Z")
Sorry I still don’t get it, perhaps because I’m tired, could you post the required result? ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [March 5, 2025, 1:21am UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/5 "2025-03-05T01:21:26Z")
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. ```auto --- - 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 ``` ---
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 5, 2025, 12:15pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/6 "2025-03-05T12:15:14Z")
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. ```auto 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 ``` ---
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 5, 2025, 12:21pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/7 "2025-03-05T12:21:13Z")
This is _closer_, but its returning the whole object, and what I need is just the `DistinguishedName` bit. ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [March 5, 2025, 12:53pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/8 "2025-03-05T12:53:17Z")
Try this: ```auto - 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 ``` ---
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 5, 2025, 1:28pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/9 "2025-03-05T13:28:24Z")
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. ---
**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\ **Post date:** [March 5, 2025, 3:19pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/10 "2025-03-05T15:19:56Z")
To update this, in case anyone else needs something similar in the future: I needed to feed this list into [microsoft.ad.group](https://docs.ansible.com/ansible/latest/collections/microsoft/ad/group_module.html#ansible-collections-microsoft-ad-group-module) 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. ```auto - 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 ---
**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\ **Post date:** [March 5, 2025, 3:50pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/11 "2025-03-05T15:50:30Z")
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 “{{}}” . ---
**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\ **Post date:** [April 4, 2025, 3:50pm UTC](https://forum.ansible.com/t/help-with-json-query-filtering/40789/12 "2025-04-04T15:50:44Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.