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