Cosmos query to fetch only specific inner array items that meets specific condition-CodePudding

In cosmos DB the document structure is like this

[
  {
    "id": "1",
    "Plants": [
      {
        "PlantId": 3,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 4,
        "UniqueQualityId": "3_pe55d74fc5sdfmsdfklms"
      },
      {
        "PlantId": 10,
        "UniqueQualityId": "3_pe55d7akjdsj6ysdssdsd"
      },
      {
        "PlantId": 12,
        "UniqueQualityId": "5_fdffpe55d7akjdsj6ysds"
      }
    ],
    "CompletionTime": 36
  },
  {
    "id": "2",
    "Plants": [
      {
        "PlantId": 3,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 4,
        "UniqueQualityId": "3_pe55d74fc5sdfmsdfklms"
      },
      {
        "PlantId": 3,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 5,
        "UniqueQualityId": "3_pe55d7akjdsj6ysdssdsd"
      }
    ],
    "CompletionTime": 36
  },
  {
    "id": "2",
    "Plants": [
      {
        "PlantId": 10,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 11,
        "UniqueQualityId": "3_pe55d74fc5sdfmsdfklms"
      }
    ],
    "CompletionTime": 36
  }
]

I need to get the collection of plants that meets specific condition:

For example, the query is written as to fetch Plants along with some parent data where PlantId in ("3","4") , then the output am expecting is

[
  {
    "id": "1",
    "Plants": [
      {
        "PlantId": 3,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 4,
        "UniqueQualityId": "3_pe55d74fc5sdfmsdfklms"
      }
    ],
    "CompletionTime": 36
  },
  {
    "id": "2",
    "Plants": [
      {
        "PlantId": 3,
        "UniqueQualityId": "3_pe55d74fc5f92b11ab3fe"
      },
      {
        "PlantId": 4,
        "UniqueQualityId": "3_pe55d74fc5sdfmsdfklms"
      }
      }
    ],
    "CompletionTime": 36
  }
]

Here in the plants array it should only contain the items that meet the filtered condition.

I have tried the following methods

SELECT root["Plants"],root.id FROM root 
WHERE EXISTS(select value plant FROM plant in root.Plants WHERE plant.PlantId in ("3","4"))

SELECT root.id,root.Plants FROM root where ARRAY_CONTAINS(c.Plants,{"PlantId": "3"},true)

If any of the plant items meet the condition it is returning the entire plant array instead of specific items.

Is there any method where it will return only the specific array items that meet the condition?

CodePudding user response：

You can use the following query to get the result to get the output you want:

SELECT 
    c.id,
    ARRAY(
        SELECT VALUE p
        FROM p IN c.Plants
        WHERE p.PlantId IN (3, 4)
    ) AS Plants,
    c.CompletionTime
FROM c

Although my personal preference would be the query below that does the same, but creates a seperate item for every plant. From the context I understand that you are looking for specific plants and capture some parent data in the result. In that case it would make sense to have seperate results by the plant.

SELECT 
    c.id,
    p AS Plant,
    c.CompletionTime
FROM c
JOIN 
(
    SELECT VALUE p 
    FROM p IN c.Plants
    WHERE p.PlantId IN (3, 4)
) AS p