How can I achieve mongo "unwind" in postgres JSONB? (Flatten nested arrays)

Question

I recently looked into migrating our product database from mongo to postgres. Coming from mongoDb, I am used of "unwinding" objects and arrays.

Suppose you have the following object:

{
  "styleGroupId": "2",
  "brand": "MOP",
  "colorVariants": [
    {
        "color": "red",
        "colorCode": "222",
        "sizeVariants": [
           {"gtin": "444",
           "size": "M"},
           {"gtin": "555",
           "size": "L"}
       ]
    },
    {
        "color": "blue",
        "colorCode": "111",
        "sizeVariants": [
           {"gtin": "66",
           "size": "M"},
           {"gtin": "77",
           "size": "L"}
       ]
    }
    ]
}

If you want to flatten it, in mongo you use the following:

db.test.aggregate([
{
    $unwind: "$colorVariants"
},
{
    $unwind: "$colorVariants.sizeVariants"
}
])

which will result in objects like this:

{
    "_id" : ObjectId("5a7dc59dafc86d25964b873c"),
    "styleGroupId" : "2",
    "brand" : "MOP",
    "colorVariants" : {
        "color" : "red",
        "colorCode" : "222",
        "sizeVariants" : {
            "gtin" : "444",
            "size" : "M"
        }
    }
}

I have spent hours searching for "mongo unwind in postgres" but could hardly find a satisfying answer. Also a lot of the resources on querying JSONB data in postgres barely touch nested arrays. Hopefully this post will help other poor souls searching for a migration from mongoDb to postgres.

Answer 1

The function:

create or replace function jsonb_unwind(target jsonb, path text[])
returns jsonb language plpgsql as $$
begin
    if jsonb_typeof(target #> path) = 'array' then
        return jsonb_set(target, path, target #> path -> 0);
    else
        return target;
    end if;
end $$;

Example usage:

select 
    jsonb_unwind(
        jsonb_unwind(json_data, '{colorVariants}'), 
        '{colorVariants, sizeVariants}')
from my_table;

Test it in rextester.