title: Oracle query for nested JSON (with duplicate field names and dynamic multiple nested levels)
body: <p>I have sample JSON as below, please note how many times "listOfItems" tag appears is dynamic, it can be just one or many children's within child's and so on. 
  JSON's are stored in a CLOB column, this is just one sample JSON to help understand its structure. The nested levels is dynamic and vary from one JSON to another. 
  The table will have many rows with varying nested levels. I should be able to search for a field and its value across all rows to figure out the matching rows or the rows that have the element and its value per input.</p>
  
  <pre><code>{
  "id": 1000,
  "version": 1,  
  "number": "123456",
  "type": "Y",
  "itemName": "some name1",
  "listOfItems": [
  {
    "id": 2000,
    "version": 2,
  
    "number": "234567",
    "type": "Y",
    "itemName": "some name2",
  
    "listOfItems": [
      {
        "id": 3000,
        "version": 3,
  
        "number": "345678",
        "type": "Y",
        "itemName": "some name3",
  
        "listOfItems": [
          {
            "id": 4000,
            "version": 4,
  
            "number": "456789",
            "type": "N",
            "itemName": "some name2",
  
            "listOfItems": [
              {
                "id": 5000,
                "version": 5,
  
                "number": "567890",
                "type": "N",
                "itemName": "some name3",
  
                "listOfItems": [                    
                ]                  
              },
              {
                "id": 6000,
                "version": 6,
  
                "number": "678901",
                "type": "N",
                "itemName": "some name3",
  
                "listOfItems": [
  
                ]
  
              }                
            ]
          }
        ]
      }
    ]
  }
  ]
  }
  </code></pre>
  
  <p>Oracle version is - Oracle Database 12c (12.1.0.2.0)
  The requirement is to create a view from all nested levels with all available child fields and their values. So later we can query this view to find if an item with a field value or name, etc exists within a given JSON or not.</p>