jq - select entry if any of several keys is NOT present

user_6319555 0
Viewed 219

I have the JSON file as shown below. What I want - using jq select and display as a new array all the individual Entries where db[].name is missing any of "Business Overview", "Load Test" or "Performance Monitoring". In other words, any Entry where all three of them are present in db.[] should be dropped and left only these where any of them is missing.

So, the result of applying such a filter would be displaying the very last Entry with "Tenant": "UT-S1835", where db[].name is missing "Load Test".

I can't seem to make it work. You probably know better, would be glad to know how.

Thanks

[
  {
    "Entry": {
      "Tenant": "CA-T4932",
      "Stage": "prd"
    },
    "db": [
      {
        "id": "xxxxx0001-0a0a-0b0b-0c0c-765434",
        "name": "Business Overview",
      },
      {
        "id": "bbbb0001-0a0a-0b0b-0c0c-6b68706",
        "name": "Performance Monitoring"
      },
      {
        "id": "bbbb0001-0a0a-0b0b-0c0c-f616465",
        "name": "Load Test"
      }
      ]
  },
  {
    "Entry": {
      "Tenant": "NV-R2133",
      "Stage": "dev"
    },
    "db": [
      {
        "id": "ccccc0006-0a0a-0707-0c0c-765434",
        "name": "Business Overview",
      },
      {
        "id": "rrrrr0007-0f0f-0803-0c0c-5e18331",
        "name": "Performance Monitoring"
      },
      {
        "id": "bbbb0001-0a0a-0b0b-0d0d-d439575",
        "name": "Load Test"
      },
      {
        "id": "zzzz0004-0e0e-0ddd-0a0e-ee83005",
        "name": "Home"
      }
      ]
   },
     {
    "Entry": {
      "Tenant": "UT-S1835",
      "Stage": "stg"
     },
    "db": [
      {
        "id": "bcbcb0032-0ccc-4040-1d1d-326923",
        "name": "Business Overview",
      },
      {
        "id": "rrrrr0007-a3b4-1230-0c0c-0e12044",
        "name": "Performance Monitoring"
      },
      {
        "id": "abab0043-0c0f-0dca-0a0e-ee31705",
        "name": "Home"
      }
      ]
   }
]
jq filter select
4 Answers

You can use this filter to achieve the result

[ "Business Overview", "Load Test", "Performance Monitoring" ] as $bl | 
map( select( [ [.db[].name] | unique[] | IN( $bl[] ) ] |
  map( select( . == true ) ) |
    length == ($bl|length) | not ) 
  )

jqplay - Online demo

The filter works by selecting number of times, unique instances of .name field of the object matches one of the entries in the blacklist array. If the count matches the number of items in blacklist, we exclude that from being printed.

You can also customize the blacklist from the command line by including the array in the --argjson field as below and exclude the first line in the above pipeline.

--argjson bl '["Business Overview", "Load Test", "Performance Monitoring"]'
user_52910150

Here are two solutions which avoid contains (which has complex semantics, as noted elsewhere on this page) and index/1 (which currently is not implemented as efficiently as one would hope). In both cases, it is assumed that $required has somehow been set to the list of required values, e.g. by:

["Business Overview", "Load Test", "Performance Monitoring"] as $required

Simple

map(select( $required - [.db[].name] != []))

Efficient

map(select( .db as $db
            | all( $required[]; . as $req | any($db[].name; . == $req) )
            | not))
user_9973580

Here is an approach that uses index and any/2 (the two argument form of any) along with map and select in a manner similar to the other answers given here.

  [ "Performance Monitoring", "Business Overview", "Load Test" ] as $required
| map( select( [.db[].name] | any(index($required[]); . == null) ) )

Try it online!

user_83795970

The contains filter seems suited for your task.

The filter contains(b) will produce true if b is completely contained within the input. … An array B is contained in an array A if all elements in B are contained in any element in A. (jq 1.6 manual)

My solution puts the values of "name" in each object into an array. It uses contains to see if the array of the values you listed are all in that array.

jq 'map( select( [.db[].name]
   | contains( ["Business Overview", "Performance Monitoring", "Load Test"] )
   | not ) )'

Note

Inian points out that contains can be tricky. For example, this returns "true."

jq 'contains(["A"])' <<< '["A B"]'

That could lead to the wrong result if the value of "name" is "Business Overview B" and so on.

user_78391890
Related