Postgres如何将json数组解压到列中，然后重新打包，但保留数据类型？

-- 创建一个包含JSON数组的表
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  json_array JSON
);

-- 插入示例数据
INSERT INTO my_table (json_array) VALUES
  ('[1, 2, 3]'),
  ('["foo", "bar", "baz"]'),
  ('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]');

-- 使用json_array_elements函数解压JSON数组到列中
SELECT id, json_array_elements(json_array) AS value
FROM my_table;

-- 输出结果：
-- id | value
-- ---+-------
-- 1  | 1
-- 1  | 2
-- 1  | 3
-- 2  | "foo"
-- 2  | "bar"
-- 2  | "baz"
-- 3  | {"name": "John", "age": 30}
-- 3  | {"name": "Jane", "age": 25}

-- 创建新的列来保存解压后的值
ALTER TABLE my_table ADD COLUMN value_type TEXT;
ALTER TABLE my_table ADD COLUMN value_int INTEGER;
ALTER TABLE my_table ADD COLUMN value_text TEXT;
ALTER TABLE my_table ADD COLUMN value_json JSON;

-- 更新新列的值
UPDATE my_table SET
  value_type = json_typeof(value),
  value_int = (value::text::integer),
  value_text = (value::text),
  value_json = (value::json)
FROM (
  SELECT id, json_array_elements(json_array) AS value
  FROM my_table
) AS subquery
WHERE my_table.id = subquery.id;

-- 打包数据并保留数据类型
SELECT id, json_agg(json_build_object(
  'value_type', value_type,
  'value_int', value_int,
  'value_text', value_text,
  'value_json', value_json
)) AS json_array
FROM my_table
GROUP BY id;

-- 输出结果：
-- id | json_array
-- ---+--------------------------------------------------
-- 1  | [{"value_type": "number", "value_int": 1, "value_text": "1", "value_json": 1},
--     |  {"value_type": "number", "value_int": 2, "value_text": "2", "value_json": 2},
--     |  {"value_type": "number", "value_int": 3, "value_text": "3", "value_json": 3}]
-- 2  | [{"value_type": "string", "value_int": null, "value_text": "foo", "value_json": "foo"},
--     |  {"value_type": "string", "value_int": null, "value_text": "bar", "value_json": "bar"},
--     |  {"value_type": "string", "value_int": null, "value_text": "baz", "value_json": "baz"}]
-- 3  | [{"value_type": "object", "value_int": null, "value_text": "{\"name\": \"John\", \"age\": 30}", "value_json": {"name": "John", "age": 30}},
--     |  {"value_type": "object", "value_int": null, "value_text": "{\"name\": \"Jane\", \"age\": 25}", "value_json": {"name": "Jane", "age": 25}}]