'How to query from nested Json array object where there are multiple sub arrays?

I am trying to retrieve from a subarray of JSON object where "menuId": "AOIEnquiryActivity" and "isEnabled": "yes". Here I have query in PostgreSQL.

[
  {
    "menuId": "",
    "menuText": "Service Request",
    "menuIcon": "vector_ic_agent_tools",
    "isEnabled": "Yes",
    "subMenu": [
      {
        "menuId": "",
        "menuText": "Customer Tools",
        "menuIcon": "vector_ic_new_account",
        "isEnabled": "Yes",
        "subMenu": [
          {
            "menuId": "AOIEnquiryActivity",
            "menuText": "AOI Update Request",
            "menuIcon": "vector_ic_new_account",
            "isEnabled": "yes",
            "subMenu": []
          },
          {
            "menuId": "SearchByCASAAccForLoanActivity",
            "menuText": "Loan Request",
            "menuIcon": "vector_ic_new_account",
            "isEnabled": "No",
            "subMenu": []
          }
        ]
      }
    ]
  }
]
arraysjsonpostgresql


Solution 1:[1]

try this :

SELECT jsonb_path_query('[
  {
    "menuId": "",
    "menuText": "Service Request",
    "menuIcon": "vector_ic_agent_tools",
    "isEnabled": "Yes",
    "subMenu": [
      {
        "menuId": "",
        "menuText": "Customer Tools",
        "menuIcon": "vector_ic_new_account",
        "isEnabled": "Yes",
        "subMenu": [
          {
            "menuId": "AOIEnquiryActivity",
            "menuText": "AOI Update Request",
            "menuIcon": "vector_ic_new_account",
            "isEnabled": "yes",
            "subMenu": []
          },
          {
            "menuId": "SearchByCASAAccForLoanActivity",
            "menuText": "Loan Request",
            "menuIcon": "vector_ic_new_account",
            "isEnabled": "No",
            "subMenu": []
          }
        ]
      }
    ]
  }
]' :: jsonb, '$.** ? (@.menuId == "AOIEnquiryActivity" && @.isEnabled == "yes")')
LIMIT 1

but be carreful of the lower/upper case in your json keys and values ("yes" is different from "Yes")

see the test result in dbfiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Edouard

Related Questions