Link to home
Start Free TrialLog in
Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America

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

Open in new window

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"}
]

Open in new window

Avatar of Sean Stuber
Sean Stuber

you can use sql to generate the basic content
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
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_json);
END;
Avatar of Eddie Shipman

ASKER

The
WITHIN GROUP (ORDER BY keyword)

Open in new window

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

Open in new window

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
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Found the xmlagg stuff earlier and it works perfectly.
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

Open in new window

great