N1Ql query help

I have 2 level nested JSON as below, when i run below query i get zero records even though i have more than 10 records matching the tags. Is my query correct?

Couchbase document json:

“Content”:
{
“search”:{
“tags”:[
“powerpoint”,
“ppt”
]
}
}

n1ql Query

SELECT *
FROM content
UNNEST content.search.tags AS tag
    WHERE LOWER(tag) in ["powerpoint", "ppt"]

Hey @badrictl,

What if instead of using an UNNEST, you made use of the ANY/SATISFIES condition in your WHERE clause. For example, your query would look something like this:

SELECT
    *
FROM `bucket-name-here`
WHERE
    ANY tag IN search.tags SATISFIES tag IN ['powerpoint', 'ppt'] END;

Let me know if that covers what you need.

Best,

1 Like

Can you also post the following result:

SELECT * FROM content LIMIT 1;

@nraboy @geraldss

Hi Nic - Thank you. Your suggestion works perfectly.

No problem! I’m glad it works :slight_smile:

Nic - Whats the best way to search for documents within ‘x’ radius based on lat, long or zip using n1ql

Hey @badrictl,

While this is possible (for longitude and latitude), it does involve a lot of complex math to accomplish the job. A Google search lists a variety of formulas to accomplish the job, most of which should work fine with Couchbase.

You’ll be using various number functions like ACOS, etc.

Hopefully that helps.

Best,

Thank you, Nic. I will take a look.

Nic - Can the radius base search be done using Spatial View Query in Couchbase 4.0 (java)

@badrictl You can’t directly do radius search with Spatial Views. You would need to do that in your application. You would calculate a bounding box around the radius you want to search for, and then filter out the false positives.

Thank you, Volker. Appreciate it.