Using the JSON and JSONB data types inside stored procedures

Learn how to use the JSON and JSONB data types inside stored procedures.

The JSON datatype is supported in Netezza Performance Server 11.0.3.X, 11.1.X.X and higher.

The JSONB datatype is supported in Netezza Performance Server 11.1.X.X and higher.

Using the JSON and JSONB data types inside stored procedure is the same as creating any table inside a stored procedure.

You can use any SQL operation, such as alter table, create view, create external table, adding constraints, etc., which are supported with the JSON and JSONB data types inside stored procedures.

Example:
  • Run drop/create/insert queries for a json table inside the stored procedure:
    TESTSP.ADMIN(ADMIN)$> DROP TABLE <json_table_name> IF EXISTS;
 TESTSP.ADMIN(ADMIN)$> CREATE TABLE <json_table_name>(C1 JSON);
 TESTSP.ADMIN(ADMIN)$> INSERT INTO <json_table_name> VALUES('{ "CUSTOMER": "ABK", "ITEMS": {"PRODUCT": "LIGHT","QTY": 63}}');
  • Select a use query from a table:
    TESTSP.ADMIN(ADMIN)$> SELECT COUNT(*) INTO tableCnt FROM <json/jsonb_table_name>;
 TESTSP.ADMIN(ADMIN)$> RETURN tableCnt;

Examples

Examples for the JSON data type
CREATE OR REPLACE PROCEDURE JSONSP()
 RETURNS integer
 EXECUTE AS OWNER LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
 tableCnt integer;
 BEGIN
 DROP TABLE JSONTABLE IF EXISTS;
 CREATE TABLE JSONTABLE(C1 JSON);
 INSERT INTO JSONTABLE VALUES('{ "CUSTOMER": "ABK", "ITEMS": {"PRODUCT": "LIGHT","QTY": 63}}');
 INSERT INTO JSONTABLE VALUES('{ "CUSTOMER": "PQR", "ITEMS": {"PRODUCT": "LIGHT","QTY": 6}}');
 SELECT COUNT(*) INTO tableCnt FROM JSONTABLE;
 RETURN tableCnt;
 END;
 END_PROC;
  • Call or invoke procedure:
    TESTSP.ADMIN(ADMIN)=> call JSONSP();
 JSONSP
 --------
 2
 (1 row)
  • Show procedure:
    TESTSP.ADMIN(ADMIN)=> show procedure JSONSP;
 SCHEMA | RESULT | PROCEDURE | BUILTIN | ARGUMENTS
 ----------------------------------------
 ADMIN | INTEGER | JSONSP | f | ()
 (1 row)
Examples for a table with the JSONB data type
CREATE OR REPLACE PROCEDURE JSONB_SP()
 RETURNS integer 
 EXECUTE AS OWNER LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE 
 tableCnt integer;
 BEGIN
 DROP TABLE JSONB_TABLE IF EXISTS;
 CREATE TABLE JSONB_TABLE (C1 JSONB, C2 INT, C3 VARCHAR(50));
 INSERT INTO JSONB_TABLE VALUES('{"TITLE": "SLEEPING BEAUTIES", "GENRES": ["FICTION", "THRILLER", "HORROR"], "PUBLISHED":"FALSE"}',1,'ABC');
 INSERT INTO JSONB_TABLE VALUES('{"TITLE": "INFLUENCE", "GENRES": ["MARKETING & SALES", "SELF-HELP ", "PSYCHOLOGY"], "PUBLISHED": "TRUE"}',2,'PQR');
 SELECT COUNT(*) INTO tableCnt FROM JSONB_TABLE;
 RETURN tableCnt;
 END;
 END_PROC;
  • Call or invoke procedure:
    TESTSP.ADMIN(ADMIN)=> call JSONB_SP();
JSONB_SP
 ----------
 2
 (1 row)
  • Show procedure:
    TESTSP.ADMIN(ADMIN)=> show procedure JSONB_SP;
 SCHEMA | RESULT | PROCEDURE | BUILTIN | ARGUMENTS
 ----------------------------------------
 ADMIN | INTEGER | JSONB_SP | f | ()
 (1 row)