PostgreSQL：如何将jsonb类型attributes列值转换为数组？

Hey there! To give you the most accurate solution, I’ll need you to share the exact original JSON structure stored in your attributes column and the target structure you want to convert it to. That said, I’ll walk through some common conversion scenarios below—maybe one of these matches your use case!

Scenario 1: Flatten Nested JSON #

Suppose your original attributes looks like this:

{
  "user": {
    "name": "Alice",
    "age": 30
  },
  "address": {
    "city": "New York"
  }
}

And you want to flatten it to:

{
  "user_name": "Alice",
  "user_age": 30,
  "address_city": "New York"
}

You can use jsonb_build_object to manually extract and restructure the fields:

UPDATE your_table
SET attributes = jsonb_build_object(
  'user_name', attributes->'user'->>'name',
  'user_age', (attributes->'user'->>'age')::integer,
  'address_city', attributes->'address'->>'city'
)
WHERE attributes ? 'user'; -- Only update rows that have the 'user' key

Scenario 2: Convert Flat JSON to Nested Structure #

If your original data is flat:

{
  "product_id": 123,
  "product_name": "Laptop",
  "brand_name": "Dell"
}

And you want to nest it into:

{
  "product": {
    "id": 123,
    "name": "Laptop"
  },
  "brand": {
    "name": "Dell"
  }
}

Use nested jsonb_build_object calls:

UPDATE your_table
SET attributes = jsonb_build_object(
  'product', jsonb_build_object(
    'id', (attributes->>'product_id')::integer,
    'name', attributes->>'product_name'
  ),
  'brand', jsonb_build_object(
    'name', attributes->>'brand_name'
  )
)
WHERE attributes ? 'product_id';

Scenario 3: Transform JSON Arrays #

Let’s say your original array structure is:

{
  "tags": ["tech", "electronics"]
}

And you need to convert each array element into an object:

{
  "tag_list": [
    {"name": "tech"},
    {"name": "electronics"}
  ]
}

Use a CTE with jsonb_array_elements and jsonb_agg to restructure the array:

WITH transformed_tags AS (
  SELECT 
    id,
    jsonb_agg(jsonb_build_object('name', tag::text)) AS new_tag_list
  FROM your_table,
       jsonb_array_elements(attributes->'tags') AS tag
  WHERE attributes ? 'tags'
  GROUP BY id
)
UPDATE your_table t
SET attributes = (attributes - 'tags') || jsonb_build_object('tag_list', tt.new_tag_list)
FROM transformed_tags tt
WHERE t.id = tt.id;

If your conversion need doesn’t fit any of these cases, please share specific examples of your original and target JSON structures—I’ll help you craft the perfect SQL query!

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