How to concatenate json array values via "|"?-CodePudding

I have colunn with jsonb format. Columns name is "column1" and table is "table1"

{
.....
"source_types": {
  "source_type_os": [ ,

      1,3,5
    ],
   ....
  },
  ....
}

I can take string of array like "[1,3,5]" but i need to get something like 1|3|5

I have tryed this code

select ....
       t1.column1::jsonb #>>'{source_types,source_type_os}' as "test", <--here the problem
       ....
from table1 t1
where ....;

CodePudding user response：

select ....
       string_agg(c.col, '|') as "test"
  from table1 as t1
 cross join lateral jsonb_array_elements_text(t1.column1::jsonb #>>'{source_types,source_type_os}') as c(col)
 group by t1

CodePudding user response：

select ....
       replace(trim(both '[]' from t1.column1::jsonb #>>'{source_types,source_type_os}'),',','|') as "test",
       ....
from table1 t1
where ....;