如何将非起始索引为1的text[]数组转换为保留索引的JSON？

碰到PostgreSQL里起始索引不为1的text数组转JSON的需求时，自带的array_to_json()函数会直接丢失原数组的索引信息，比如你有这样的数组：

select myarr from tbl;

返回结果：

myarr
-------------------
[5:5]={blablabla}

用原生函数转换后：

select array_to_json(myarr) from tbl;

得到的结果是：

array_to_json
---------------
["blablabla"]

但我们需要保留索引对应的位置，也就是在前面补全null，得到[null, null, null, null, "blablabla"]这种格式。

自定义函数解决方案 #

我封装了一个array_to_json_with_null函数来搞定这个问题，代码如下：

CREATE OR REPLACE FUNCTION array_to_json_with_null(myarr text[]) RETURNS json AS $func$
BEGIN
if myarr is null or cardinality(myarr) = 0 then
return null;
else
return array_to_json (array_fill(null::text, ARRAY[array_lower(myarr, 1) - 1], ARRAY[1]) || myarr);
end if;
END $func$ LANGUAGE plpgsql;

函数逻辑说明 #

• 先做边界判断：如果输入数组是null或者空数组，直接返回null
• 如果数组有效，用array_fill生成一个从索引1开始、长度为array_lower(myarr,1)-1的null数组，补全原数组起始索引前的缺失位置
• 把生成的null数组和原数组拼接，再用array_to_json转换，这样就完整保留了原索引对应的位置信息

使用示例 #

调用这个自定义函数：

select array_to_json_with_null(myarr) from tbl;

就能得到你想要的结果：

array_to_json_with_null
------------------------
[null, null, null, null, "blablabla"]

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