Using IN to find value in array then another IN to find value in sub-array

SQL++
1

I am trying build a query to find the relevant warehouses based on the stateName and storageType that the manager object would have access too. I thought to use IN for the states array but then the sub-array of storage needs to match too.

This would be the manager object.

{
“id”: “managerID”,
"_type": "manager"
“email”:”abc@sfdf.com”,
“states”: [
    {
        “stateName”:”California”.
        “storage”:[
            {
                “storageType”:”Electronics”,
                ”clearance”: 1
            },
            {
                “storageType”:”Clothing”,
                ”clearance”: 3
            }
        ]
    },
    {
        “stateName”:”Texas”.
        “storage”: [
            {
                “storageType”:”Electronics”,
                ”clearance”: 2
            },
            {
                “storageType”:”Raw Materials”,
                ”clearance”: 4
            }
        ]
    }  
]
}

These would be examples of matching results.

[
{
"id": “11111”,
"_type": "warehouse",
"stateName": “California”,
"storageType":“Electronics”
},
{
"id": “12222”,
"_type": "warehouse",
"stateName": “California”,
"storageType":“Clothing”
},
{
"id": “13333”,
"_type": "warehouse",
"stateName": “Texas”,
"storageType":“Electronics”
}
]
2

you can use UNNEST to get stateName and storageType
for example

SELECT s.stateName,st.storageType 
  FROM default m USE KEYS ["managerID"] UNNEST m.states s UNNEST s.storage st

then you can use stateName and storageType to match your data.

1 Like
3

maybe this is what you need
sample data

INSERT INTO default VALUES("managerID", {
"id": "managerID",
"_type": "manager",
"email":"abc@sfdf.com",
"states": [
    {
        "stateName":"California",
        "storage":[
            {
                "storageType":"Electronics",
                "clearance": 1
            },
            {
                "storageType":"Clothing",
                "clearance": 3
            }
        ]
    },
    {
        "stateName":"Texas",
        "storage": [
            {
                "storageType":"Electronics",
                "clearance": 2
            },
            {
                "storageType":"Raw Materials",
                "clearance": 4
            }
        ]
    }  
]
});




INSERT INTO default VALUES("11111", {
"id": "11111",
"_type": "warehouse",
"stateName": "California",
"storageType":"Electronics"
})
INSERT INTO default VALUES("12222", {
"id": "12222",
"_type": "warehouse",
"stateName": "California",
"storageType":"Clothing"
})
INSERT INTO default VALUES("13333", {
"id": "13333",
"_type": "warehouse",
"stateName": "Texas",
"storageType":"Electronics"
})
INSERT INTO default VALUES("14444", {
"id": "14444",
"_type": "warehouse",
"stateName": "California",
"storageType":"Raw Materials"
})

N1QL

SELECT a.res[0].*
  FROM (
        SELECT ARRAY w FOR ma IN (SELECT RAW {"stateName":s.stateName,"storageType":st.storageType} 
                          FROM default m USE KEYS ["managerID"] 
                        UNNEST m.states s UNNEST s.storage st)
                    WHEN (w.stateName == ma.stateName AND w.storageType == ma.storageType)
                END AS res       
          FROM default w 
         WHERE w._type == "warehouse"
  ) a
 WHERE ARRAY_LENGTH(a.res) > 0

result data

[
  {
    "_type": "warehouse",
    "id": "11111",
    "stateName": "California",
    "storageType": "Electronics"
  },
  {
    "_type": "warehouse",
    "id": "12222",
    "stateName": "California",
    "storageType": "Clothing"
  },
  {
    "_type": "warehouse",
    "id": "13333",
    "stateName": "Texas",
    "storageType": "Electronics"
  }
]
4

Based on @atom_yang query the UNNEST document used only in predicate not in projection may be ANY clause will be better (to avoid duplicate of left side of unnest). Try all the variations and which ever performs better you can use that.

SELECT RAW w FROM default AS w
LET m = (SELECT RAW m FROM default AS m USE KEYS ["managerID"])[0]
WHERE  w._type = "warehouse" AND
       ANY s IN m.states SATISFIES w.stateName = s.stateName AND w.storageType IN s.storage[*].storageType END;

SELECT RAW w FROM default m USE KEYS ["managerID"]
UNNEST (SELECT RAW w FROM default AS w WHERE  w._type = "warehouse") AS w
WHERE ANY s IN m.states SATISFIES w.stateName = s.stateName AND w.storageType IN s.storage[*].storageType END;
2 Likes
5

That worked perfectly, thank you. I never thought to use LET.