Question 376311 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Query child documents based off field that is only present in parent?

In my MongoDB collection I have a documents that follow a parent-child structure.

Each parent doc typically has 4 fields, with the children having 3 (no group field).

parent:

{
_id: doc_123
parent_id: 123
active: true
group: A
}

children

{
id: doc_123_1
parent_id: 123
active: true
}

{
id: doc_123_2
parent_id: 123
active: true
}

I want to write a BSON query / aggregation if needed for my Java Spring project that will return all the docs that match the following fields provided by user:

  • active field – this will be true or false
  • group field – e.g "A"

My difficulty is that each child document is assumed to have the same value as the parent for the group field, but it is not actually in the document.

How can I write a query that will match all the parent and child documents for a certain group?

All documents are in the one collection, there are no separate collections for parent and child docs.

Tags:

2

Answers


  1. 0

    Aggregation steps:

    1. Search by the group and active true/false based on the user-provided values.
      • note that only parent records have the group, it’s missing/null for children
      • so this stage returns only the matching parents
    2. Use the parent_id to self-lookup into the same collection and match on parent_id
      • For whatever reason, the parent_id is identical for children and the parent.
      • Relying on the id pattern for documents to identify parents & children is a really bad idea: doc_123 for parents and doc_123_1, doc_123_2 for children will be very non-performant for actual search/match/lookups.
      • Is the id in children called id or _id? But it doesn’t affect the pipeline here.
    3. Exclude the parent record if you want only child records. See the comment for that stage.
    db.collection.aggregate([
  {
    $match: {
      // set this to a variable for true/false search
      active: true,
      // set this to a variable for group search
      // only "parents" have the group
      group: "A"
    }
  },
  {
    // self-lookup into the same collection, matching on parent_id
    $lookup: {
      from: "collection",
      localField: "parent_id",
      foreignField: "parent_id",
      as: "children"
    }
  },
  { $unwind: "$children" },
  { $replaceWith: "$children" },
  {
    // exclude the parents
    // IF YOU WANT PARENTS ALSO THEN REMOVE THIS STAGE
    $match: {
      group: { $exists: false }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Join to parents adding a condition of group on the join:

    db.collection.aggregate([
  {
    // self-join on child.parent_id -> parent.parent_id
    $lookup: {
      from: "collection",
      localField: "parent_id",
      foreignField: "parent_id",
      "pipeline": [
        {
          "$match": {
            "group": "A" // "A" is from the client
            
          }
        }
      ],
      as: "parent"
    }
  },
  {
    $match: {
      active: true, // "true" is from the client
      group: {
        $exists: false // exclude parents which join to themselves
      }
    }
  }
])

    See live demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search