Hash Index, Best Way?

SQL++
1

Sample Doc (I have 100’s of them)

{
	id: xxx,
    type: "blah",
	sites: {
		"B1": {
			"type":"building"
		},
		"B2": {
			"type":"building"
		},
		"U1": {
			"type":"unit"
		}
	}
}

I want to be able to select all “blahs” that have a sites that have “B1” and type=“building”

I see how to create an index for an Array, but what’s the best way for an index for this?

2 
CREATE INDEX ix1 ON default(DISTINCT ARRAY  [v.val.type, v.name] FOR v IN  OBJECT_PAIRS(sites) END) WHERE type = "blah";

SELECT * FROM default 
WHERE type = "blah" AND  ANY v IN OBJECT_PAIRS(sites)  SATISFIES    [v.val.type, v.name]  = ["building","B1"] END;
3

@vsr1 Thanks that worked

One more question is it possible to use a Select… Like for example to get all “B%” so it will get both B1 and B2

4

Equality is simple all others are complex and special care must have been taken.

For trailing %, name LIKE “BD%” nothing but name >= “BD” AND name < “BE”

SELECT * FROM default
WHERE ANY v IN OBJECT_PAIRS(sites)  SATISFIES    [v.val.type, v.name]  >= ["building","B"]  AND   [v.val.type, v.name]  < ["building", SUCCESSOR("B")]   END;

Also note “building” must be equality.