Pinal
July 20, 2019, 8:23am
1
Hi,
How to write n1ql query on bellow data to fetch distinct “data” column with its values total count eg. value of “no” 1 and 2 of data = data, so data count is 2 and total assignments array filled.
“Map”: {
“1”: {
“assignments”: [
“1”
],
“temp1”: “value”,
“data”: “data”,
"No: “1”
},
“2”: {
“assignments”: [
1
],
“temp1”: “value”,
“data”: “data”,
“No”: “2”
},
“3”: {
“assignments”: [ ],
“temp1”: “value”,
“data”: “data1”,
“No”: “3”
},
“4”: {
“assignments”: [ ],
“temp1”: “value”,
“data”: “data1”,
“No”: “4”
},
“5”: {
“assignments”: [ *],
“temp1”: “value”,
“data”: “data2”,
“No”: “5”
}
}
Thank you
Pinal.
vsr1
July 20, 2019, 6:41pm
2
Do you need within the document or across documents. The count across the documents and document value is not possible due to aggregate semantics.
The problem is not clear. Please post the expected output
Pinal
July 20, 2019, 8:12pm
3
Expected out put:
distinct value of “data”: data, data1, data2
total count of data : 2 ,2 ,1
assignment: 2 0 0
vsr1
July 20, 2019, 8:23pm
4
SELECT df.data,
COUNT(1) AS cnt,
SUM(ARRAY_COUNT(df.assignments)) AS assignments
FROM default AS d
UNNEST OBJECT_VALUES(d.Map) AS df
WHERE ...
GROUP BY df.data;
Pinal
July 20, 2019, 8:47pm
5
Pinal:
“1”: {
“assignments”: [
“1”
],
“temp1”: “value”,
“data”: “data”,
"No: “1”
},
“2”: {
“assignments”: [
1
],
“temp1”: “value”,
“data”: “data”,
“No”: “2”
},
“3”: {
“assignments”: ,
“temp1”: “value”,
“data”: “data1”,
“No”: “3”
},
“4”: {
“assignments”: ,
“temp1”: “value”,
“data”: “data1”,
“No”: “4”
},
“5”: {
“assignments”: [ *],
“temp1”: “value”,
“data”: “data2”,
“No”: “5”
}
Its not working
bellow is entered data plz suggest
{
“__version”: “1.0”,
“__frwg”: “2343243424”,
“__fsdfssff”: “ggsdgsgg”,
“__id”: “dsfsdfsdfs”,
“__type”: “tablename”,
“__lastModified”: 432424242422,
“Map”: {
“1”: {
“assignments”: [
1
],
“temp1”: “value”,
“data”: “data”,
"No: “1”
},
“2”: {
“assignments”: [
1
],
“temp1”: “value”,
“data”: “data”,
“No”: “2”
},
“3”: {
“assignments”: ,
“temp1”: “value”,
“data”: “data1”,
“No”: “3”
},
“4”: {
“assignments”: ,
“temp1”: “value”,
“data”: “data1”,
“No”: “4”
},
“5”: {
“assignments”: [ *],
“temp1”: “value”,
“data”: “data2”,
“No”: “5”
}
}
}
vsr1
July 20, 2019, 10:57pm
6
INSERT INTO default VALUES("k01",{ "__version": "1.0", "__frwg": "2343243424", "__fsdfssff": "ggsdgsgg", "__id": "dsfsdfsdfs", "__type": "tablename", "__lastModified": 432424242422, "Map": { "1": { "assignments": [ 1 ], "temp1": "value", "data": "data", "No": "1" }, "2": { "assignments": [ 1 ], "temp1": "value", "data": "data", "No": "2" }, "3": { "assignments": [], "temp1": "value", "data": "data1", "No": "3" }, "4": { "assignments": [], "temp1": "value", "data": "data1", "No": "4" }, "5": { "assignments": [], "temp1": "value", "data": "data2", "No": "5" } } }) ;
SELECT df.data,
COUNT(1) AS cnt,
SUM(ARRAY_COUNT(df.assignments)) AS assignments
FROM default AS d
UNNEST OBJECT_VALUES(d.`Map`) AS df
WHERE d.`__type` = "tablename"
GROUP BY df.data;
"results": [
{
"assignments": 0,
"cnt": 1,
"data": "data2"
},
{
"assignments": 2,
"cnt": 2,
"data": "data"
},
{
"assignments": 0,
"cnt": 2,
"data": "data1"
}
]
1 Like