Filter JSON field from file

Question

I have a JSON file with some data on it, like the below one:

{
    "84d0da32-c945-9844-86cc-4b4bd6100dc5": {
        "UUID": "84d0da32-c945-9844-86cc-4b4bd6100dc5",
        "GroupName": "TEST1",
        "EntryTitle": "host1",
        "Username": "sys",
        "NewPassword": "@PVmkiajauauajjhfz-5/NN"
    },
    "test": {
        "UUID": "5c3c162f-0a80-f949-85a0-afcf9aedb6c8",
        "GroupName": "TEST2",
        "EntryTitle": "host2",
        "Username": "sys",
        "NewPassword": "H7-uPz2mkaaua@ki7q?NSs?"
    }
}

I am trying to filter only the field GroupName or EntryTitle, with json_query but it always give null, if I try to write all GroupName field, it returns both.
I only want to pass for example if GroupName is TEST2 return the EntryTitle field.

Example of my code until now:

- name: load json data
  shell: cat entries.json
  register: result

- name: save json do var
  set_fact:
    jsondata: "{{ result.stdout | from_json }}"
- name: server name
  set_fact:
    servername: "{{ jsondata | json_query(jq) }}"
  vars:
    jq: "*.GroupName"
- name: Print
  debug:
    msg: "{{ item }}"
  with_items:
    -  "{{ servername }}"

A good question would have included your trial that _"always give `null`"_ as you are claiming it. — β.εηοιτ.βε, May 23 '23 at 18:20

score 0 · Answer 1 · answered May 23 '23 at 11:54

I can't reproduce your problem. Your code works as expected. Given the file

shell> cat /tmp/entries.json 
{
    "84d0da32-c945-9844-86cc-4b4bd6100dc5": {
        "UUID": "84d0da32-c945-9844-86cc-4b4bd6100dc5",
        "GroupName": "TEST1",
        "EntryTitle": "host1",
        "Username": "sys",
        "NewPassword": "@PVmkiajauauajjhfz-5/NN"
    },
    "test": {
        "UUID": "5c3c162f-0a80-f949-85a0-afcf9aedb6c8",
        "GroupName": "TEST2",
        "EntryTitle": "host2",
        "Username": "sys",
        "NewPassword": "H7-uPz2mkaaua@ki7q?NSs?"
    }
}

read the file

    - command: cat /tmp/entries.json
      register: result

and convert the result from JSON. Declare the variable

  jsondata: "{{ result.stdout|from_json }}"

gives

  jsondata:
    84d0da32-c945-9844-86cc-4b4bd6100dc5:
      EntryTitle: host1
      GroupName: TEST1
      NewPassword: '@PVmkiajauauajjhfz-5/NN'
      UUID: 84d0da32-c945-9844-86cc-4b4bd6100dc5
      Username: sys
    test:
      EntryTitle: host2
      GroupName: TEST2
      NewPassword: H7-uPz2mkaaua@ki7q?NSs?
      UUID: 5c3c162f-0a80-f949-85a0-afcf9aedb6c8
      Username: sys

The filter json_query

  jq: "*.GroupName"
  servername: "{{ jsondata|json_query(jq) }}"

gives the list of the attribute GroupName values

  servername:
  - TEST1
  - TEST2

^{Example of a complete playbook for testing
shell> cat pb.yml
- hosts: all

vars:

jsondata: "{{ result.stdout|from_json }}"
jq: "*.GroupName"
servername: "{{ jsondata|json_query(jq) }}"

tasks:

- command: cat /tmp/entries.json
register: result
- debug:
var: jsondata
- debug:
var: servername

gives
shell> ansible-playbook -i localhost, pb.yml

PLAY [all] ************************************************************************************

TASK [command] ********************************************************************************
changed: [localhost]

TASK [debug] **********************************************************************************
ok: [localhost] =>
jsondata:
84d0da32-c945-9844-86cc-4b4bd6100dc5:
EntryTitle: host1
GroupName: TEST1
NewPassword: '@PVmkiajauauajjhfz-5/NN'
UUID: 84d0da32-c945-9844-86cc-4b4bd6100dc5
Username: sys
test:
EntryTitle: host2
GroupName: TEST2
NewPassword: H7-uPz2mkaaua@ki7q?NSs?
UUID: 5c3c162f-0a80-f949-85a0-afcf9aedb6c8
Username: sys

TASK [debug] **********************************************************************************
ok: [localhost] =>
servername:
- TEST1
- TEST2

PLAY RECAP ************************************************************************************
localhost: ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0}

β.εηοιτ.βε · Accepted Answer · 2023-05-23T19:00:22.737

I guess your trial at a query was something like

*[?GroupName == `TEST2`].EntryTitle

This does indeed returns an empty list ([]) as * is an object projection, and in order to chain a projection on top of another projection, in JMESPath, you have to reset the previous projection, with a pipe expression.

Here, the query [?GroupName == `TEST2`] is a filter projection, so you indeed have to follow the above mentioned rule by resetting the projection created by the object projection:

* | [?GroupName == `TEST2`].EntryTitle

So, you should update your server name task accordingly:

- name: server name
  set_fact:
    servername: "{{ jsondata | json_query(jq) }}"
  vars:
    jq: "* | [?GroupName == `TEST2`].EntryTitle"

Side notes:

another good idea would be to use the purposed modules of Ansible rather than a shell task. If the file is on the remote node, use the slurp module, if the file is local to the controller, use a file lookup
try to get the habit of using the loop syntax instead of the with_* one, already. There are plenty of examples to migrate them in the documentation and the syntax usually feels more aligned across all different loops of your tasks, as the logic is expressed in filters rather than in the variation of the with_* used

So, if the file is on the remote node(s):

- slurp:
    src: entries.json
  register: entries_json

- debug:
    var: >-
      entries_json.content
        | b64decode
        | from_json
        | json_query('* | [?GroupName == `TEST2`].EntryTitle | [0]')

Would yield

ok: [localhost] => 
  ? |-
    entries_json.content
      | b64decode
      | from_json
      | json_query('* | [?GroupName == `TEST2`].EntryTitle | [0]')
  : host2

And if the file is on the controller node, then you can cut it down to a single task:

- debug:
    var: >-
      lookup('ansible.builtin.file', 'entries.json')
        | from_json
        | json_query('* | [?GroupName == `TEST2`].EntryTitle | [0]')

Would yield

ok: [localhost] => 
  ? |-
    lookup('ansible.builtin.file', 'entries.json')
      | from_json
      | json_query('* | [?GroupName == `TEST2`].EntryTitle | [0]')
  : host2

Extra note: the | [0] is once again a pipe expression meant to reset the previous projection, as explained before, in order to get the element [0], so the first one, of the JSON array.

Thanks a lot, I was able to pick the field, the error was the projection you mention. I was trying to pick the value to build a sql command to be executed on every database to change password.s. To execute the sql command if the host is running is the same on json field if not skip and proceed to next one. — scapy, May 24 '23 at 12:25

Filter JSON field from file

2 Answers2