N1QL Query query for getting distinct and its count

SQL++
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.

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

3

Expected out put:

distinct value of “data”: data, data1, data2
total count of data : 2 ,2 ,1
assignment: 2 0 0

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;
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”
}
}
}

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