JSONPath expression for nested arrays

[kshorty]

Hello!

I've tested this several ways and cannot extract the desired results when I have nested arrays in my JSON.

Sample JSON:

{
  "stores": [
    {
      "store": {
        "storeInfo": {
          "storeId": "111",
          "storeName": "Store Name 1",
          "storeDisplayName": null,
          "storePortfolioId": "10059595",
          "storeClass": "BookStore",
        },
        "bookInfo": {
          "bookPrice": "8.62",
          "bookAsOfDate": "1575849600000",
        },
        "storeClasses": [
          {
            "filterStoreClass": {
              "levelOneName": "Level One",
              "levelTwoName": "Level Two"
            }
          }
        ],
        "relatedBooks": [
          {
            "relatedBook": {
              "bookInfo": {
                "bookId": "121",
                "bookName": "My Related Book 1",
                "bookSymbol": "A",
                "bookProductCode": "0010",
                "status": "Open"
              },
              "currencyInfo": null,
              "otherRelatedInfo": {
                "indicator": false,
                "otherId": null,
                "otherDeadlines": [
                ]
              }
            }, 
            "relatedBook": {
              "bookInfo": {
                "bookId": "122",
                "bookName": "My Related Book 2",
                "bookSymbol": "AA",
                "bookProductCode": "0011",
                "status": "Open"
              },
              "currencyInfo": null,
              "otherRelatedInfo": {
                "indicator": false,
                "otherId": null,
                "otherDeadlines": [
                ]
              }
            }
          }
        ]
      }
    },
    {
      "store": {
        "storeInfo": {
          "storeId": "222",
          "storeName": "Store Name 2",
          "storeDisplayName": null,
          "storePortfolioId": "11595",
          "storeClass": "BookStore",
        },
        "bookInfo": {
          "bookPrice": "8.62",
          "bookAsOfDate": "1575849600000",
        },
        "storeClasses": [
          {
            "filterStoreClass": {
              "levelOneName": "Level One",
              "levelTwoName": "Level Two"
            }
          }
        ],
        "relatedBooks": [
          {
            "relatedBook": {
              "bookInfo": {
                "bookId": "122",
                "bookName": "My Related Book 3",
                "bookSymbol": "AAA",
                "bookProductCode": "0030",
                "status": "Open"
              },
              "currencyInfo": null,
              "otherRelatedInfo": {
                "indicator": false,
                "otherId": null,
                "otherDeadlines": [
                ]
              }
            }, 
            "relatedBook": {
              "bookInfo": {
                "bookId": "124",
                "bookName": "My Related Book 4",
                "bookSymbol": "AA",
                "bookProductCode": "0014",
                "status": "Open"
              },
              "currencyInfo": null,
              "otherRelatedInfo": {
                "indicator": false,
                "otherId": null,
                "otherDeadlines": [
                ]
              }
            }

          }
        ]
      }
    }      
  ]
}

There are 2 stores and each store has books with related books. If I find a related book in the store ideally I would like to return the storeId of that store (or the complete store object where there is a match). For example bookId=='122' is in both stores.

here is a breakdown of expressions I tried:

--- Returns the store information on found store id
$..store[?(@.storeInfo.storeId=='111')]

--- Returns just the found related book
$..relatedBooks[?(@.relatedBook.bookInfo.bookId == '122')]

--- Returns just the found related book
$..relatedBooks[?(@.relatedBook.bookInfo.bookProductCode == '0011')]

--- Returns the related book id
$..*[?(@.relatedBook.bookInfo.bookId == '122')]

--- NOT WORKING Returns the store with related book found
$..[?(@.store.relatedBooks[?(@.relatedBook.bookInfo.bookProductCode == '0010')])]'

--- NOT WORKING Returns bookId 122 for the first store and 124 for the second store
$..*[?(@.relatedBooks[?(@.relatedBook.bookInfo.bookId == '122')])]

Any help would be appreciated.

[Alanscut]

In object relatedBooks, you have two same key name object relatedBook, the first one will be overriden by the last relatedBook.

as for the last 2 case didn't return the expected results, I suppose JsonPath don't support the nested filter condition so far.

[Alanscut]

• for $..[?(@.store.relatedBooks[?(@.relatedBook.bookInfo.bookProductCode == '0010')])]', you could use:

Filter feq = Filter.filter(Criteria.where("store.relatedBooks[*].relatedBook.bookInfo.bookProductCode").contains("0011"));
List<Object> eq = JsonPath.read(json, "$..stores[?]", feq);

or

String path = "$.stores[?(@..store.relatedBooks[*].relatedBook.bookInfo.bookProductCode CONTAINS '0011')]";
List<String> res = JsonPath.read(json, path);

• for $..*[?(@.relatedBooks[?(@.relatedBook.bookInfo.bookId == '122')])], you could use:

Filter feq = Filter.filter(Criteria.where("relatedBooks[*].relatedBook.bookInfo.bookId").contains("122"));
List<Object> eq = JsonPath.read(json, "$..[?]", feq);

or

String path = "$..*[?(@.relatedBooks[*].relatedBook.bookInfo.bookId CONTAINS '122')]";
List<String> res = JsonPath.read(json, path);