N1Ql query help

SQL++
1

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"]
2

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
3

Can you also post the following result:

SELECT * FROM content LIMIT 1;

4

@nraboy @geraldss

Hi Nic - Thank you. Your suggestion works perfectly.

5

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

7

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

8

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,

9

Thank you, Nic. I will take a look.

10

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

11

@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.

12

Thank you, Volker. Appreciate it.