Cosmos DB nested query inside another list

Question

I need to query inside a cosmos db object where it has a list of object, that list will have another list inside. I am being able to use ARRAY_CONTAINS if there is only one level of nested element. But I am trying to query based upon nested element inside nested element. for example

[
  {
    "a": "someValue",
    "b": [
      {
        "c": "someValue",
        "d": [
          {
            "e": 1,
            "f": 2
          },
          {
            "e": 3,
            "f": 4
          }
        ]
      }
    ],
    "g": "someValue"
  },
  {
    "a": "someValue",
    "b": [
      {
        "c": "someValue",
        "d": [
          {
            "e": 5,
            "f": 6
          },
          {
            "e": 7,
            "f": 8
          }
        ]
      }
    ],
    "g": "someValue"
  }
]

Now when I query like this I am not getting any result back

SELECT * FROM data where  ARRAY_CONTAINS(data.b.d, {"f": 8},true)

I want to get back the result like

[
 {
    "a": "someValue",
    "b": [
      {
        "c": "someValue",
        "d": [
          {
            "e": 5,
            "f": 6
          },
          {
            "e": 7,
            "f": 8
          }
        ]
      }
    ],
    "g": "someValue"
  }
]

Answer 1

I was able to find solution using joins for my question.

 SELECT VALUE c
 FROM c
 JOIN x IN c.b
 Join y IN x.d
 where y.f = 8

Answer 2

When I worked on your repro, the above code you mentioned didn’t worked.

Instead of that you can try like this with the ARRAY_CONTAINS:

SELECT * FROM c where  ARRAY_CONTAINS([c.b[0].d[1]], {"a":8},true) 

You will get the output in the form of Array.

SELECT * FROM c where  ARRAY_CONTAINS([c.b[0].d[0]], {"a":2},true)

You can also try this method without the ARRAY_CONTAINS:

SELECT * FROM c where c.b[0].d[1].a=8