Querying array with single elements

SQL++
1

HI All,
I have document like

{
“Name” : “George”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking”,“biking”,“snow boarding”]
}

now i want select the data on basis of hobbies… How to do same using aaray.
query is like :- select * from bucketName where Hobbies =“biking”

2 
SELECT d.*
FROM bucketName AS d
WHERE ANY v IN  d.`Hobbies` SATISFIES v = "biking" END;

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

3

What if the counts required ???

4

Count by hobbies: SELECT h,count(1) FROM bucketName as d UNNEST Hobbies as h GROUP BY h
Count hobbies by Name : SELECT d.Name, ARRAY_COUNT(d.Hobbies) FROM bucketName

5

This result give only for the document . What if we have multiple documents and want to count the users who is having the same hobbies ?

6

can you write out the output you would like to see?

7

Thanks for asking @binh.le

Say if we have

{
“Name” : “George”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking”,“biking”,“snow boarding”]
},
{
“Name” : “Charlie”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking”,“biking”,“Listening Music”]
},
{
“Name” : “Mary”,
“Job Title” : “Software Engineer”
“Hobbies” : [“Painting”,“Swimming”,“Listening Music”]
}

O/P :

"Hobbies ":[{“Painting”,1},{“Swimming”,1},{Listening Music:2},{“hiking”,2},{“biking”,2},{“snow boarding”,1}]

8

That’s the first query - Count by hobbies: SELECT h,count(1) FROM bucketName as d UNNEST Hobbies as h GROUP BY h

9

Could you please help with the index ?

10

Index only helps when the query has predicate(s), and the predicates are selective.
https://blog.couchbase.com/create-right-index-get-right-performance/

11

[quote=“vinmroy, post:7, topic:19480”]

@ [binh.le]
{
“Name” : “George”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking”,“biking”,“snow boarding”]
},
{
“Name” : “Charlie”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking”,“biking”,“Listening Music”]
},
{
“Name” : “Mary”,
“Job Title” : “Software Engineer”
“Hobbies” : [“Painting”,“Swimming”,“Listening Music”]
}

what if my O/P needs to be like this

{
“Name” : “George”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking” : 3 ,“biking” : 2 ,“snow boarding” : 1]
},
{
“Name” : “Charlie”,
“Job Title” : “Software Engineer”
“Hobbies” : [“hiking” : 3 ,“biking” : 2 ,“Listening Music” : 2 ]
},
{
“Name” : “Mary”,
“Job Title” : “Software Engineer”
“Hobbies” : [“Painting” : 1,“Swimming” : 1,“Listening Music”: 2 ]
}

12

The representation is not possible because count() is group operation and you can’t project non group fields.

If you really needed use two queries and match using array construct to present your desired results .

SELECT d.Name,
        d.`Job Title`, 
        ARRAY  (FIRST {hv.h:hv.cnt}  FOR hv IN ha WHEN hv.h = v END) FOR v IN d.Hobbies  END AS Hobbies
FROM default AS d
LET ha = (SELECT h, COUNT(1) AS cnt FROM default AS d1 UNNEST d1.Hobbies AS h GROUP BY  h);
13

@vsr1 Thanks a lot for the query . Does it have any performance issue if we run this on millions of records ?? I created the index on the hobbies .

14

If Hobbies are small number u don’t see any impact. If those are lot you will see it.
There is no other alternative because aggregate queries can only project aggregates or group by expressions.

15

Thanks @vsr1 for your inputs and help on the query