PostgreSQL：如何将单行数据转换为name-value格式的JSON数组

嘿，这个需求我之前在项目里碰过好多次，PostgreSQL的JSON函数家族完全能搞定，给你几种实用的方案，按需选就行：

方案1：手动构造（适合字段少的场景） #

如果你的字段不多，直接手动把每个字段名和对应值拆成行，再聚合生成JSON数组，可读性拉满：

SELECT
  array_agg(
    json_build_object(
      'name', col_name,
      'value', col_value
    )
  ) AS target_json_array
FROM (
  -- 这里把需要转换的字段名和对应值一一列出来
  VALUES
    ('Col1', your_table.Col1),
    ('Col2', your_table.Col2)
  ) AS temp(col_name, col_value)
WHERE your_table.id = 1; -- 替换成你定位目标行的条件

运行后就能得到你想要的[ {"name": "Col1", "value": "Column1Value"}, {"name": "Col2", "value": "Column2Value"} ]格式。

方案2：用JSONB自动拆分（通用无扩展） #

要是字段多，不想一个个写，用JSONB的内置函数自动把行转成键值对再处理，不需要装额外扩展：

SELECT
  jsonb_agg(
    jsonb_build_object('name', key, 'value', value)
  ) AS target_json_array
FROM
  jsonb_each_text(to_jsonb(your_table)) AS temp(key, value)
WHERE your_table.id = 1;

小技巧：只转换特定字段 #

如果不想把整行所有字段都转，只挑Col1和Col2的话，可以这么写：

SELECT
  jsonb_agg(
    jsonb_build_object('name', key, 'value', value)
  ) AS target_json_array
FROM
  jsonb_each_text(
    to_jsonb((SELECT t FROM (SELECT Col1, Col2) t))
  ) AS temp(key, value);

方案3：借助HStore扩展（适合习惯键值对操作的场景） #

如果你的数据库装了HStore扩展，也可以用它来快速拆分字段：

-- 先确保HStore扩展已安装（第一次用需要运行）
CREATE EXTENSION IF NOT EXISTS hstore;

SELECT
  json_agg(
    json_build_object('name', key, 'value', value)
  ) AS target_json_array
FROM
  hstore_to_array(hstore(your_table)) AS temp(key, value)
WHERE your_table.id = 1;

举个实际例子，假设你有个表user_info，其中一行id=1的Col1='张三'，Col2='25'，用方案2的SQL运行后，结果就是：

[{"name": "Col1", "value": "张三"}, {"name": "Col2", "value": "25"}]

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