如何在PostgreSQL JSONB中实现Mongo的unwind操作（扁平化嵌套数组）

Hey there! I totally get your frustration—switching from MongoDB's trusty $unwind to PostgreSQL's JSONB handling can feel like navigating a new toolset, especially when nested arrays are involved. Let's walk through exactly how to replicate that flattening behavior in Postgres, step by step.

First, let's ground this in a concrete example (since you mentioned working with product data):

Example Scenario #

Say you have a MongoDB document like this:

{
  "_id": 1,
  "product_name": "Wireless Headphones",
  "tags": ["audio", "portable"],
  "sku_variants": [
    {"sku": "WH-001", "color": "black", "price": 99.99},
    {"sku": "WH-002", "color": "white", "price": 109.99}
  ]
}

In MongoDB, you'd use $unwind to flatten the sku_variants array:

db.products.aggregate([{$unwind: "$sku_variants"}])

Which gives you two flattened documents, one per variant:

{"_id":1, "product_name":"Wireless Headphones", "tags":["audio","portable"], "sku_variants":{"sku":"WH-001", "color":"black", "price":99.99}}
{"_id":1, "product_name":"Wireless Headphones", "tags":["audio","portable"], "sku_variants":{"sku":"WH-002", "color":"white", "price":109.99}}

Replicating $unwind in PostgreSQL #

PostgreSQL uses jsonb_array_elements (or json_array_elements for non-binary JSON) alongside LATERAL JOIN to achieve this flattening. Let's assume you have a Postgres table products with a data column of type jsonb holding your product data.

1. Flattening Top-Level Arrays

To replicate the basic $unwind on sku_variants, run this query:

SELECT
  p.data->>'_id' AS product_id,
  p.data->>'product_name' AS product_name,
  p.data->'tags' AS product_tags,
  variant AS sku_variant
FROM
  products p,
  jsonb_array_elements(p.data->'sku_variants') AS variant;

This will split each array element into its own row, just like MongoDB's $unwind.

2. Handling Nested Arrays

If you have deeper nested arrays (e.g., each sku_variant has a features array), you can nest the jsonb_array_elements calls. Let's extend our example:

{
  "_id": 2,
  "product_name": "Smart Speaker",
  "tags": ["smart home", "audio"],
  "sku_variants": [
    {"sku": "SS-001", "color": "gray", "price": 79.99, "features": ["voice control", "bluetooth"]},
    {"sku": "SS-002", "color": "black", "price": 89.99, "features": ["voice control", "wifi", "multi-room"]}
  ]
}

To flatten both sku_variants and their nested features, use this query:

SELECT
  p.data->>'_id' AS product_id,
  p.data->>'product_name' AS product_name,
  variant->>'sku' AS sku,
  variant->>'color' AS color,
  feature AS product_feature
FROM
  products p,
  jsonb_array_elements(p.data->'sku_variants') AS variant,
  jsonb_array_elements(variant->'features') AS feature;

This will generate a row for every combination of variant and its features—perfect for fully flattening nested structures.

3. Adding Filters (Like $match After $unwind)

If you want to filter the flattened rows (similar to adding a $match stage in MongoDB), just add a WHERE clause:

SELECT
  p.data->>'_id' AS product_id,
  variant AS sku_variant
FROM
  products p,
  jsonb_array_elements(p.data->'sku_variants') AS variant
WHERE variant->>'price' > '80';

This will only return variants priced over $80.

内容的提问来源于stack exchange，提问作者Bernhardt Scherer