IN query with combination with 2 field

Question

I have a list of devices in cosmos DB documents (thousands), Make and SerialNumber is a unique identifier for each asset.

[{
    "make": "CAT",
    "serial": "A1",
    "NAME": "CAR"
},
{
    "make": "ZAT",
    "serial": "B1",
    "NAME": "BUS"
}, {
    "make": "CAT",
    "serial": "C1",
    "NAME": "TRUCK"
}, {
    "make": "ABC",
    "serial": "A1",
    "NAME": "Plane"
}, {
    "make": "ZAT",
    "serial": "A1",
    "NAME": "BIKE"
}
]

I need to get complete detail of Asset from the above documents based on below input device input.

[{
    "make": "CAT",
    "serial": "A1"
},
{
    "make": "ZAT",
    "serial": "B1"
}, {
    "make": "CAT",
    "serial": "C1"
}
]

I'm using below query to get complete detail(I know below query is incorrect). In general, I am getting passing 100s devices serial in a single query.

select * from t where serial in (A1,B1,C1) and make in (CAT,ZAT,CAT)

Is there any way to write IN query with combination with two fields?

Expected Result

A1 CAT

B1 ZAT

C1 CAT

Jay Gong Jay Gong · Accepted Answer · 2019-04-08T02:00:25

Pankaj Rawat,please use this query sql,it could meet your requirements.

SELECT c FROM c where
ARRAY_CONTAINS([
    {"serialNumber":"A1","make":"CAT"},
    {"serialNumber":"B1","make":"ZAT"},
    {"serialNumber":"C1","make":"CAT"}
    ],
    {"serialNumber": c.serialNumber,
       "make":c.make}
    )

Output:

IN query with combination with 2 field

1 Answers