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": "[email protected]",
                }, {
                    "sitename": "HTS_SITE_002",
                    "sitelink": "http://facebook.com",
                    "address": "5th Avenue, New York",
                    "filename": "site2.json",
                    "persons": 1,
                    "status": "70%",
                    "contact": "[email protected]",
                }, {
                    "sitename": "HTS_SITE_003",
                    "sitelink": "http://facebook.com",
                    "address": "Palo Alto, California",
                    "filename": "site3.json",
                    "persons": 1,
                    "status": "80%",
                    "contact": "[email protected]",
                }, {
                    "sitename": "HTS_SITE_004",
                    "sitelink": "http://facebook.com",
                    "address": "Bellandur, Bangalore",
                    "filename": "site4.json",
                    "persons": 1,
                    "status": "80%",
                    "contact": "[email protected]",
                }
            ]

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 ?

Please upgrade to Couchbase 4.1 or 4.5 DP.

geraldss
– geraldss

2016-03-31 18:10:37 +00:00
Commented Mar 31, 2016 at 18:10 — geraldss
– geraldss, Commented Mar 31, 2016 at 18:10

geraldss · Accepted Answer · 2016-04-01 16:12:04Z

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 );

edited Apr 1, 2016 at 16:12

answered Apr 1, 2016 at 5:49

geraldss

2,4451 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Barker Over a year ago

Is there a way to select the indexes into siteMaster that match?

Collectives™ on Stack Overflow

N1QL query to filter JSON array in Couchbase server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related