How do you get rid of the back slashes, but still keep the double quotes when using json_array?
TABLE A:
Hospital Cat Dog Lizard Mouse Hosp1 D M Hosp2 C D L Hosp3 C D L M
Wrong code:
select json_array (regexp_replace(
case when Cat = 'C' then '"cat",' end) ||
case when Dog = 'D' then '"dog",' end) ||
case when Lizard = 'L' then '"lizard",' end) ||
case when Mouse = 'M' then '"mouse",' end,
'",$','')
) pets from A where Hospital = Hosp2;
Wrong output:
PETS [""cat","dog","lizard"]
Desired output:
PETS ["cat","dog","lizard"]
And yes, I know you can just concatenate like so "select '['|| etc. without using the json_array, but I need to have that in the code. Is there a way to use regexp_replace somehow, to get rid of that ?? Thanks!
Advertisement
Answer
No need for concatenation:
select json_array (
case when Cat = 'C' then 'cat' end,
case when Dog = 'D' then 'dog' end,
case when Lizard = 'L' then 'lizard' end,
case when Mouse = 'M' then 'mouse' end) as pets
from A
where Hospital = 'Hosp2';