N1QL subquery in select

SQL++
1

Hi i am trying to implement sub-query in the select itself

Document 1:
{
“Date”: “2016/12/01”,
“IpAddress”: “192.168.0.1”,
“Region”: “Sout-Asia”,
“Country”: “India”,
“State”: “Gujarat”,
“City”: “Ahmedabad”,
“UserAgent”: “Mozilla”,
“Device”: “”,
“Impression”: “0”,
“Click”: “1”,
“Platform”: “Desktop”,
“Channel”: “B2B”,
“PageRegion”: “Header”,
“Zone”: “Head”,
“Advertisement”: “Advt2”,
“Advertiser”: “Advt1”,
“CampaignPlacmentId”: “1”,
“Url”: “abc”,
“Id”: 1
}

Document 2:

{
“Date”: “2016/12/02”,
“IpAddress”: “192.168.0.1”,
“Region”: “Sout-Asia”,
“Country”: “India”,
“State”: “Gujarat”,
“City”: “Ahmedabad”,
“UserAgent”: “Mozilla”,
“Device”: “”,
“Impression”: “1”,
“Click”: “1”,
“Platform”: “Desktop”,
“Channel”: “B2B”,
“PageRegion”: “Header”,
“Zone”: “Head”,
“Advertisement”: “Advt1”,
“Advertiser”: “Advt1”,
“CampaignPlacmentId”: “1”,
“Url”: “abc”,
“Id”: 2
}

Document 3

{
“Date”: “2016/12/01”,
“IpAddress”: “192.168.0.1”,
“Region”: “Sout-Asia”,
“Country”: “India”,
“State”: “Gujarat”,
“City”: “Ahmedabad”,
“UserAgent”: “Mozilla”,
“Device”: “”,
“Impression”: “1”,
“Click”: “1”,
“Platform”: “Desktop”,
“Channel”: “B2B”,
“PageRegion”: “Header”,
“Zone”: “Head”,
“Advertisement”: “Advt1”,
“Advertiser”: “Advt1”,
“CampaignPlacmentId”: “1”,
“Url”: “abc”,
“Id”: 3
}

And I have written query as below but its not working

SELECT
DISTINCT s.Platform,
COUNT(DISTINCT s.Channel) AS Channels,
COUNT(DISTINCT s.Advertisement) AS Advertisements,
SUM(CASE WHEN s.Click == “1” THEN 1 ELSE 0 END) AS TotalClicks,
SUM(CASE WHEN s.Impression == “1” THEN 1 ELSE 0 END) AS TotalImpressions,
(SELECT COUNT(DISTINCT IpAddress) AS UniqueClicks FROM default WHERE Platform = s.Platform AND Click = “1”) AS UniqueClicks
FROM
default AS s
GROUP BY
s.Platform
ORDER BY Platform

Or any other way the same output can be archived.

Any help is appreciated.

Regards,
Siddharth

2

Hi @siddharth.ruchwani,
What version are you using? What is the exact error you are getting??
Your query has two issues…

  1. The subquery in projection list must be an aggregate (as that is not in GROUP BY list). Change it to:
    ARRAY_AGG((SELECT COUNT(DISTINCT IpAddress) AS UniqueClicks FROM default WHERE Platform = s.Platform AND Click = "1")) AS UniqueClicks

Note that the subquery must be in parenthesis to be evaluated as an expression. Hence use see two parenthesis around the subquery.

  1. You are using correlated sub-query, with WHERE Platform = s.Platform. This requires USE KEYS (similar to a lookup JOIN). See https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/subqueries.html

-Prasad

3

@prasad Thanks for the reply. I am using Version: 4.5.0-2601 Community Edition (build-2601)

As per your suggestion i tried with the below query

SELECT
DISTINCT s.Platform,
COUNT(DISTINCT s.Channel) AS Channels,
COUNT(DISTINCT s.Advertisement) AS Advertisements,
SUM(CASE WHEN s.Click == “1” THEN 1 ELSE 0 END) AS TotalClicks,
SUM(CASE WHEN s.Impression == “1” THEN 1 ELSE 0 END) AS TotalImpressions,
ARRAY_AGG((SELECT COUNT(DISTINCT IpAddress) AS UniqueClicks FROM default USE KEYS s.Platform WHERE Click = “1” group by Platform)) AS UniqueClicks
FROM
default AS s
GROUP BY
s.Platform
ORDER BY
Platform

But still its not working. It gives me the following output. In the above query Platform is not the PrimaryKey or Document Key.

[
{
“Advertisements”: 2,
“Channels”: 1,
“Platform”: “Desktop”,
“TotalClicks”: 6,
“TotalImpressions”: 6,
“UniqueClicks”: [
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
]
]
},
{
“Advertisements”: 1,
“Channels”: 1,
“Platform”: “Mobile”,
“TotalClicks”: 3,
“TotalImpressions”: 3,
“UniqueClicks”: [
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
],
[
{
“UniqueClicks”: 0
}
]
]
}
]

So is it possible to make joins or sub-query with any other key then primary key.

Your help would be appreciated.

Regards,
Siddharth Ruchwani

4

No. This is not yet supported. For now, you will have to somehow generate the doc-key for the USE KEYS (to use correlated subquery). Or, try to rewrite the query using JOIN ( check index-joins Couchbase SDKs).

5

If your documents are related by primary key, you probably can accomplish your task using N1QL. cc @vsr1 @keshav_m

6

Hi @siddharth.ruchwani,

Form query my understanding is UniqueClicks expression that you are looking when CLick = 1 want to count distinct ip addresses. This can be achieved without subquery and joins with the following query.

SELECT s.Platform, COUNT(DISTINCT s.Channel) AS Channels, COUNT(DISTINCT s.Advertisement) AS Advertisements,
SUM(CASE WHEN s.Click == "1" THEN 1 ELSE 0 END) AS TotalClicks,
SUM(CASE WHEN s.Impression == "1" THEN 1 ELSE 0 END) AS TotalImpressions,
COUNT (DISTINCT  (CASE WHEN s.Click = "1" THEN IpAddress ELSE NULL END)) As UniqueClicks
FROM default AS s GROUP BY s.Platform  ORDER BY Platform;

Also DISTINCT in projection is not required because GROUP BY makes it DISTINCT. cc @geraldss

4 Likes
7

@vsr1 Thanks for the reply, this is what exactly I was looking for. I have already achieved my requirement by re-writing the query. But your suggested way looks much better and faster, will be using the same.

Thanks for the help.

1 Like
8

Hello…
I have two documents like below:
doc1:
{
“updateDescriptions”: [
{
“id”: “1”,
“updateDescription”: “September Updates for ‘For Profit’”
},
{
“id”: “2”,
“updateDescription”: “October Updates for ‘For Profit’”
},
{
“id”: “3”,
“updateDescription”: “November Updates for ‘For Profit’”
}
]
}

doc 2:
{
“lineItemID”: 5539,
“indent”: 2,
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”,
“updateDescriptionId”: “2”
}

I need to show the detailsof uodateDescriptionId 2 like below:
O/P–
{
“lineItemID”: 5539,
“indent”: 2,
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”,
“updateDescriptionId”: “2”,
“updateDescriptionDetails”:[
{
“id”: “2”,
“updateDescription”: “October Updates for ‘For Profit’”
}
]
}

Could you please help me with the query…

9

What are the Document Ids for Doc1 and Doc2? You need relation through document id in couchbase to join them

10

The document ids are different.
The common factor for these tow docs is “updateDescriptionId” only .

11

To Join in the N1QL the relation between documents needs to be present through the document key.
The following link explain full details how to JOIN in the N1QL https://dzone.com/articles/visually-explaining-n1ql-joins

i.e. doc1 needs to contain the doc2 document key or vice versa to join

12

On the above example , doc1 contains the updateDescriptionId which is present in doc2 as id.

Is it not ok?

13

That is not enough the relation needs to be through document key. The primary key relation ship is through document key.

Example : “doc1”, “docxyz” are document keys. These two can be joined because “doc1” contains the document key as join key.

“doc1” { “name”: “abc”, “joinKey”: “docxyz”}
“docxyz” {“f1”:123}

14

If you don’t have join relation through document key ,

You can do independent queries and JOIN through ARRAY’s. This may not best option but that is only option.

SELECT d.*,FIRST v FOR v IN ud WHEN d.updateDescriptionId = v.id END AS updateDescriptionDetails FROM default AS d USE KEYS ["doc2"]
LET ud = ARRAY_FLATTEN((SELECT RAW d2.updateDescriptions FROM default AS d2 USE KEYS ["doc1"]),2);
15

Can I use the query like this , because I have multiple documents having the same updateDescriptions. So I need to select all document from ChecklisttLibrary.
Query:
SELECT meta(d).id,d.*,FIRST v FOR v IN ud WHEN d.updateDescriptionId = v.id END AS updateDescriptionDetails
FROM ChecklistLibrary AS d
USE KEYS [meta(d).id]
LET ud = ARRAY_FLATTEN((SELECT RAW d2.updateDescriptions FROM ChecklistLibrary d2
USE KEYS [“For_Profit:updateDescription”]),2)

butt given below ERROOR:
{
“code”: 3000,
“msg”: “Ambiguous reference to field d.”,

As per the requirement same updateDescription can be used for may checklistlibrary document. So, I need to match witth all meta.id having that update description.

Please help…

16

Constant string is required as part of USE KEYS. Otherwise remove USE KEYS all together and provide predicate or query uses primary index.

17

Thanks a lot. Really appreciate your help…