Combine attribute value using json_query in ansible

Question

I want to combine two attribute into single string separated by delimiter using the json_query in ansible
Sample data

{
  "locations": [
    {"name": "Seattle", "state": "WA"},
    {"name": "New York", "state": "NY"},
    {"name": "Bellevue", "state": "WA"},
    {"name": "Olympia", "state": "WA"}
  ]
}

As shown in above data set i'm trying to filter the state "WA" and execpted output is:

[
    "Seattle-WA",
    "Bellevue-WA",
    "Olympia-WA"
]

What i have tried as of now:

    - debug:
        msg: "{{ chart_list.HELM_CHARTS | json_query(\"[?state == 'WA'].{name:name,state:state}\") }}"
Output:
[
  {
    "name": "Seattle",
    "state": "WA"
  },
  {
    "name": "Bellevue",
    "state": "WA"
  },
  {
    "name": "Olympia",
    "state": "WA"
  }
]

Updated : I was able to get the expected result by trial and error method and these are my findings:

[?state == 'WA'].[join('-',[name,state])][]
Output:
[
  "Seattle-WA",
  "Bellevue-WA",
  "Olympia-WA"
]

Also if the input which you give is in unicode format, i suggest you to add to_json | from_json expressions as mentioned below:

        selected_cities: "{{ test.locations| to_json | from_json | json_query(\"[?state == 'WA'].[join('-',[name,state])][]\") }}"

Using above expression will eliminate unicode error whil using the values or in any condition. Check JMESPath site for more detail on the json_query, it was really helpful in resolving the issue.

I see innumerable questions about how to bend `json_query` to the questioner's will, but I don't understand the obsession with it. Jinja2 has `for` loops to go with its handy `selectattr` filter, and they work great. — mdaniel, Mar 04 '21 at 05:45
Hi @mdaniel , the reason i wanted it in json_query was to quickly access the required data from huge dataset based on condition. I agree that loops would work but i'm bit familiar with this plugin :-) — Snkini, Mar 04 '21 at 10:27

Vladimir Botka · Accepted Answer · 2021-03-06T05:41:13.477

For example

    - debug:
        msg: "{{ locations|
                 json_query('[?state == `WA`].[name,state]')|
                 map('join', '-')|list }}"

gives

  msg:
  - Seattle-WA
  - Bellevue-WA
  - Olympia-WA

The same result gives the task below using Jinja2 filters only

    - debug:
        msg: "{{ _names|zip(_states)|map('join', '-')|list }}"
      vars:
        _locations: "{{ locations|selectattr('state', 'eq', 'WA')|list }}"
        _names: "{{ _locations|map(attribute='name')|list }}"
        _states: "{{ _locations|map(attribute='state')|list }}"

json_query issue (fixed in 2.10 and later)

There is JMESPath join. Unfortunately

    - debug:
        msg: "{{ locations|
                 json_query('[].join(`-`, [name,state])') }}"

fails

msg: |- JMESPathError in json_query filter plugin: In function join(), invalid type for value: Seattle, expected one of: ['array-string'], received: "AnsibleUnicode"

to_json|from_json workaround

Quoting from json_query: Add examples for starts_with and contains #72821

data structure returned from register variables needs to be parsed using to_json | from_json in order to get a correct result. Fixes: ansible-collections/community.general#320

    - debug:
        msg: "{{ locations|to_json|from_json|
                 json_query('[].join(`-`, [name,state])') }}"

gives

  msg:
  - Seattle-WA
  - New York-NY
  - Bellevue-WA
  - Olympia-WA

*(There is JMESPath join. I'd also like to learn how to use it.)* > Wish granted. — β.εηοιτ.βε, Mar 04 '21 at 19:20
Unfortunately, it doesn't work for me. (ansible 2.9.6, python3-jmespath 0.9.4-2). Would it be possible to post working versions? — Vladimir Botka, Mar 04 '21 at 19:56
Oh? Will try getting back to 2.9 from my latest and see what's behind this — β.εηοιτ.βε, Mar 04 '21 at 20:21
Looks like this is a case of [`to_json | from_json`](https://github.com/ansible/ansible/issues/27299#issuecomment-331068246) issue, that got fixed on 2.10 but affect 2.9 — β.εηοιτ.βε, Mar 04 '21 at 20:55

β.εηοιτ.βε · Answer 2 · 2021-03-04T21:22:57.540

Just for the sake of a pure JMESPath way of doing it, as your trial and error solution still have an unneeded extra layer of complexity.

When you are doing

[?state == 'WA'].[join('-', [name, state])][]

You are creating an array [join('-', [name, state])] then flattening it [] for no reason.

You can just go to the solution with a shorter approach:

[?state == `WA`].join(`-`, [name, state])

Also mind that you can overcome the quotes in quotes (simple or double) complication for JMESPath queries using:

YAML multilines string: How do I break a string in YAML over multiple lines?
Backticks in your JMESPath query, as pointed in the documentation:

In the example above, quoting literals using backticks avoids escaping quotes and maintains readability.

^{Source: https://docs.ansible.com/ansible/latest/user_guide/playbooks_filters.html#selecting-json-data-json-queries}

So you end up with (see note below if you are on an Ansible version < 2.10):

- debug:
    msg: >-
      {{ test.locations 
           | json_query('[?state == `WA`].join(`-`, [name, state])') }}

Please note: as raised by @Vladimir Botka on the versions prior to 2.10, you will be affected by this issue: https://github.com/ansible/ansible/issues/27299#issuecomment-331068246, forcing you to add a | to_json | from_json filter on the list.

Given the playbook:

- hosts: all
  gather_facts: yes

  tasks:
    - debug:
        msg: >-
          {{ test.locations 
              | json_query('[?state == `WA`].join(`-`, [name, state])') 
          }}
      vars:
        test:
          locations:
            - name: Seattle
              state: WA
            - name: New York
              state: NY
            - name: Bellevue
              state: WA
            - name: Olympia
              state: WA

This yields:

[
    "Seattle-WA",
    "Bellevue-WA",
    "Olympia-WA"
]

Combine attribute value using json_query in ansible

2 Answers2

Linked