Find document by field in sub-array and return another field in same sub-array

Question

I have a collection called products, which has documents containing several fields, along with a variants sub-array.

The variants sub-arrays have several fields, including sku and id.

I know the id value, and I need to use it to get the sku value.

The simplified collection looks like this:

[
    "_id"      => "whatever_id_1",
    "field_2"  => "some value 1",
    "variants" => 
        [
            "id"  => "some_id_123"
            "sku" => "SKU-1"
        ],
        [
            "id"  => "some_id_124"
            "sku" => "SKU-2"
        ],
        [
            "id"  => "some_id_125"
            "sku" => "SKU-3"
        ]
],
[
    "_id"      => "whatever_id_2",
    "field_2"  => "some value 2",
    "variants" => 
        [
            "id"  => "some_id_126"
            "sku" => "SKU-4"
        ],
        [
            "id"  => "some_id_127"
            "sku" => "SKU-5"
        ],
        [
            "id"  => "some_id_128"
            "sku" => "SKU-6"
        ]
],
[
    "_id"      => "whatever_id_3",
    "field_2"  => "some value 3",
    "variants" => 
        [
            "id"  => "some_id_129"
            "sku" => "SKU-7"
        ],
        [
            "id"  => "some_id_130"
            "sku" => "SKU-8"
        ],
        [
            "id"  => "some_id_131"
            "sku" => "SKU-9"
        ]
]

I am retrieving the correct document with

// Set item_id
$item_id = 'some_id_127';
// Build 'find product with inventory item id' query
$find_product_with_id_query = [ 'variants' => ['$elemMatch' => ['id' => $item_id] ] ];
// Get the product document to process 
$inventory_update_product = $client_products_collection->findOne($find_product_with_id_query);

This properly returns the parent document with "_id" => "whatever_id_2".

Now, I know I can iterate over that results (eg. $inventory_update_product['variants'), and find the sku value that way.

QUESTIONS
1. But is there some way to get the sku value with MongoDB?
2. Is there any benefit to using MongoDB for this last step, or is it more efficient to just use PHP for loop to find the sku?

$item_id = some_id_127'; missing a quote there, to answer you question you can use projection and an aggregate query and probably a bit of JS. I'd have to look it up but it's possible. So the answer is -- 1. Yes, 2. Benchmark it. — ArtisticPhoenix
– ArtisticPhoenix, Commented Sep 9, 2018 at 2:11
docs.mongodb.com/manual/reference/operator/aggregation-pipeline if you look a project, basically it creates a temporary document that you can edit the schema to add a new field (your total). — ArtisticPhoenix
– ArtisticPhoenix, Commented Sep 9, 2018 at 2:16

B. Fleming · Accepted Answer · 2018-09-09 02:57:10Z

1

Yes, actually. You can use projection:

// Set item_id
$item_id = 'some_id_127';

// Build 'find product with inventory item id' query
$find_product_with_id_query = [ 'variants' => ['$elemMatch' => ['id' => $item_id] ] ];

// Project and limit options
$options = [
    'projection' => [ 'variants.$' => 1 ],
    'limit' => 1
];

// Get the product document to process 
$inventory_update_product = $client_products_collection->find($find_product_with_id_query, $options);

This will return a cursor containing the document with the array variants with only the element matching the one you searched for.

Exact syntax may vary depending on driver version and whether or not you're using the userland library.

answered Sep 9, 2018 at 2:57

B. Fleming

7,2501 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find document by field in sub-array and return another field in same sub-array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related