Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: How To Nest JSON Output

  1. #1

    Default How To Nest JSON Output

    Hi all,

    Simple question. I'm trying to build a JSON structure to integrate our app with ZenDesk via the RestClient step and ZenDesk's REST API. So I need to create

    {"ticket":{"requester":{"name":"The Customer", "email":"thecustomer@domain.com"},
    "subject":"My printer is on fire!",
    "comment": { "body": "The smoke is very colorful." }}}

    But I'm having issues nesting data anywhere beyond the root level. I've seen some discussion of using the JSON Input step using the
    $.comment.body notation in the element name field but that's just creating root elements of that name. Is it possible to nest data
    this way using the JSON Output step? It seems like it should be able to do this.

    If not what's the best way to create complex JSON objects?

    Cheers,

    Walter.

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

    Default

    Nesting isn't supported by JSON Output.
    The step can generate a flat notation as part of a named array, that's all.
    While you can extract a single item from the array via JSON Input, you can't add another JSON Output downstream, because zhe item is treated as a string, thus introducing unwanted escapes.
    With Kettle version 4.4 I would try to use scripting today.
    So long, and thanks for all the fish.

  3. #3

    Default

    Can we generate nested tags using XML output and then use this step as input to Json output step?

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

    Default

    Think twice.
    Even if you manage to create an XML representation of a nested JavaScript object, it's inevitable that you convert it back to rows and fields, so that JSON Output can use it as input.
    There's just no way to use JSON Output to generate a nested object notation.
    The step wasn't designed for this.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2014
    Posts
    4

    Default

    If the you are creating the JSON to add documents to a Mongo Database, then I have found it is possible to create complex JSON documents using the "MongoDB Output" step (see attached). This task contains a tab called "mongo document fields" that (unlike "JSON Output") lets you specify a nested JSON document path using dot notation (e.g. ticket.requestor.email ==> {ticket:{requestor:{email:"blah"}}} ).

    Obviously, if you're not using Mongo, this post will be of no help to you. However, I thought it was worth mentioning.

    It's a shame the "JSON Output" step doesn't have the functionality held within the "MongoDB Output" step.

    ComplexJSON.ktr

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

    Default

    Quote Originally Posted by alankonz View Post
    It's a shame the "JSON Output" step doesn't have the functionality held within the "MongoDB Output" step.
    Jira isn't only for bug tracking, it's for feature requests, too.
    So long, and thanks for all the fish.

  7. #7

    Default

    Thanks for all the responses guys. So the step wasn't designed to do this? Why not? It seems like a JSON Output step should allow you to output whatever JSON you want. I want to create JSON for use with the REST Client step. It's pretty useless without being able to nest.

    My solution was simply to build nested XML and use a standard XSLT to transform the XML to JSON. Which works, but is quite hacky. The good old XML stack with it's robust functionality.

  8. #8
    Join Date
    Aug 2008
    Posts
    563

    Default

    Turns out my Jira search didn't reveal any results for this requirement - oh well, I created a Jira case for it now:

    Be so kind and add your vote.
    Best regards,
    Diethard
    ===============
    Visit my Pentaho blog which offers some tutorials mainly on Kettle, Report Designer and Mondrian
    ===============

  9. #9
    Join Date
    Dec 2015
    Posts
    4

    Default Can any one share one of sample ktr file...I couldn't able to achieve this.

    Can any one share a sample ktr file or steps to be followed to achieve nested JSON output in pentaho....I am new to Pentaho, I would require help /guidance from senior members.

  10. #10
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi,

    for me the simplest way to build a nested JSON is to use javascript step and the JSON.stringify( ) function:

    var obj = {};
    obj.entries = [ {"id": 1, "value": "stuff"}, {"id": 2, "foo": 0} ];
    var jsonString = JSON.stringify(obj);

    With a combination of json output step, group by step with concatenate string and jsvascript you can do almost everything.
    But this is not the most efficient way.

    You could also use the java json api in a UDJC step

  11. #11
    Join Date
    Dec 2015
    Posts
    4

    Default

    Thanks bro,

    your solution worked perfectly for my requirement, I have tried your first solution.

    ------------------------------------------------------------------
    Quote Originally Posted by Mathias.CH View Post
    Hi,

    for me the simplest way to build a nested JSON is to use javascript step and the JSON.stringify( ) function:

    var obj = {};
    obj.entries = [ {"id": 1, "value": "stuff"}, {"id": 2, "foo": 0} ];
    var jsonString = JSON.stringify(obj);

    With a combination of json output step, group by step with concatenate string and jsvascript you can do almost everything.
    But this is not the most efficient way.

    You could also use the java json api in a UDJC step

  12. #12
    Join Date
    Dec 2017
    Posts
    10

    Default

    I would like to represent a different structure such as user/messages where users has a collection of messages. How do I create the nested output to MongoDB if the representation is coming from a join merge?
    Using pentaho 8.
    Best regards

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.