How to make a string for the group by field in Oracle Data Integrator 12c

Question

I am a newbie in ODI 12c. I have recently installed it and did some tests on it. I have a table like this:

My goal is that I want to group by on customer_id and make a JSON format string for each customer_id. In Oracle database, I can do that with this query as following below:

  select customer_id,'[' || listagg('{"TRX_ID":' 
    || '"' || trx_id || '"' || ',"count_rules":' 
    || '"' || count_rules || '"'  
    || '}',',') within group(order by count_rules) || ']' as JSON_RULES
  from (select customer_id,trx_id,count(rules) as count_rules from test_rules group by 
  customer_id,trx_id) group by customer_id

The result is like this:

However, I want to do the same work in ODI 12c, would you please guide me how I can do that?

Any help is really appreciated.

Answer 1

The aggregate component in a mapping can be parametrized to use a custom GROUP BY clause if needed.

Here is how to use this component :

1. Drag and drop an aggregate component from the component pane to the mapping canvas
2. Drag CUSTOMER_ID column from source datastore to the aggregate component
3. Click on the aggregate component, go on the attributes tab and add a new column JSON_RULES
4. Click on that new attribute in the mapping canvas and set the expression '[' || listagg('{"TRX_ID":' || '"' || trx_id || '"' || ',"count_rules":' || '"' || count_rules || '"' || '}',',') within group(order by count_rules) || ']'
5. By default the aggregate component will set the Is Group By property of all attributes to Auto. Auto means that all attributes that DON'T have an aggregate function in their expression will be part of the GROUP BY clause. So in your case only CUSTOMER_ID should be in the GROUP BY clause and you should be fine. If the JSON_RULES attribute is mistakenly added in the GROUP BY clause, you can still set the Is Group By property to No.
6. In case of a need for more advanced GROUP BY clause, it's still possible to manually set one in the General tab of the aggregate component. It is also where wecan set an HAVING clause if needed

[EDIT] I didn't see you had a subquery in there. You just need to put a first aggregate component before the one I showed to aggregate by CUSTOMER_ID and TRX_ID.

Answer 2

ODI Release 12c (12.1.3) has some JSON support. Even if your installed ODI's subversion doesn't support JSON format, you can still create a view within the database

CREATE OR REPLACE VIEW v_test_rules AS
WITH t AS
(
SELECT JSON_OBJECT(KEY 'trx_id' IS TO_CHAR(trx_id) FORMAT JSON,  
                   KEY 'count_rules' IS TO_CHAR(COUNT(rules)) FORMAT JSON) AS jo,
       customer_id                          
  FROM test_rules 
 GROUP BY customer_id, trx_id
)
SELECT customer_id, JSON_ARRAYAGG( jo ) AS json_rules
  FROM t
 GROUP BY customer_id

and then call from ODI simply as

SELECT *
  FROM v_test_rules

Demo

Answer 3

I don't have much experience with ODI, but does it support Oracle's JSON SQL functions?

Query

WITH
    test_rules (trx_id, customer_id, rules)
    AS
        (SELECT 1, 'a', 3 FROM DUAL
         UNION ALL
         SELECT 1, 'a', 1 FROM DUAL
         UNION ALL
         SELECT 2, 'a', 5 FROM DUAL
         UNION ALL
         SELECT 2, 'a', 1 FROM DUAL
         UNION ALL
         SELECT 3, 'b', 1 FROM DUAL
         UNION ALL
         SELECT 3, 'b', 2 FROM DUAL
         UNION ALL
         SELECT 3, 'b', 3 FROM DUAL
         UNION ALL
         SELECT 4, 'c', 2 FROM DUAL
         UNION ALL
         SELECT 4, 'c', 3 FROM DUAL
         UNION ALL
         SELECT 5, 'd', 1 FROM DUAL
         UNION ALL
         SELECT 5, 'd', 4 FROM DUAL)
  SELECT customer_id,
         json_arrayagg (json_object (KEY 'TRX_ID' VALUE trx_id, KEY 'count_rules' VALUE count_rules))    AS json_rules
    FROM (  SELECT customer_id, trx_id, COUNT (*) AS count_rules
              FROM test_rules
          GROUP BY customer_id, trx_id)
GROUP BY customer_id;

Result

   CUSTOMER_ID                                                     JSON_RULES
______________ ______________________________________________________________
a              [{"TRX_ID":2,"count_rules":2},{"TRX_ID":1,"count_rules":2}]
b              [{"TRX_ID":3,"count_rules":3}]
c              [{"TRX_ID":4,"count_rules":2}]
d              [{"TRX_ID":5,"count_rules":2}]