Description
I have some date (from Support's Nutshell) an array of nested objects.
When I run a query to extract distinct values from one field of those nested objects:
select array_distinct(array_star(array_star(array_star(node).results).checkers).`OS Name`)[0] as os, count(*) as os_count |
from nutshell
|
where array_length(array_distinct(array_star(array_star(array_star(node).results).checkers).`OS Name`)) > 0 |
group by array_distinct(array_star(array_star(array_star(node).results).checkers).`OS Name`)[0]; |
I get the following error:
{{[ { "code": 5040, "msg": "Duplicate Final Group." } ] }}
If I rename the "Group By" using "Let", however, the query works fine:
select os, count(*) as count
|
from nutshell
|
LET os = array_distinct(array_star(array_star(array_star(node).results).checkers).`OS Name`)[0] |
where array_length(array_distinct(array_star(array_star(array_star(node).results).checkers).`OS Name`)) > 0 |
group by os;
|
I would expect these two queries to be equivalent, but one of them fails.
FYI, the "node" subobject appears as follows:
"node": [ |
{
|
"node": "ns_1@127.0.0.1", |
"results": { |
"analysers": { |
"Open File Information": { |
"Command": [ |
"beam.smp", |
"memcached", |
"moxi", |
"==Total==" |
],
|
"File Descriptors": [ |
"74", |
"202", |
"46", |
"328" |
],
|
"Memory Maps": [ |
"40", |
"22", |
"18", |
"84" |
]
|
},
|
"Per Node Bucket Stats (CB buckets only)": { |
"Active": "-", |
"Bucket": "Total (0 buckets)", |
"Current Items": "0", |
"In-Memory KV (MB)": "0", |
"Metadata %": "-", |
"Replica": "0", |
"Resident Ratio": "-" |
}
|
},
|
"checkers": { |
"CB Packages": "ii couchbase-server 3.1.6 Couchbase Server", |
"CB Uptime": "??? (Attribute not found). Cannot check.", |
"CB Version": "3.1.6-1904-rel-enterprise - EOL Version", |
"Collection Time": "2017-07-04 02:21:08", |
"Data Directory": "/opt/couchbase/var/lib/couchbase/data - ext4 - (/dev/sdb1 1.1TB / 1% used)", |
"Data Quota": 77221, |
"Dedupe NMVB Maps": "Disabled", |
"Erlang VM vsize": 2078, |
"Installed CPUs": 40, |
"Installed RAM": 128702, |
"Interface 'bond0' MTU": "uses Jumbo Frames MTU value 9000", |
"Interface 'bond0' failures": "RX:363116 / TX:0 - Details:\n - RX packets:739,726 errors:0 dropped:363,116 overruns:0 frame:0 ", |
"Interface 'eth0' MTU": "uses Jumbo Frames MTU value 9000", |
"Interface 'eth0' failures": "RX:363103 / TX:0 - Details:\n - RX packets:362,638 errors:0 dropped:363,103 overruns:0 frame:0 ", |
"Interface 'eth1' MTU": "uses Jumbo Frames MTU value 9000", |
"Interface 'eth1' failures": "RX:0 / TX:0", |
"Interface 'eth2' Status": "down", |
"Interface 'eth3' Status": "down", |
"Interface 'vlan330' (172.20.19.66) failures": "RX:117 / TX:0 - Details:\n - RX packets:298,533 errors:0 dropped:117 overruns:0 frame:0 ", |
"Interface 'vlan330' MTU": "uses Jumbo Frames MTU value 9000", |
"Memcached Fragmentation": "??? (Attribute not found). Cannot check.", |
"Memcached vsize": 395, |
"NUMA Configuration": "2 nodes - Couchbase processes have default NUMA policy", |
"OS Name": "Linux 3.13.0-32-generic", |
"OS Version": "Ubuntu 12.04.5 LTS", |
"Swap Used": { |
"total": 28762108, |
"used": 0 |
},
|
"System Uptime": "23 min", |
"Total Server Quota": 77221, |
"Transparent Huge Pages": "always - Recommended is 'never' or 'madvise'", |
"Used RAM": { |
"total": 128701, |
"used": 1610 |
},
|
"analyse_connections": "Failed to run analyser", |
"check_non_default_max_connections": "Failed to run checker", |
"vm.swappiness": "0 (Recommended value)" |
}
|
}
|
}
|
],
|
|