alasql JSON Query - Limitation or User error? Unable to query an item which is inside an array but when not present on first position

Question

I just found alasql and its awesome! However I am not sure if this is a know limitation or an issue with my query but for some reason, I can't find value "algb2b-gb" in below JSON using query below. It looks like if the exact position of item in the array is not mentioned, then query is failing to give results. Is this a known limitation or is there a better way to query for array items?

Appreciate your thoughts.

Below is the SQL Query:

SELECT COUNT( * ) FROM ? WHERE profile->ad_site_permissions->0 = "algb2b-gb" 

Below is my JSON

    [{
        "id": "00u1fcghdvmRWnNb81d8",
        "profile": {
            "lastName": "AUvalidation",
            "ad_site_permissions": [
                "algb2b-us",
                "algb2b-it",
                "algb2b-de",
                "algb2b-au",
                "algb2b-nz",
                "algb2b-nl",
                "algb2b-fr",
                "algb2b-gb",
                "algb2b-es",
                "algb2b-pr",
                "algb2b-ca",
                "algb2b-br",
                "algb2b-ch"
            ],
            "ad_system_permissions": [
                "bpReadAccess",
                "ecomReadAccess"
            ]


        }
    }, {
        "id": "00u2zvh77sVZMACHYLIZ",
        "profile": {
            "ad_site_permissions": [
                "algb2b-ca",
                "algb2b-fr",
                "algb2b-it",
                "algb2b-de",
                "algb2b-nl",
                "algb2b-au",
                "algb2b-nz",
                "algb2b-gb",
                "algb2b-es",
                "algb2b-br"
            ],
            "ad_system_permissions": [
                "bpReadAccess",
                "ecomReadAccess"
            ]
        }
    }]

My output is

[
    {
        "COUNT(*)": 0
    }
]

If however I change my query to below, then it works:

SELECT COUNT( * ) FROM ? WHERE profile->ad_site_permissions->7 = "algb2b-gb" 

Went to the github site for documentation access.

Answer 1

SELECT COUNT( * ) FROM ? WHERE profile->ad_site_permissions->0 = "algb2b-gb"`

The above query will check the count of ad_site_permissions[0] array item which is algb2b-us and it not equal to algb2b-gb. Therefore count is 0.

you can optimize the query to search all indexes of the array for example:

var data = [{a:[1,2,3,4]}, {a:[6,7,8,9]}]

var response = alasql(`SELECT COUNT( * ) FROM ? WHERE 6 = any(a)`,[data]);

console.log(response) // [ { 'COUNT(*)': 1 } ]

so you can try something like this

 var res = alasql(`SELECT COUNT( * ) FROM ? WHERE  "algb2b-gb" = any(profile->ad_site_permissions) `, [data]);

console.log(res) // [ { 'COUNT(*)': 2 } ]