Filter by partial string match in JSON using JMESPath

Question

Below is a sample JSON:

{
  "School": [
  {"@id": "ABC_1",
  "SchoolType": {"@tc": "10023204",
   "#text": "BLUE FOX"}},
  {"@id": "ABC_2",
  "SchoolType": {"@tc": "143", "#text": "AN EAGLE"}},
  {"@id": "ABC_3",
  "SchoolType": {"@tc": "21474836", "#text": "OTHER REASONS"},
  "SchoolStatus": {"@tc": "21474836", "#text": "FINE"},
  "Teacher": [
    {"@id": "XYZ_1",
    "TeacherType": {"@tc": "5", "#text": "GENDER"},
    "Gender": "FEMALE",
    "Extension": {"@VC": "23",
     "Extension_Teacher": {"DateDuration": {"@tc": "10023111",
       "#text": "0-6 MONTHS"}}}},
    {"@id": "XYZ_2",
    "TeacherType": {"@tc": "23", "#text": "EDUCATED"},
    "Extension": {"@VC": "23",
     "Extension_Teacher": {"DateDuration": {"@tc": "10023111",
       "#text": "CURRENT"}}}}]},
  {"@id": "ABC_4",
  "SchoolType": {"@tc": "21474836", "#text": "OTHER DAYS"},
  "SchoolStatus": {"@tc": "1", "#text": "DOING OKAY"},
  "Extension": {"Extension_School": {"AdditionalDetails": "CHRISTMAS DAY"}}}]
}

I want to extract the Teacher information (TeacherType, Gender etc.) for each Teacher @id where the associated SchoolType.\"#text\" contains "OTHER" for any School @id for all School.

I tried the below query but it doesn't work:

School[?SchoolType.\"#text\".contains(@, "OTHER")].Teacher[*].TeacherType.\"#text\"[]]

Plebs

Is jmespath a requirement for this? – Macgregor

@Macgregor - Unfortunately yes!!! – Plebs

When asking those kind of question, the best is also to provide your expected JSON output. – Sweepings

Answer 1

You have to warp the contains function around the array of your condition this way:

[?contains(SchoolType."#text", 'OTHER')]

So a way to get the full Teacher object would be:

School[?contains(SchoolType."#text", 'OTHER')].Teacher

Or, getting rid of the array of array with a flatten operator:

School[?contains(SchoolType."#text", 'OTHER')].Teacher | []

This would give:

[
  {
    "@id": "XYZ_1",
    "TeacherType": {
      "@tc": "5",
      "#text": "GENDER"
    },
    "Gender": "FEMALE",
    "Extension": {
      "@VC": "23",
      "Extension_Teacher": {
        "DateDuration": {
          "@tc": "10023111",
          "#text": "0-6 MONTHS"
        }
      }
    }
  },
  {
    "@id": "XYZ_2",
    "TeacherType": {
      "@tc": "23",
      "#text": "EDUCATED"
    },
    "Extension": {
      "@VC": "23",
      "Extension_Teacher": {
        "DateDuration": {
          "@tc": "10023111",
          "#text": "CURRENT"
        }
      }
    }
  }
]

Haswell