Json parsing multiple layers

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Json parsing multiple layers

Post by boxtoby »

Hello,

I wonder if anyone can shed any light on this JSON parsing problem.

The following file structure is handled fine by the Hierarchical stage:

Code: Select all

[
    {
        "reach": "national",
        "profile_id": "5y2",
        "First_name": "Bob",
        "last_name": "Xyz",
        "address": {
            "zip": "ABC 123",
            "country": "United Kingdom",
            "city": "Windsor",
            "state": "Berks",
            "state_code": "BK"
        }
    }
]

This example is NOT handled:

[
    {
        "profile_id": "567",
        "First_name": "Bob",
        "last_name": "Xyz",
        "address": {
            "zip": "ABC 123",
            "country": "United Kingdom",
            "city": "Windsor",
            "state": "Berks",
            "state_code": "BK"
        },
       "deciles": [
        {
            "decile_1": 4.0,
            "decile_2": 4.0,
            "decile_3": 6.0,
            "decile_4": 1.0
        }
        ]
    }
]
It seems that the introduction of the structure { [ ] } is causing me, at least, some problems.

The schema library import actually works fine for both examples, the problem comes with mapping the columns.

The name and address columns map fine but, although the decile columns are displayed, they are not available for mapping.

The imported structure looks like this:

Code: Select all

JSON_Parser_Step
    ns0:root
        root_anon_choice_0
            e2res:@@choiceDescriminator
            nullValue
                objectValue
                    address
                    ...
                    ...
                first_name
                ...
                ...
                deciles
                    deciles_anon_choice_0
                        e2res:@@choiceDescriminator
                        nullValue
                            objectValue
                                decile_1
                                .....
                                .....
I would be very grateful if some could shed any light on this matter.

I should also add that I have no control over the JSON structure, that is decided elsewhere.

Cheers,
Bob
Bob Oxtoby
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

I have discovered a little more. When I use the "Click to Select > More" method of mapping, attempting to map decile_1 produces the following error message:

CDIUI2820E The mapping is not applicable due to an invalid type conversion or difference in the source and target list dimensions. The decile_1 is seen as a type string by the import is being mapped in to a varchar so there shouldn't be a problem.

Cheers,
Bob.
Bob Oxtoby
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

<added some code tags for you>
-craig

"You can never have too many knives" -- Logan Nine Fingers
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

The biggest key to parsing structures in the hierarchical stage is in the very first thing you need to do when you arrive at the mapping page.....before you do ANYTHING else, first map "the lowest level list that you care about" to the list of the output link..... Blue list icon to blue list icon.

In your case (and definitely ALWAYS use the "more" feature) it is probably a list in the resulting imported library that is under deciles.

Once you do that, you can map fields that are children of or parents of, the decile info.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

Hi Ernie,

Got it! I mapped the "anon choice" to the link name and then all the columns attached to it were mappable.

Cheers,
Bob.
Bob Oxtoby
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Great job! Thanks for sharing.
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
Post Reply