How to extract values from a JSON field with Mongodb?

Question

When I run mongdob query I got this json results in a column. Column name is "user"

{"email": "[email protected]"}
{"email": "[email protected]", "name": "name1"}
{"email": "[email protected]"}
{"email": "[email protected]", "name": "mail2"}
{"email": "[email protected]"}
{"email": "[email protected]", "name": "name3"}
{"email": "[email protected]"}
{"email": "[email protected]", "name": "name4"}

However I need to extract email and name from this JSON into different columns like "email", "name"

How can I do this with Mongodb?

Output

enter image description here

Answer 1

Have you tried with aggregate yet?

Following I used $addField and $project

var c = db.getCollection("json_column")
if (c == null) {
 db.createCollection('json_column');
}
db.getCollection("json_column").remove({});
db.getCollection('json_column').insert({user:{"email": "[email protected]", "name": "name1"}});
db.getCollection('json_column').insert({user:{"email": "[email protected]"}});
db.getCollection('json_column').insert({user:{"email": "[email protected]", "name": "mail2"}});
db.getCollection('json_column').insert({user:{"email": "[email protected]"}});
db.getCollection('json_column').insert({user:{"email": "[email protected]", "name": "name3"}});
db.getCollection('json_column').insert({user:{"email": "[email protected]", "name": "name4"}});
db.getCollection('json_column').aggregate([
{
 $addFields: {
 "email": "$user.email",
 "name": "$user.name"
 }
},
{
 $project: {
 "user":0
 }
}
]);

enter image description here

Answer 2

Using MongoDB 7.0.1 and Mongosh 2.0.0. The input JSON example document:

let doc = {
 "user": [
 {"email": "[email protected]"},
 {"email": "[email protected]", "name": "name 1"},
 {"email": "[email protected]"},
 {"email": "[email protected]", "name": "name 2"},
 {"email": "[email protected]"}
 ]
}

Insert into a collection:

db.collection.insertOne(doc)

Query the collection to get the desired result:

db.collection.aggregate([
 {
 $unwind: "$user"
 },
 {
 $project: {
 _id: 0,
 email: "$user.email",
 name: "$user.name",
 }
 }
])

This outputs:

[
 { email: '[email protected]' },
 { email: '[email protected]', name: 'name 1' },
 { email: '[email protected]' },
 { email: '[email protected]', name: 'name 2' },
 { email: '[email protected]' }
]