postgresql数组操作

1. 遇到一个麻烦的事情，展示详情的时候，有个字段是varchar类型，但是存储的是数组字符串。

类似‘["09","03","11","05","04","01"]’。

需要通过查询字符串的数据，获得相应的中文展示。

如: '['九','三','十一','五','四','一']'

 

2. 查询postgresql文档后，找到一个非常方便的方法，可以将字符串数组切换成table

select * from json_array_elements_text('["09","03","11","05","04","01"]'::json)

　　

 

3. 可是换到服务器上发现，服务器数据库版本过低不支持json_array_elements_text方法

 

4. 怎么办，想到了升级数据库，这个方法不行，毕竟是在服务器。又想到添加json_array_elements_text方法到现有数据库，

   虽然找到文档，但是操作起来非常麻烦。后来查到一篇文章，可以将数据转换成逗号分割的字符串

SELECT
	string_agg(TRIM(elem :: TEXT, '"'), ',')
FROM
	json_array_elements(
		'["09","03","11","05","04","01"]' :: json
	) AS elem

　　执行后的结果： 09,03,11,05,04,01

 

5. 😄，好熟悉的格式，这个结果可以通过函数操作转换成table

SELECT
regexp_split_to_table(
	(
		SELECT
			string_agg(TRIM(elem :: TEXT, '"'), ',')
		FROM
			json_array_elements('["09","03","11","05","04","01"]'::json) AS elem
	),
	E'\,'
);

　　输出结果：

 

6. 于是乎一切迎刃而解。

最终sql代码：

SELECT
	"service"."province_id",
	(
		SELECT
			ARRAY_AGG(province)
		FROM
			test_province
		WHERE
			province_id IN(
				SELECT
					regexp_split_to_table(
						(
							SELECT
								string_agg(TRIM(elem :: TEXT, '"'), ',')
							FROM
								json_array_elements("service"."province_id" :: json) AS elem
						),
						E'\,'
					)
			)
	) AS "province_name"
FROM
	"test_service" AS "service"
WHERE
	"service"."service_id" = 'test';

　　结果：

 

参考： https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array