CODEWITHSUNDEEP
sqljsonoracle-databaseplsqloracle12c
Swapnil Boralkar
Swapnil Boralkar

Reputation: 308

PL SQL comma delimited to JSON array conversion

I have values in one column with delimit as comma.

Col1

a,b,c,d

I want to convert this into JSON Array. I know JSON_ARRAY function is available in PL/SQL from 12.2 onwards. But JSON_ARRAY converts multiple columns to the array. I have values in the single column.

output: ["a","b","c","d"]

Upvotes: 0

Views: 1322

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use JSON_ARRAYAGG() instead of JSON_ARRAY() function without using PL/SQL, after converting those letters into row-wise style through splitting by commas such as

WITH t(id,col1) AS
(
 SELECT 1,'a,b,c,d' FROM dual UNION ALL
 SELECT 2,'d,e,f,g,h,i' FROM dual
), t2 AS
(
 SELECT REGEXP_SUBSTR(col1,'[^,]+',1,level) AS col, id
   FROM t
CONNECT BY level <= REGEXP_COUNT(col1,',')+1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR col1 = col1
)    
SELECT id, JSON_ARRAYAGG(col ORDER BY col RETURNING VARCHAR2(100)) As "JSON value"
  FROM t2
 GROUP BY id

Demo

Upvotes: 2

MT0
MT0

Reputation: 167982

Just use replace:

SELECT '["'
       ||REPLACE(REPLACE(col1,'"','\"'),',','","')
       ||'"]' AS json_value
FROM   table_name;

Or, in PL/SQL:

DECLARE
  col1 VARCHAR2(50) := 'a,b,c,d';
  json VARCHAR2(50);
BEGIN
 json := '["'||REPLACE(REPLACE(col1,'"','\"'),',','","')||'"]';
 DBMS_OUTPUT.PUT_LINE(json);
END;
/

db<>fiddle here

Upvotes: 0

Related Questions