json - query with conditions

Question

I have following json:

{
  "id": "1",
  "name": "profile1",
  "userId": "0",
  "groupId": "3",
  "attributes": [
    {
      "id": "104",
      "name": "Enable",
      "value": "1"
    },
    {
      "id": "105",
      "name": "TargetNode",
      "value": "system1"
    },
    {
      "id": "106",
      "name": "Timeout",
      "value": "30"
    }
  ],
  "xconns": [
    {
      "id": "1",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    },
    {
      "id": "1",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    },
    {
      "id": "1",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    }
  ]
}
{
  "id": "2",
  "name": "profile2",
  "userId": "7",
  "groupId": "0",
  "attributes": [
    {
      "id": "104",
      "name": "Enable",
      "value": "1"
    },
    {
      "id": "105",
      "name": "TargetNode",
      "value": "system2"
    },
    {
      "id": "106",
      "name": "Timeout",
      "value": "30"
    }
  ],
  "xconns": [
    {
      "id": "2",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    },
    {
      "id": "2",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    },
    {
      "id": "2",
      "locked": false,
      "attributeList": [
        {
          "id": "101",
          "name": "Lgrp",
          "value": "1"
        },
        {
          "id": "102",
          "name": "IsRem",
          "value": "1"
        },
        {
          "id": "103",
          "name": "Media",
          "value": "1"
        }
      ]
    }
  ]
}

I can filter following:

$ jq -r 'select([.attributes[] | .name == "TargetNode" ] | any ) | [{userId, groupId, id, name}] | .[] | if (.userId == "0") then del(.userId) else . end | if (.groupId == "0") then del(.groupId) else . end | to_entries | map("\(.key | ascii_upcase):\(.value)") | @tsv' file.json
GROUPID:3       ID:1    NAME:profile1
USERID:7        ID:2    NAME:profile2

I need to add also value of TargetNode:

GROUPID:3       ID:1    NAME:profile1    TARGETNODE:system1
USERID:7        ID:2    NAME:profile2    TARGETNODE:system2

is there a way to include it in [{userId, groupId, id, name, TargetNode}] to get the value of TargetNode and not null?

GROUPID:3 ID:1 NAME:profile1 TARGETNODE:null

USERID:7 ID:2 NAME:profile2 TARGETNODE:null

Update: the solution provided by RomanPerekhrest is nearly ok, but there is one issue because the json file in real is much bigger, there are more attrobutes in "main secttion", for example:

{
  "id": "1",
  "name": "profile1",
  "userId": "0",
  "groupId": "3",
  "attrib101": "A",
  "attrib102": "B",
  "attributes": [
...
...

it is cousing that RomanPerekhrest's jq filter returns too much... how to rid of them too?

ID:1    NAME:profile1   GROUPID:3   ATTRIB101:A     ATTRIB102:B TARGETNODE:system1
ID:2    NAME:profile2   USERID:7    ATTRIB101:C     ATTRIB102:D TARGETNODE:system2

RomanPerekhrest · Accepted Answer · 2018-02-07T15:37:55.023

3

jq solution:

jq -r '.attributes |= map(select(.name == "TargetNode")) 
       | if (.attributes | length != 0) then .targetNode = .attributes[0].value else . end 
       | if (.userId == "0") then del(.userId) else . end 
       | if (.groupId == "0") then del(.groupId) else . end 
       | del(.attributes, .xconns) | to_entries 
       | map("\(.key | ascii_upcase):\(.value)") | @tsv' file.json

If an object with "name": "TargetNode" pair not exists - TARGETNODE won't be added into resulting structure

The output:

ID:1    NAME:profile1   GROUPID:3   TARGETNODE:system1
ID:2    NAME:profile2   USERID:7    TARGETNODE:system2

edited Feb 07 '18 at 15:37

answered Feb 07 '18 at 14:54

RomanPerekhrest

88,541
4
65
105

thank you, it works perfectly, please ignore my update as you already answered it to: | del(.attributes, .xconns, .startOrder, .attrib101, .attrib102) | to_entries – Chris Feb 09 '18 at 09:52
How to add order change to this filter to get on output for example: ID:1 TARGETNODE:system1 GROUPID:3 NAME:profile1 – Chris Feb 09 '18 at 10:23
@Chris, we can't move dynamic/optional fields: `TARGETNODE` is optional + `GROUPID` and `USERID` are mutually exclusive – RomanPerekhrest Feb 09 '18 at 12:06

json - query with conditions

1 Answers1