Query with SPLIT() not getting expected results

SQL++
1

I have documents that have the groupIDs value as a CSV string, this query shows the values for example:

Select SPLIT(ci.groupIds, ","), ci.courseInstanceId FROM default:`BvCourseInstance` ci
WHERE ci.groupIds IS NOT NULL
[
  {
    "$1": [
      "325775",
      "326059"
    ],
    "courseInstanceId": 118824
  },
  {
    "$1": [
      "325775",
      "326059"
    ],
    "courseInstanceId": 118826
  },

however this query instead of giving me a set of courseInstanceIds I get an empty set.

SELECT ci.courseInstanceId
FROM default:`BvCourseInstance` ci
WHERE "325775" IN (
    SELECT SPLIT(ci.groupIds, ","))

{
  "results": []
}

Why am I not getting any results???

does not split() yield an array of string values? why am I not finding matches for my constant?

2

SELECT SPLIT() makes ARRAY of OBJECT (of field $1 of split array), SELECT RAW SPLIT() removes object but makes double nested array.
right side of IN is required ARRAY or expression that result as ARRAY.
SPLIT is expression produces ARRAY and not required another SELECT.
So Use the following

SELECT ci.courseInstanceId
FROM `BvCourseInstance` ci
WHERE "325775" IN  SPLIT(ci.groupIds, ",")

If you looking index

CREATE INDEX ix1 ON  BvCourseInstance( DISTINCT SPLIT(groupIds, ","), courseInstanceId);

SELECT ci.courseInstanceId
FROM `BvCourseInstance` ci
WHERE  ANY v IN  SPLIT(ci.groupIds, ",") SATISFIES v IN ["325775"] END;
1 Like