Indexing Unnamed Array

SQL++
1

I am trying to index the Usefull_strings

"sync_gateway": {
    "_sync": {...},
    "bid": [
        "string",
        "Usefull_string",
        "Usefull_string"
    ],
    "type": "bcn_info",
    "venue": {
        "id": "2448328e-57f5-402b-aa06-1c422e79aab0",
        "pos": [
            int,
            int
        ],
        "room": "string"
    }
}

bid[1], bid[2] together are unique. I may need to search them together as a key, or bid[1] alone.
(if it helps, they are zero-padded numbers with 4 digits. Never searched with SUBSTR() or LIKE "")

This is my attempt using (https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html#topic_hv4_sbr_w5__query-predicate-format):

CREATE INDEX `bcn_info-bid-major` ON agora_bucket
(DISTINCT ARRAY v[1] FOR v IN bid END, DISTINCT ARRAY v[2] FOR v IN bid END)
WHERE type="bcn_info";
2

In your data above, what are the exact values of

bid[1] = ??

bid[2] = ??

3

Numerical strings (i.e. numbers, but zero-padded)
They just are zero-padded [because that’s what the previous owner left me with. I don’t care if I have to search 10 or 0010 - I know it may make a difference on the index, but it’s not an option to go through that aswell]

4

Are you saying that in your data above, bid[1] contains the following string:

Numerical strings (i.e. numbers, but zero-padded)
They just are zero-padded [because that's what the previous owner left me with. I don't care if I have to search 10 or 0010 - I know it may make a difference on the index, but it's not an option to go through that aswell]
5

"bid": [ "17971a3189dbad38936f77e1f807c853", "0014", "0014"]

A “unique” 32-char hex string, a 4-digit zero-padded number, and … a 4-digit, zero-padded number.
I do not need the 32-char string - I want to be able to search documents WHERE type="bcn_scan" by their bid[1] number or by both bid[1] and bid[2] number

6

Are your array subscripts 0-based or 1-based? That is what I was trying to understand when I asked for the values of bid[1] and bid[2] from your initial data. I still didn’t see an answer using your initial data.

7

The bid-entries look like what I posted above:

And I am interested on the lat
I guess, 0-based is the ‘correct’ way?

8

Which 2 things together are unique. Please post an actual sample document, and then post the actual 2 values that together are unique.

9

I posted an actual document right on the first post. I only blinded the data, because technically I am not even supposed to access them :slight_smile:

"sync_gateway": {
    "_sync": {...},
    "bid": [
        "17971a3189dbad38936f77e1f807c853",
        "0014",
        "0001"
    ],
    "type": "bcn_info",
    "venue": {
        "id": "2448328e-57f5-402b-aa06-1c422e79aab0",
        "pos": [
            0,
            0
        ],
        "room": "string"
    }
}

Do you require the _sync collection aswell?

"0014", "0001" together are unique

10

Better, thank you :slight_smile: I will take a look.

11

Here you go.

CREATE INDEX idx_bid ON some_data( bid[1], bid[2] );

EXPLAIN
SELECT *
FROM some_data
WHERE bid[1] = value1;

EXPLAIN
SELECT *
FROM some_data
WHERE bid[1] = value1 AND bid[2] = value2;