N1QL: How to create index to speed up query to find parents with > 0 children

N1QL
,
1

Hi,
I would like to speed up a query which should return documents WITH or WITHOUT children.
Here is an example:

doc1: (has 1 child)

{
  "master_id": "",
  "identifier": "m1",
  "childrenids": {
    "child1": null
  }
}

doc2: (has 2 children)

{
  "master_id": "",
  "identifier": "m2",
  "childrenids": {
    "child2": null,
    "child3": null
  }
}

doc3: (has 0 children)

{
  "master_id": "",
  "identifier": "m3",
  "childrenids": {
  }
}

doc4: (has also 0 children but the childrenids object is missing too)

{
  "master_id": "",
  "identifier": "m4"
}

N1QL Query #1 "Find all items with children:

select meta(mybucketname).id from datastoreitems WHERE (master_id = '' AND (childrenids IS NOT MISSING AND OBJECT_LENGTH(childrenids) > 0) )

Expected result:
doc1 and doc2

N1QL Query #2 "Find all items WITHOUT children:

select meta(mybucketname).id from datastoreitems WHERE AND (master_id = '' AND (childrenids IS MISSING OR OBJECT_LENGTH(childrenids) = 0) )

Expected result:
doc3 and doc4

The queries above are working but are slow, because there is no index in the childrenids object (which is supposed to be a Map). The reason we modelled it as a map (and not an array) is, that we are using SubDoc-API to add/remove field-pairs. Thus you see varying number of fields in childrenids.

My Question:

How should I create a GSI index to speed up the OBJECT_LENGTH(childrenids) > 0 or OBJECT_LENGTH(childrenids) = 0 part of the query?

Thanks
Christoph

2

try this index

CREATE INDEX `idx-childrenids-length` ON `default`(OBJECT_LENGTH(IFMISSINGORNULL(childrenids,{}))) WHERE (master_id = '');

with following N1QL

N1QL Query #1 "Find all items with children:

select meta(default).id from default WHERE (master_id = '' AND OBJECT_LENGTH(IFMISSINGORNULL(childrenids,{})) > 0)

and
N1QL Query #2 "Find all items WITHOUT children:

select meta(default).id from default WHERE (master_id = '' AND OBJECT_LENGTH(IFMISSINGORNULL(childrenids,{})) = 0)

replace default to your bucket name.
two N1QL will use idx-childrenids-length as CoverScan.

2 Likes