Merging results of two queries

SQL++
,
1

I have sample results of two separate queries here:
Query 1:

{
    "id": "f95102e7-03aa-4719-b770-086f099e595c",
    "name": "Information Technology Company",
    "count1": 5
}

Query 2:

{
    "id": "b621635e-f334-43f1-a634-980f2b7e86c4",
    "name": "College & University",
    "count2": 1
}

I have to combine the results so that the final result will look like this:

{
    "id": "b621635e-f334-43f1-a634-980f2b7e86c4",
    "name": "College & University",
    "count1": 5,
    "count2": 1
}

So far I have tried to use UNION but the results still appear as separate documents. Using object_concat only displays one result, which is not suited for my requirements and seems to be inefficient since I need to get all at the same time. Is there any other approach I can use for this? Thanks.

2

Hello, Johanna.

Could you explain why object_concat() is not workable in your situation? It looks like the right solution.

3

SELECT q1.count1, q2.count2, q2.id, q2.name
LET q1 = ( SELECT RAW { projection } FORM …)[0],
q2 = (SELECT RAW {projection } FORM …)[0];

4

I have come up with the query below:

SELECT OBJECT_CONCAT(
   (SELECT DISTINCT c._id AS id, c.name AS name, b.count AS count1
    FROM bucket1 a
    UNNEST a.aggregates b
    LEFT JOIN bucket1 c
    ON KEYS "Industry|" || b.industry.`$ref`
    WHERE a._type = "Type1"
    AND a.`user`.`$ref` = "74de7616-698c-4b1c-923c-e554cb1a5b90"
    ORDER BY b.count DESC
    LIMIT 12)[0],
   (SELECT DISTINCT y.count AS count2
    FROM bucket1 z
    UNNEST z.aggregates y
    LEFT JOIN bucket1 x
    ON KEYS "Industry|" || y.industry.`$ref`
    WHERE z._type = "Type2"
    AND z.`user`.`$ref` = "74de7616-698c-4b1c-923c-e554cb1a5b90"
    AND y.industry.`$ref` IN (ARRAY_HERE))[0]
 );

and it returns the following:

{
   "id": "b621635e-f334-43f1-a634-980f2b7e86c4",
   "name": "College & University",
   "count1": 5,
   "count2": 1
}

which is the desired format but it only returns one document. How do I get my query to do this for all entries?

6

I tried this as you suggested but it is also only returning one document at a time. I think may be missing something here…

7

My post is for query that returns one document. Each query returns different set of documents what is your Join criteria for combine them into single document.

8

Here’s how to run OBJECT_CONCAT on pairs of elements of arrays:

SELECT ARRAY OBJECT_CONCAT(v1, v2) FOR v1 IN [{“a”:1},{“a”: 2}, {“a”: 3}], v2 IN [{“b”:11}, {“b”:12}, {“b”:13}] END res

Can you extrapolate from that, Johanna?

Result:
[
{
“res”: [
{
“a”: 1,
“b”: 11
},
{
“a”: 2,
“b”: 12
},
{
“a”: 3,
“b”: 13
}
]
}
]

9

I was trying to run this query

SELECT q1, q2
LET q1 = ( SELECT META(response).id, response.assessmentId, response.respondedAt,response.respondedBy, response.assessmentVersion, assessment.assessmentName, response.finalScore, response.inflight, response.responses, response.actionItems, response.scores FROM secat-dev AS response JOIN secat-dev AS assessment ON KEYS response.assessmentId WHERE response.objectType = ‘response’ and response.inflight = false order by (select SUBSTR(date,6,4)||SUBSTR(date,2,4)||SUBSTR(date,0,2)|| ‘T’||SUBSTR(date,11,11)||’:’||SUBSTR(date,22,2) LET date=response.respondedAt) DESC offset 0 limit 10)
LET q2 = ( select count(*) from secat-dev where inflight = false);

To get the following result -

[
{
“q1”: [
{
“assessmentId”: “ASMT-d73e109f-ab34-47d7-8532-3e99da92c6c7”,
“assessmentName”: “Satish Ashwin”,
“assessmentVersion”: 1,
“finalScore”: 3,
“id”: “RESP-c08c8df0-d8be-426a-8151-ec8db7e33f65”,
“inflight”: false,
“respondedAt”: “12-02-2018 09:22:24+0530”,
“respondedBy”: “Datahub”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 2,
“finalScore”: 1,
“id”: “RESP-c63ad117-a307-4e9a-b184-ce0ed2e7add4”,
“inflight”: false,
“respondedAt”: “01-02-2018 16:55:01+0530”,
“respondedBy”: “autonomy”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 2,
“finalScore”: 1.1,
“id”: “RESP-9966cc66-ab5c-45db-82f3-50a9d7508b17”,
“inflight”: false,
“respondedAt”: “01-02-2018 16:01:30+0530”,
“respondedBy”: “TURBO”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 2,
“finalScore”: 1.9,
“id”: “RESP-b0eb77b1-feb7-482f-8bab-f647a9028028”,
“inflight”: false,
“respondedAt”: “24-01-2018 00:19:32+0000”,
“respondedBy”: “Configuration Management”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 3.5,
“id”: “RESP-b24d1899-417c-48a1-a1ff-99a0851a5d43”,
“inflight”: false,
“respondedAt”: “22-01-2018 01:02:58+0000”,
“respondedBy”: “Kaveri”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 2.1,
“id”: “RESP-efb8ea41-b453-4c57-8e02-6e7fccfea2d1”,
“inflight”: false,
“respondedAt”: “18-01-2018 23:37:01+0000”,
“respondedBy”: “CM”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 4.8,
“id”: “RESP-87c8127a-1203-4328-a3fa-cf47be0bda50”,
“inflight”: false,
“respondedAt”: “16-01-2018 01:13:22+0000”,
“respondedBy”: “Skywalker”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 2.4,
“id”: “RESP-7dcf0fae-6761-474b-8753-756bc28a5aa2”,
“inflight”: false,
“respondedAt”: “15-01-2018 04:54:54+0000”,
“respondedBy”: “Galaxy”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 3.5,
“id”: “RESP-2f627921-44a6-435c-bbbf-49df844607e6”,
“inflight”: false,
“respondedAt”: “05-01-2018 07:42:44+0000”,
“respondedBy”: “TYConnectSingapore”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“assessmentName”: “Devops”,
“assessmentVersion”: 1,
“finalScore”: 1.8,
“id”: “RESP-4fc0fc7e-9079-469b-ba22-9d9c4464a4e1”,
“inflight”: false,
“respondedAt”: “03-01-2018 02:19:48+0000”,
“respondedBy”: “Golden Wattle”
}
],
“q2”: {
"$1": 77
}
}
]

But instead getting error in LET query -

[
{
“code”: 3000,
“msg”: “syntax error - at LET”,
“query_from_user”: “SELECT q1, q2\r\nLET q1 = ( SELECT META(response).id, response.assessmentId, response.respondedAt,response.respondedBy, response.assessmentVersion, assessment.assessmentName, response.finalScore, response.inflight, response.responses, response.actionItems, response.scores FROM secat-dev AS response JOIN secat-dev AS assessment ON KEYS response.assessmentId WHERE response.objectType = ‘response’ and response.inflight = false order by (select SUBSTR(date,6,4)||SUBSTR(date,2,4)||SUBSTR(date,0,2)|| ‘T’||SUBSTR(date,11,11)||’:’||SUBSTR(date,22,2) LET date=response.respondedAt) DESC offset 0 limit 10)\r\nLET q2 = select count(*) from secat-dev where inflight = false;”
}
]

I am not sure where i am going wrong. Can anyone help me out understand this ?

10

Got it correct by running this.

SELECT q1, q2
LET q1 = ( SELECT META(response).id, response.assessmentId, response.respondedAt,response.respondedBy, response.assessmentVersion, assessment.assessmentName, response.finalScore FROM secat-dev AS response JOIN secat-dev AS assessment ON KEYS response.assessmentId WHERE response.objectType = ‘response’ and response.inflight = false order by (select SUBSTR(date,6,4)||SUBSTR(date,2,4)||SUBSTR(date,0,2)|| ‘T’||SUBSTR(date,11,11)||’:’||SUBSTR(date,22,2) LET date=response.respondedAt) DESC offset 0 limit 10),
q2 = ( select count(*) from secat-dev where inflight = false );

Multiple LET keywords are not allowed.

11

one LET keyword with comma separated. Check the syntax diagram

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/let.html