Is it possible to group on a field and create an array with that field as _id?

Working with Data
1

I have data in an aggregation pipeline stage in the following format:

[
	{
		"_id": "62702be5d87ed16cea3b2315",
		"G": 3,
		"N": [
			1295748572
		],
		"t": "020682390"
	},
	{
		"_id": "62702be5d87ed16cea3b2316",
		"G": 3,
		"N": [
			1609988849
		],
		"t": "200766679"
	},
	{
		"_id": "62702be5d87ed16cea3b2317",
		"G": 3,
		"N": [
			1083965362
		],
		"t": "105764876"
	},
	{
		"_id": "62702be5d87ed16cea3b2318",
		"G": 3,
		"N": [
			1063897809,
			1144556531,
			1316227135
		],
		"t": "200719145"
	}
]

I want to group on “G” and use it as an “id” field, with the rest of the data in the array.

My desired result would be:

{
	"G": 3,
	"N": [
		1295748572
	],
	"t": "020682390",
	"N": [
		1609988849
	],
	"t": "200766679",
	"N": [
		1083965362
	],
	"t": "105764876",
	"N": [
		1063897809,
		1144556531,
		1316227135
	],
	"t": "200719145"
}

The raw data is coming from SQL Server and I have control over everything but the final output format. There are hundreds of groups (“G”) with members, and each member can have multiple “N” values and one “t” value.

Any help or directions is greatly appreciated. I am very experienced in SQL, but still learning NoSQL.

2

Your expect result cannot really achieve.

In javascript if I try to create a document with your expected result, I get:

mongosh > expected_result = {
	"G": 3,
	"N": [
		1295748572
	],
	"t": "020682390",
	"N": [
		1609988849
	],
	"t": "200766679",
	"N": [
		1083965362
	],
	"t": "105764876",
	"N": [
		1063897809,
		1144556531,
		1316227135
	],
	"t": "200719145"
}
< { G: 3, N: [ 1063897809, 1144556531, 1316227135 ], t: '200719145' }
mongosh > expected_result
< { G: 3, N: [ 1063897809, 1144556531, 1316227135 ], t: '200719145' }

In most of the JSON implementation, you lose all but the last occurrence of a key:value. The first few t’s get overwritten by the last one.

What can be done is to have an array of those repeated keys. For example, the following would be possible:

{
  "G": 3 ,
  "data" : [
    {
      "N": [
		1295748572
      ],
      "t": "020682390"
    } ,
    {
      "N": [
		1609988849
      ],
      "t": "200766679",
    } ,
    {
      "N": [
		1083965362
      ],
      "t": "105764876"
    } ,
    {
      "N": [
		1063897809,
		1144556531,
		1316227135
      ],
      "t": "200719145"
    }
  ]
}
1 Like
3

Thanks! I was afraid that was where I would end up.
I appreciate having someone more knowledgeable keep me from wasting any more time.

1 Like