When I need to store JSON data in a DB2 for i table this is my preferred approach.

JSON data can be stored in the database using a character, graphic or binary string value. I prefer to store JSON data in the BSON binary format, which provides faster access to the JSON data.

DB2 for i Table

I like to store my JSON data in a BLOB.

The following code provides a simplified example of how this is defined.

CREATE OR REPLACE TABLE INTEGRATION_MESSAGES  (
  ID                INTEGER       GENERATED BY DEFAULT AS IDENTITY ,
  MESSAGE_TYPE      VARCHAR(10) ,
  MESSAGE_DATA      BLOB(256K) ,
  CONSTRAINT INTEGRATION_MESSAGES_PK PRIMARY KEY (ID) ) ;

When I want to browse the JSON data I find it convenient to create a view and use the JSON_TABLE table function to provide columns based on the JSON object properties.

CREATE OR REPLACE VIEW INTEGRATION_MESSAGES_LEADS AS ( 
  WITH
    JSON_DOC AS (
      SELECT
        IM.ID ,
        JT.JSON_ID ,
        JT.JSON_FIRST_NAME ,
        JT.JSON_LAST_NAME ,
        JT.JSON_ADDRESS ,
        JT.JSON_EMAIL
      FROM
        INTEGRATION_MESSAGES AS IM,
        JSON_TABLE( 
          IM.MESSAGE_DATA,
          'lax $'
          COLUMNS (
            JSON_ID          INTEGER      PATH 'lax $.id' ,
            JSON_FIRST_NAME  VARCHAR(50)  PATH 'lax $.firstName' ,
            JSON_LAST_NAME   VARCHAR(50)  PATH 'lax $.lastName' ,
            JSON_ADDRESS     VARCHAR(100) PATH 'lax $.address.address' ,
            JSON_EMAIL       VARCHAR(100) PATH 'lax $.email' 
          )
        ) AS JT
    )

  SELECT
    IM.ID ,
    JD.JSON_ID ,
    JD.JSON_FIRST_NAME ,
    JD.JSON_LAST_NAME ,
    JD.JSON_ADDRESS ,
    JD.JSON_EMAIL 
  FROM
    INTEGRATION_MESSAGES AS IM
    LEFT OUTER JOIN JSON_DOC AS JD ON JD.ID=IM.ID
  WHERE
    IM.MESSAGE_TYPE = 'LEAD'
) ;

For this simple example I can create some demo data using a REST api service that returns fake data. Thanks to the folks at https://dummyjson.com.

Note that the fake data returned from https://dummyjson.com includes more properites than I am including in the view - I’m trying to keep the example simple.

Note that in order to use HTTPS with HTTP_GET scalar function you need to have created the *SYSTEM certificate store in DCM and set the necessary authorities to the certificate store files.

begin
  declare data_id integer default 0;
  while data_id < 30 do
    set data_id = data_id + 1;
    insert into INTEGRATION_MESSAGES (message_type, message_data) values('LEAD', json_to_bson(http_get('https://dummyjson.com/users/'||char(data_id))));
  end while;
end

You can now browse the demo data using a simple select statement over the view.

select * from INTEGRATION_MESSAGES_LEADS;

Native DB2 JSON Support

Detailed descriptions of the following functions can be found in the IBM i SQL Programming: JSON Concepts.

  • The IS JSON predicate can be used in a query to validate JSON data.

  • The JSON_TO_BSON and BSON_TO_JSON scalar functions can be used to convert between JSON and BSON formatted data. Examples of these can be seen below in the example RPGLE programs.

  • The JSON_QUERY function returns an SQL/JSON value from the specified JSON text by using an SQL/JSON path expression.

  • The JSON_VALUE function returns an SQL scalar value from a JSON text by using an SQL/JSON path expression.

  • The JSON_TABLE table function can be used to converts all or part of a JSON document into a relational table that can be queried. You can see an example of this used in the sample SQL view.

Add a Row from RPGLE

**free

ctl-opt option(*nodebugio:*srcstmt:*nounref);
ctl-opt main(main) actgrp(*new);

dcl-s clob_t sqltype(clob:262144) ccsid(*utf8) template;

dcl-proc main;
  dcl-s json_data varchar(262144) ccsid(*utf8);
  dcl-s clob_data like(clob_t);

  exec sql set option commit = *none;

  json_data = '{"id":30, "firstName":"Richard","lastName":"Moulton"}';              
  clob_data = set_clob_data(json_data);

  exec sql
    insert into integration_messages
      (message_type, message_data)
      values('LEAD', json_to_bson(:clob_data));

end-proc;

dcl-proc set_clob_data;
  dcl-pi *n likeds(clob_t);
    json_data varchar(262144) ccsid(*utf8) const;
  end-pi;

  dcl-ds clob_data likeds(clob_t);

  clob_data.clob_t_data = json_data;
  clob_data.clob_t_len  = %len(json_data);

  return clob_data;
end-proc;

Read a Row from RPGLE

This first example accesses the MESSAGE_DATA column directly, which provides the full JSON message in a variable.

**free

ctl-opt option(*nodebugio:*srcstmt:*nounref);
ctl-opt main(main) actgrp(*new);

dcl-s clob_t sqltype(clob:262144) ccsid(*utf8) template;

dcl-proc main;
  dcl-s json_data varchar(262144) ccsid(*utf8);
  dcl-s clob_data like(clob_t);

  exec sql set option commit = *none;

  exec sql
    select 
      bson_to_json(message_data)
    into
      :clob_data
    from
      integration_messages
    where
      id = (select max(id) from integration_messages);

  json_data = get_json_data(clob_data);

  snd-msg json_data;
  snd-msg 'Length of JSON: ' + %char(%len(json_data));

end-proc;

dcl-proc get_json_data;
  dcl-pi *n varchar(262144) ccsid(*utf8);
    clob_data likeds(clob_t) const;
  end-pi;

  dcl-s json_data varchar(262144) ccsid(*utf8);

  json_data = %subst(
    clob_data.clob_t_data:
    1:
    clob_data.clob_t_len
  );

  return json_data;
end-proc;

This second example accesses the JSON data via the INTEGRATION_MESSAGES_LEADS view, which provides a much cleaner interface.

**free

ctl-opt option(*nodebugio:*srcstmt:*nounref);
ctl-opt main(main) actgrp(*new);

dcl-proc main;
  dcl-s first_name varchar(50);
  dcl-s last_name varchar(50);

  exec sql set option commit = *none;

  exec sql
    select 
      JSON_FIRST_NAME ,
      JSON_LAST_NAME
    into
      :first_name,
      :last_name
    from
      integration_messages_leads
    where
      id = (select max(id) from integration_messages);

  snd-msg first_name;
  snd-msg last_name;

end-proc;

Useful References