N1QL query to filter JSON array in Couchbase server

Question

I have the following array of data inside the data bucket SITES in my Couchbase server

"siteMaster": [
                {
                    "sitename": "HTS_SITE_001",
                    "sitelink": "http://facebook.com",
                    "address" : "19/2, Bellandur, Bangalore India",
                    "filename": "site1.json",
                    "persons": 1,
                    "status": "70%",
                    "contact": "max.smith@honeywell.com",
                }, {
                    "sitename": "HTS_SITE_002",
                    "sitelink": "http://facebook.com",
                    "address": "5th Avenue, New York",
                    "filename": "site2.json",
                    "persons": 1,
                    "status": "70%",
                    "contact": "john.smith@facebook.com",
                }, {
                    "sitename": "HTS_SITE_003",
                    "sitelink": "http://facebook.com",
                    "address": "Palo Alto, California",
                    "filename": "site3.json",
                    "persons": 1,
                    "status": "80%",
                    "contact": "steve.jobs@apple.com",
                }, {
                    "sitename": "HTS_SITE_004",
                    "sitelink": "http://facebook.com",
                    "address": "Bellandur, Bangalore",
                    "filename": "site4.json",
                    "persons": 1,
                    "status": "80%",
                    "contact": "max.mustermann@deutsche.com",
                }
            ]

The N1QL query for

select * from SITES where status = "70%" should return me two rows, but unfortunately it is not returning any rows.

Where am I going wrong with the query ?

Answer 1

Please use the following query:

SELECT *
FROM SITES
WHERE ANY sm IN siteMaster SATISFIES sm.status = "70%" END;

You can also create the following array index to speed up the query:

CREATE INDEX idx ON SITES( DISTINCT ARRAY sm.status FOR sm IN siteMaster END );