Array Intersect for sub documents

I have yet another strange request.
I have a query that runs on a LocId and returns the below result.

LocId = 123

 [      
      {
        "Descriptions": {
          "en": "3.625 SS TOS NAC CHS"
        },
        "EffDtRnge": {
          "EndDt": "9999-12-25",
          "StrtDt": "2020-03-18"
        },
        "MtrlUomCdv": "017",
        "MtrlUomId": "10378500"
      },
      {
        "Descriptions": {
          "en": "3.625 SS TOS NAC CHS"
        },
        "EffDtRnge": {
          "EndDt": "9999-12-25",
          "StrtDt": "2020-03-18"
        },
        "MtrlUomCdv": "004",
        "MtrlUomId": "10378501"
      }
    ]

We need to run this query on 5 such LocIds and return a result that is an intersect of all the responses (not Distinct but an intersect). I am looking at ARRAY_INTERSECT but it does not seem to work if the array is an array of objects rather than singular items like a string.

Any ideas on how I can get an intersect?

LocId = 567

 [      
      {
        "Descriptions": {
          "en": "3.625 SS TOS NAC CHS"
        },
        "EffDtRnge": {
          "EndDt": "9999-12-25",
          "StrtDt": "2020-03-18"
        },
        "MtrlUomCdv": "017",
        "MtrlUomId": "10378500"
      }
    ]

Using the above 2 object arrays, we want the output to be as below. I may be asking for too much but … it is what it is… :anguished:

      {
        "Descriptions": {
          "en": "3.625 SS TOS NAC CHS"
        },
        "EffDtRnge": {
          "EndDt": "9999-12-25",
          "StrtDt": "2020-03-18"
        },
        "MtrlUomCdv": "017",
        "MtrlUomId": "10378500"
 }

Each argument of ARRAY_INTERSECT() must be array. It works.

SELECT ARRAY_INTERSECT([      { "Descriptions": { "en": "3.625 SS TOS NAC CHS" }, "EffDtRnge": { "EndDt": "9999-12-25", "StrtDt": "2020-03-18" }, "MtrlUomCdv": "017", "MtrlUomId": "10378500" }, { "Descriptions": { "en": "3.625 SS TOS NAC CHS" }, "EffDtRnge": { "EndDt": "9999-12-25", "StrtDt": "2020-03-18" }, "MtrlUomCdv": "004", "MtrlUomId": "10378501" } ], [      { "Descriptions": { "en": "3.625 SS TOS NAC CHS" }, "EffDtRnge": { "EndDt": "9999-12-25", "StrtDt": "2020-03-18" }, "MtrlUomCdv": "017", "MtrlUomId": "10378500" } ]) AS s;

{
        "s": [
            {
                "Descriptions": {
                    "en": "3.625 SS TOS NAC CHS"
                },
                "EffDtRnge": {
                    "EndDt": "9999-12-25",
                    "StrtDt": "2020-03-18"
                },
                "MtrlUomCdv": "017",
                "MtrlUomId": "10378500"
            }
        ]
    }

I am not sure what is your query is
You might want use GROUP BY with HAVING like below

SELECT  RAW d.xxx
FROM default AS d
WHERE d.LocId IN [ 1,2,3,4,5]
GROUP BY d.xxx
HAVING COUNT(1) >=  5;

If you have 5 different queries try to use
Q1 INTERSECT Q2 INTERSECT Q3 INTERSECT Q4 INTERSECT Q5
OR
SELECT ARRAY_INTERSECT ((Q1), (Q2), (Q3), (Q4), (Q5));

I got it to work. I missed adding RAW to the query. I feel like an i**** :anguished: