Parsing JSON with tExtractJSONFields

[Anonymous]

I have this JSON that I made up

{ "store": {
    "book": [ 
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby **bleep**",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": [
			{ "EU":22.99},
			{ "US":72.3 },
			{ "BIH":5.80}
		]
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}

Is it possible that I can use tExtractJSONFields  to get every book in its own row ? How can I do that ?

My end goal the is to go through each of that row and get prices.

[Anonymous]

I see your problem. First of all, you have a problem with your JSON. I think this is what your JSON should look like.....

{  
   "Actions":[  
      {  
         "ActionID":410788,
         "Allocations":[  

         ]
      },
      {  
         "ActionID":410766,
         "Allocations":[  
            {  
               "AllocTargetID":82373,
               "Fiscal_Year":2018
            },
            {  
               "AllocTargetID":82378,
               "Fiscal_Year":2020
            }
         ]
      }
   ]
}

The JSON you posted is not valid JSON.

Apart from that issue (I assume caused by copying and pasting), you do have a point in that it is difficult to push rows through a tExtractJSONField component where the "Look JSONPath Query" returns nothing. This is the case for your empty array. But there is a workaround for this. I built a job as shown below...

The tJavaFlex is just where I added your JSON. I won't show that. The example I used is above.

The first tExtractJSONFields component looks like below.....

This should be pretty easy to understand.

The tJavaRow is where the workaround takes place. The code looks like this.....

Essentially, what I am doing is passing through the ActionID as normal, but adding ....

{"Allocations":

....and ....

}

...around the array "[]" passed on. If the array looks like this "[]" I am substituting it with this....

[empty]

What this does is cause the tExtractJSONField component to try to process the array. It won't find anything for "empty", but it means that a row is passed through anyway. This row carries your ActionID.

The tExtractJSONField component after the tJavaRow looks like this...

Pay attention to the JSONPaths.

When it runs, the following is output....

[Anonymous]

Thank you for the details. Will give a try and let you know.

My json have private information, so just modified your existing json and
pasted here.

[Anonymous]

I thought there would be a reason for the JSON not being quite correct. Let me know how you get on 🙂

[Anonymous]

@rhall Your suggestion to use tJavaRow for Null check worked like a charm. Thank you so much.

Note: I do not find any difference in the functionality with the value used for 'Loop JSON Path query' as "$.Allocations[*]" or "$.[*]"  having JSON Field as Allocations

Thanks a lot again

[PITadmin]

Hi,

i have a similar problem with extracting JSON.

I get a REST API response that looks like this but my tExtractJSONFields delivers 0 rows.

When i try $.[*].basicData.firstName @http://www.jsonquerytool.com/ i get the correct data.

thx in advance for your ideas

Stefan

[
  {
    "updateFields": null,
    "id": 2,
    "externalIdentifier": null,
    "externalVersion": null,
    "externalSource": null,
    "entityVersion": 35,
    "basicData": {
      "updateFields": null,
      "firstName": "Renate",
      "firstName2": null,
      "lastName": "Test",
      "personTypes": [
        "Employee"
      ],
      "companyMembershipIds": [],
      "profilePictureUuid": "f4377dbb-3ab0-4565-a1b3-856d22c90a6d",
      "deleted": false
    },
    "personalData": null,
    "privateData": null,
    "privacySettings": null,
    "assignmentsOverview": null,
    "user": {
      "updateFields": null,
      "id": 2,
      "enabled": true,
      "username": "rtest",
    },
    "currentCareers": null,
    "pastCareers": null,
    "futureCareers": null
  },
  {
    "updateFields": null,
    "id": 3,
    "externalIdentifier": null,
    "externalVersion": null,
    "externalSource": null,
    "entityVersion": 18,
    "basicData": {
      "updateFields": null,
      "firstName": "Stefan",
      "firstName2": null,
      "lastName": "Muster",
      "personTypes": [
        "Employee"
      ],
      "companyMembershipIds": [],
      "profilePictureUuid": "68b76185-0d1e-4301-b1e7-cd8db30496c6",
      "deleted": false
    },
    "personalData": null,
    "privateData": null,
    "privacySettings": null,
    "assignmentsOverview": null,
    "user": {
      "updateFields": null,
      "id": 5,
      "enabled": true,
      "username": "smuster",
    },
    "currentCareers": null,
    "pastCareers": null,
    "futureCareers": null
  }

[PITadmin]

Just found out that my tRestClient does not deliver JSON but XML!?

Why?

[Anonymous]

What is the configuration of your tRestClient? In the "Advanced Settings" do you have "Convert Response To DOM Document" ticked?

[PITadmin]

Yes that helped - now it ist responding in JSON format

Thx a lot

Stefan

[swetha_molaka1]

Hello All,

I want to implement if then else condition in the schema mapping .( Eg: if id=245 then firstname else null)

I tried using it fieldValues[?(@.id==2645)].value to fetch value fieldValues if id==2645.

The result is coming up as []. I am expecting some value but getting empty array.

Sample json:

{

"type": customobject,

"id":1234,

"fieldValues": [

{

"type":FieldValue

"id":2654

"value": carl

},

{

"type":FieldValue

"id":2655

"value": Maxx

},

{

"type":FieldValue

"id":2656

"value": software Engineer

},

{},

{},

],

"iSMapped":yes

"uniqcode":"CONT-23333"

}

Please help me its urgent.

[Jennifer_siders]

Thanks! it helped