Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Json Query

  1. #1

    Default Json Query

    Hi,

    Can we extract first occurrence from the json file. Here I am using json input step:
    The structure is like:

    gazettes: [

    • {
      • pubdoc: "CH",

      • dated: "2015-07-03",
      • code: "DIS",

      • actionDate: "2015-06-20",
      • published: "Companies House",
      • description: "Company Dissolved"

      },
    • {
      • pubdoc: "CH",
      • dated: "2015-03-24",
      • code: "SOS",
      • actionDate: "2015-03-21",
      • published: "Companies House",
      • description: "STRIKING OFF SUSPENDED"

      },


    And I want to extract first code from first block. (Underlined)

    Thanks in advance.
    g_nish

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

    Default

    $.[0].code is my wild guess ...
    So long, and thanks for all the fish.

  3. #3

    Default

    Thanks a lot marabu.....For your wild guess.

    I am thinking what should happen with this if this particular field (gazette) is missing.

    Thanks,
    g_nish
    Last edited by Nishank Goel; 12-01-2016 at 06:38 AM.

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

    Default

    If you enable option "Ignore missing path" you shouldn't have to worry.


    Name:  215544.PNG
Views: 185
Size:  6.3 KB
    So long, and thanks for all the fish.

  5. #5

    Default

    Yes this is something which I have done previously.

    Thanks

  6. #6

    Default

    Hi Marabu,

    Is there a way to handle the missing fields inside the block like:


    • gazettes: [

      • {
        • pubdoc: "CC",
        • dated: "2015-07-03",
        • actionDate: "2015-06-20",
        • published: "Companies House",
        • description: "Company Dissolved"


        },
      • {
        • pubdoc: "SS",
        • dated: "2015-03-24",
        • code: "XXX",
        • actionDate: "YYYY-03-21",
        • published: "Companies House",
        • description: "STRIKING OFF SUSPENDED"


        },
      • {

        • pubdoc: "HH",
        • dated: "2015-07-03",
        • code: "YYY",
        • actionDate: "2015-XX-20",
        • published: "Companies House",
        • description: "Company Dissolved"


        }



    Currently, I am trying to extract code and dated block from the Json response. But while extracting both the files in a single json input step it gives and error of field values mismatch.

    Error from log:

    2016/12/14 13:54:58 - Read Code and Date.0 - The data structure is not the same inside the resource! We found 3 values for json path [$..dated], which is different that the number returned for path [$..code] (2 values). We MUST have the same number of values for all paths.
    2016/12/14 13:54:58 - Read Code and Date.0 - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2016/12/14 13:54:58 - Read Code and Date.0 - The data structure is not the same inside the resource! We found 3 values for json path [$..dated], which is different that the number returned for path [$..code] (2 values). We MUST have the same number of values for all paths.

    Can you help me with this.

    Many Thanks,

    GNish

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

    Default

    You need a JSON-Input cascade:

    • Let the first step read gazette = $.gazettes
    • Let the second step read from field gazette and extract dated = $.dated and code = $.code with option "Ignore missing path" enabled.
    So long, and thanks for all the fish.

  8. #8

    Default

    Hi Marabu,

    I have tried in the similar fashion as you have mentioned:

    In place of
    dated = $.dated and code = $.code with option "Ignore missing path" enabled
    I am using double dots dated = $..dated and code = $..code with option "Ignore missing path" enabled.

    I have tried with single dot it gives me null for both the fields

    Thanks,
    GNish
    Last edited by Nishank Goel; 12-14-2016 at 06:35 AM. Reason: More details

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

    Default

    The JSONPath operator for recursive descent can't be used in your case.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  10. #10

    Default

    This is similar to previous issue:

    http://forums.pentaho.com/showthread...all-paths-quot

    I have used with dated = $..dated --> return field value for matching record.
    and dated = $.dated --> return null
    and dated = $..dated[*]--> return null


    Greatly Appreciated.
    Last edited by Nishank Goel; 12-14-2016 at 06:58 AM.

  11. #11

    Default

    Just seen the attached sample got the pain point !!!

    Thanks alot !!!

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.