How to select this dataset with n1ql?

SQL++
1

Hello,

Apologies for my title. I did not know how to title this question.

I have the following docs.

doc1
{
    "product_types": [
        {
            "id": 3
        },
        {
            "id": 4
        }
    ]
}

doc2
{
    "product_types": [
        {
            "id": 3
        },
        {
            "id": 6
        }
    ]
}

doc3
{
    "product_types": [
        {
            "id": 3
        },
        {
            "id": 7
        }
    ]
}

I’m trying to select docs that contain product type id 3 or 7.

I have the following query

select meta(bucket).id from bucket where any productType in product_types satisfies productType.id in [3,7] end limit 2;

this returns doc1 and doc2 because doc2 has id 3 as well.

Is there a way to make my query to return only doc1 and doc3?

my end goal is to have a list of documents with a given set of product type ids. In this case, [3,7]

2

What is the reason for returning doc1 but not doc2?

3

I want to avoid duplicate. If the query result already has a doc with 3, then I just want that single doc and move on to the next product id.

4

Two options using UNNEST.

SELECT MIN( [ pt.id, META(b).id ] )
FROM b UNNEST b.product_types pt
WHERE pt.id IN [3,7]
GROUP BY pt.id;

SELECT MIN( [ pt.id, s.id ] )
FROM
(SELECT META(b).id, product_types
FROM b
WHERE ANY pt IN product_types SATISFIES pt.id IN [3,7] END) AS s
UNNEST s.product_types pt
WHERE pt.id IN [3,7]
GROUP BY pt.id;
5

You also need the index.

CREATE INDEX idx_pt ON b( DISTINCT ARRAY pt FOR pt IN product_types END );

6

Thank you so much. I will test it out with the dataset.