Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Publishing file contents using JSON and SQL
stop;
-- Publish the data within a table using SQL
with json_rows (j) as (
select json_object(
key 'EMPNO' value empno, key 'FIRSTNME' value firstnme,
key 'MIDINIT' value midinit, key 'LASTNAME' value lastname,
key 'WORKDEPT' value workdept, key 'PHONENO' value phoneno,
key 'HIREDATE' value hiredate, key 'JOB' value job,
key 'EDLEVEL' value edlevel, key 'SEX' value sex,
key 'BIRTHDATE' value birthdate, key 'SALARY' value salary,
key 'BONUS' value bonus, key 'COMM' value comm
absent on null returning clob(2g) ccsid 1208 format json
)
from shoestore.employee
order by lastname
limit 3 offset 0)
select json_object(
key 'SHOESTORE.EMPLOYEE' value json_arrayagg(
j format json
) absent on null returning clob(2g) ccsid 1208 format json
)
from json_rows;
stop;
-- Use dynamic SQL to build JSON publishing function queries!
set path coolstuff;
set schema coolstuff;
CREATE TYPE coolstuff.COLUMN_NAME_ARRAY_TYPE AS VARCHAR(128) CCSID 37 ARRAY [8000];
create or replace variable coolstuff.myJSONvar clob(2G) CCSID 1208;
CREATE OR REPLACE FUNCTION coolstuff.generate_json_for_a_table(p_schema_name VARCHAR(128),
p_table_name VARCHAR(128),
p_pagination VARCHAR(50) default ' LIMIT 10 OFFSET 0 ')
RETURNS CLOB(2G) CCSID 1208 SPECIFIC coolstuff.generate_json_for_a_table
NOT DETERMINISTIC NO EXTERNAL ACTION NOT FENCED READS SQL DATA CALLED ON NULL INPUT
SET OPTION COMMIT = *none, DBGVIEW = *SOURCE, DYNUSRPRF = *USER
BEGIN
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE V_COLUMN_NAME_ARRAY COLUMN_NAME_ARRAY_TYPE;
DECLARE V_COLUMN_NAME_ARRAY_CARDINALITY BIGINT;
DECLARE V_LOOPVAR BIGINT default 1;
DECLARE V_JSON_STATEMENT_TEXT CLOB(2M) default '';
DECLARE v_json_object CLOB(2g) CCSID 1208 ;
SELECT ARRAY_AGG( qsys2.delimit_name(column_name) ) INTO V_COLUMN_NAME_ARRAY
FROM qsys2.syscolumns
WHERE table_schema = p_schema_name AND table_name = p_table_name and CCSID<>65535;
-- Contains 65535 if the column is a BINARY,VARBIN, BLOB, or ROWID.
SET V_COLUMN_NAME_ARRAY_CARDINALITY = CARDINALITY(V_COLUMN_NAME_ARRAY);
SET V_JSON_STATEMENT_TEXT = 'with json_rows(j) AS ( SELECT JSON_OBJECT(';
-- Add the key-value pairs
WHILE (V_LOOPVAR <= V_COLUMN_NAME_ARRAY_CARDINALITY) DO
SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT
' KEY ''' CONCAT V_COLUMN_NAME_ARRAY[V_LOOPVAR] CONCAT ''' VALUE ' CONCAT V_COLUMN_NAME_ARRAY[V_LOOPVAR];
SET V_LOOPVAR = V_LOOPVAR + 1;
IF (V_LOOPVAR < V_COLUMN_NAME_ARRAY_CARDINALITY + 1) THEN
SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT ',';
END IF;
END WHILE;
SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT ' ABSENT ON NULL RETURNING CLOB (2G) CCSID 1208 FORMAT JSON)
FROM ' concat qsys2.delimit_name(p_schema_name) concat '.' concat qsys2.delimit_name(p_table_name);
-- Add pagination
SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT p_pagination concat ')';
-- Add CTE projection
SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT
'SELECT JSON_OBJECT(KEY ''' concat
upper(p_schema_name) concat '.' concat
upper(p_table_name) concat ''' VALUE
JSON_ARRAYAGG(j format json) absent ON NULL
returning CLOB(2g) CCSID 1208 format json) from json_rows';
-- I'm only using a global variable to make it easier to develop & debug
EXECUTE IMMEDIATE 'SET coolstuff.myJSONvar = (' concat V_JSON_STATEMENT_TEXT concat ')';
RETURN coolstuff.myJSONvar;
END;
stop;
values coolstuff.generate_json_for_a_table(
p_schema_name => 'TOYSTORE',
p_table_name => 'SALES',
p_pagination => ' limit 100000 ');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.