Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: [HELP] How To Parse DB Data with Nested Value Json Format?

  1. #1
    Join Date
    Jan 2017
    Posts
    6

    Question [HELP] How To Parse DB Data with Nested Value Json Format?

    Dear master of PDI,

    I'm newbie and still learning for ETL development.
    I have question, how can I parse data from input step DB ,i'm using Oracle 11G express and then there is value in a field contains json format like this
    I have 2 field that related each other like this sample data
    Code:
    EVENT_NAME EVENT_VALUE
    af_add_to_cart
    {af_currency:USD,af_content_id:[\15555048\],af_quantity:[1],af_price:[110200],af_content_type:product}
    af_purchase
    {af_currency:USD,af_content_id:201702117702161,af_revenue:62100,af_quantity:1}
    af_login
    {email:johndoe@gmail.com}
    Please help me how to parse this data from DB input step to selected value.
    I know how to filter event value based on the Event name but how can I parse the value from event_value after I got filter data?
    I want data like this
    Code:
    event_name currency content_id content_type revenue quantity price email
    add_to_cart USD 15555048 product 1 110200
    purchase USD 201702117702161 62100 1
    login johndoe@gmail.com
    Please help me how to parse data for this thing
    Thanks in advance

    Regards
    Jendmaoul
    Last edited by jendmaoul; 02-13-2017 at 12:04 AM. Reason: EDIT

  2. #2
    Join Date
    May 2014
    Posts
    358

    Default

    I suggest using the Javascript step, since Javascript works with JSON natively. There you should be able to do something like:

    var currency = EVENT_VALUE.af_currency;

  3. #3
    Join Date
    Jan 2017
    Posts
    6

    Default

    Quote Originally Posted by Lukfi View Post
    I suggest using the Javascript step, since Javascript works with JSON natively. There you should be able to do something like:

    var currency = EVENT_VALUE.af_currency;
    I have tried what you suggest but the result of all columns are undefined, I did like this

    [CODE]
    var currency = EVENT_VALUE.af_currency;
    var content = EVENT_VALUE.af_content_id;
    var qty = EVENT_VALUE.af_quantity;
    var price = EVENT_VALUE.af_price;
    var content_type = EVENT_VALUE.af_content_type;
    [CODE]

  4. #4
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi jendmaoul,

    I would say your EVENT_VALUE is JSON-like. I would expect an example to look like :-

    {
    "af_currency": "USD",
    "af_content_id": "15555048",
    "af_quantity": 1,
    "af_price": 110200,
    "af_content_type": "product"
    }

    You can then use 'JSON Input' to process the data after the EVENT_NAME filter. I have never used Oracle express, but is the a setting to return true JSON.

    Just my 2-cents worth.

    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Don't know about JavaScript, so I tried without it
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Jan 2017
    Posts
    6

    Question

    Quote Originally Posted by marabu View Post
    Don't know about JavaScript, so I tried without it
    Thank you Mr. Marabu for your kindness and your ktr made .
    But when I was trying run your ktr there is an error like this.

    Code:
    2017/02/14 08:41:01 - JSON Input.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected Error : org.pentaho.di.core.exception.KettleException: 
    2017/02/14 08:41:01 - JSON Input.0 - Error parsing string [Unexpected character (a) at position 1.]!
    2017/02/14 08:41:01 - JSON Input.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleException: 
    2017/02/14 08:41:01 - JSON Input.0 - Error parsing string [Unexpected character (a) at position 1.]!
    2017/02/14 08:41:01 - JSON Input.0 - 
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonReader.readString(JsonReader.java:147)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonInput.parseJson(JsonInput.java:272)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonInput.readFileOrString(JsonInput.java:258)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonInput.ReadNextString(JsonInput.java:165)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonInput.getOneRow(JsonInput.java:396)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.steps.jsoninput.JsonInput.processRow(JsonInput.java:344)
    2017/02/14 08:41:01 - JSON Input.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2017/02/14 08:41:01 - JSON Input.0 - 	at java.lang.Thread.run(Unknown Source)
    anyway what is PDI version that you are using?, mine is pdi-ce-5.4.0.1-130
    also is there any additional library that I should include in pentaho library so this .ktr can work?
    Thanks in advance

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    The KTR file is made with version 7.
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Jan 2017
    Posts
    6

    Default

    thank you sir, I'll try in PDI version 7
    Quote Originally Posted by marabu View Post
    The KTR file is made with version 7.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.