Format n1ql query output

SQL++
,
1

I am using CB 4.1

I have following doc for TSub:

{
"_type": "TSub",
"taskId": "2dd7312e-01e7-4152-b179-88514d1f2413",
"userId": "B1PHCzHn57d79e3f",
"submissions": [
	{
	"media": {
		"url":"xyz.com/asd.jpg",
		"resolution":"1920*800"
		"name":"asd.jpg",
		"lat":20.500,
		"long":60.24,
		"size":42514
	},
	"submissionId": "1",
	"submissionStatus": "ACCEPTED"
	},
	{
	"media": {
		"url":"xyz.com/asd.jpg",
		"resolution":"1920*800"
		"name":"asd.jpg",
		"lat":20.500,
		"long":60.24,
		"size":42514
	},
	"submissionId": "1",
	"submissionStatus": "ACCEPTED"
	},
]

}

Now I want to fetch all the submission with task 2dd7312e-01e7-4152-b179-88514d1f2413 which are doc mentioned above in following form. There are multiple TSub doc for same task

{
	"taskId": "2dd7312e-01e7-4152-b179-88514d1f2413",
	"submissions" : [{
		"media": {
			"url":"xyz.com/asd.jpg",
			"resolution":"1920*800"
			"name":"asd.jpg",
			"lat":20.500,
			"long":60.24,
			"size":42514
		},
		"submissionId": "1",
		"submissionStatus": "ACCEPTED",
		"userId": "B1PHCzHn57d79e3f"
	},{
		"media": {
			"url":"xyz.com/asd.jpg",
			"resolution":"1920*800"
			"name":"asd.jpg",
			"lat":20.500,
			"long":60.24,
			"size":42514
		},
		"submissionId": "1",
		"submissionStatus": "ACCEPTED"
		"userId": "B1PHCzHn57d79e3f"
	}
	]
}

Or any format similar to this.any help is appreciated.thank you

2

select submissions from bucket_name where taskId=“2dd7312e-01e7-4152-b179-88514d1f2413”;

Please refer to the N1QL Language reference in order to run more N1QL queries.
http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/selectintro.html

3

Hi @alexrod,
The following query transforms the output the way you want.

SELECT taskId, ARRAY { “media”: v.media, “submissionId”:v.submissionId, “submissionStatus”:v.submissionStatus, “userId”:userId} FOR v in submissions END as submissions FROM default;

4

@isha You should first read question properly and thanx @vsr1 for reply I ll try it

5

I suspect you need a GROUP BY, if you need to combine multiple TSub docs.

6

@vsr1 You query working fine for single doc thanx but I need multiple doc and I tried group by but still getting data from only one doc. geraldss if u can helip here

7

Hi @alexrod,

See if this helps.
SELECT d.taskId, array_agg({ “media”: submissions.media, “submissionId”:submissions.submissionId, “submissionStatus”:submissions.submissionStatus, “userId”:d.userId}) submissions FROM default d UNNEST d.submissions group by d.taskId;

8

Only one object is coming in output @vsr1

[
  {
    "taskId": "fb3cc8d7-3b1b-47ad-883e-7fab6f32089d",
    "submissions": [
      {
        "userId": "B1PHCzHn57d79e3f",
        "media": {
	"url":"xyz.com/asd.jpg",
	"resolution":"1920*800"
	"name":"asd.jpg",
	"lat":20.500,
	"long":60.24,
	"size":42514
},
        "submissionId": "stf9-oi6f-kkvt7s-plt6c-iud5",
        "submissionStatus": null
      }
    ]
  }
]

where as myTSub doc has 2 submission object minimum

9

@alexrod, For each taskId, all submissions are put in array ( from multiple input documents), Do u want differently

10

No I want the same but there are 4 submission for this taskId.I mean there are 2 TSub doc and there are 2 submission object in each but query is returning me one submission object

11

Ohk @vsr1 I got it thank you for help

12

Hi @vsr1, I think you need an UNNEST followed by a GROUP BY and ARRAY_AGG.

13

Hi @geraldss,

I did that in post 7 and able to get right results in CB 4.1

14

yeah @vsr1 @geraldss I marked 7 as solution . My Query Workbench’ limit was set to 1 and i hadn’t noticed it sorry for inconvenience

1 Like