Filter JSON field from file

scapy
May 23, 2023
223 views
0 votes
2 Answers

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 }}"

Answers

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

- anonymous_user
- May 23, 2023 at 8:55 pm
- 0 votes
0
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.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.