Eddie Shipman
asked on
Converting query data to JSON
Have data from a cursor like this:
RID KEYWORD VALUE
208649 KW_AA VAL_AA
208649 KW_AB VAL_AB
208649 KW_AC VAL_AC
217427 KW_BA VAL_BA
217427 KW_BB VAL_BB
217427 KW_BC VAL_BC
217427 KW_BD VAL_BD
223331 KW_CA VAL_CA
223331 KW_CB VAL_CB
223331 KW_CC VAL_CC
223331 KW_CD VAL_CD
223331 KW_CE VAL_CE
223331 KW_CF VAL_CF
227717 KW_DA VAL_DA
227717 KW_DB VAL_DB
227717 KW_DC VAL_DC
227717 KW_DD VAL_DD
Would like to know how to create a loop in PL/SQL to produce VARCHAR2 (JSON) output like this:[
{"RID": 208649, "KW_AA": "VAL_AA", "KW_AB": "VAL_AA", "KW_AC": "VAL_AC"},
{"RID": 217427, "KW_BA": "VAL_BA", "KW_BB": "VAL_BB", "KW_BC": "VAL_BC", "KW_BD": "VAL_BD"},
{"RID": 223331, "KW_CA": "VAL_CA", "KW_CB": "VAL_CB", "KW_CC": "VAL_CC", "KW_CD": "VAL_CD", "KW_CE": "VAL_CE", "KW_CF": "VAL_CF"},
{"RID": 227717, "KW_DA": "VAL_DA", "KW_DB": "VAL_DB", "KW_DC": "VAL_DC", "KW_DD": "VAL_DD"}
]
or with pl/sql
DECLARE
v_json VARCHAR2(32767);
BEGIN
FOR x
IN ( SELECT '{"RID": '
|| rid
|| ', '
|| LISTAGG('"' || keyword || '": "' || VALUE || '"', ', ')
WITHIN GROUP (ORDER BY keyword)
|| '},'
json
FROM yourdata
GROUP BY rid)
LOOP
v_json := v_json || CHR(10) || x.json;
END LOOP;
v_json := '[' || RTRIM(v_json, ',') || CHR(10) || ']';
DBMS_OUTPUT.put_line(v_jso n);
END;
DECLARE
v_json VARCHAR2(32767);
BEGIN
FOR x
IN ( SELECT '{"RID": '
|| rid
|| ', '
|| LISTAGG('"' || keyword || '": "' || VALUE || '"', ', ')
WITHIN GROUP (ORDER BY keyword)
|| '},'
json
FROM yourdata
GROUP BY rid)
LOOP
v_json := v_json || CHR(10) || x.json;
END LOOP;
v_json := '[' || RTRIM(v_json, ',') || CHR(10) || ']';
DBMS_OUTPUT.put_line(v_jso
END;
ASKER
The
Doesn't look like I can use a cursor for input to this, either
Also, I'm using 10g so LISTAGG is not avail.
WITHIN GROUP (ORDER BY keyword)
is causing an issue... Getting this error:ORA-06550: line 14, column 36:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 10, column 12:
PL/SQL: SQL Statement ignored
and it is highlighting the GROUP keyword.Doesn't look like I can use a cursor for input to this, either
Also, I'm using 10g so LISTAGG is not avail.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found the xmlagg stuff earlier and it works perfectly.
Thanks.
Thanks.
with cte as (select fd.fd_rid, fkp.fkp_keyword, fkp.fkp_value from filedirectory fd, filekeypair fkp where fkp.fkp_fd_rid = fd.fd_rid and fd.fd_rid in
(select fkp_fd_rid from filekeypair where fkp_keyword = 'CLI_RID' and fkp_value = '1006195'))
select fd_rid,
chr(123) || rtrim (xmlagg (xmlelement (fkp_keyword, CHR(34) || fkp_keyword || CHR(34) || ':' || CHR(34) || replace(fkp_value, '\', '\\') || CHR(34) || ', ')).extract ('//text()'), ', ') || chr(125) json
from cte
group by fd_rid
ASKER
great
as you iterate through, add the commas and carriage returns as needed
SELECT json
FROM (SELECT 1 n, '{' json FROM DUAL
UNION ALL
SELECT 2,
'{"RID": '
|| rid
|| ', '
|| LISTAGG('"' || keyword || '": "' || VALUE || '"', ', ')
WITHIN GROUP (ORDER BY keyword)
|| '}'
FROM yourdata
GROUP BY rid
UNION ALL
SELECT 3 n, '}' FROM DUAL)
ORDER BY n