SELECT
row_to_json(t) AS json_record -- 将整个结果集输出成json格式
FROM (
SELECT
countryName,
feedbackOrderNumber,
vin,
COALESCE(
(
SELECT jsonb_agg( -- 格式化json数组
jsonb_build_object(
'feedBackNo', feedbackOrderNumber,
'fileUrl', trim(t.url_piece),
'fileType', trim(t.type_piece),
'fileName', trim(t.name_piece)
)
)
FROM unnest( -- 根据,分隔数据
string_to_array(trim(fileUrl), ','),
string_to_array(trim(fileType), ','),
string_to_array(trim(fileName), ',')
) WITH ORDINALITY AS t(url_piece, type_piece, name_piece,rn)
),
'[]'::jsonb
) AS fileList
FROM ???
WHERE id IN (${this_id})
) AS t
使用数据同步,用sql将数据处理成json格式,然后直接接口推送