tFileInputJSON usage

Anonymous · ‎2012-12-10

Hi all,
I looked at the sample provided in the help for the tFileInputJSON component but there's one scenario that's not covered by it.
The sample has the following JSON file

{
	"firstName": "John",
	"lastName": "Smith",
	"address": {
		"streetAddress": "21 2nd street",
		"city": "New York",
		"state": "NY",
		"postalCode": "10021"
	},
	"phoneNumbers": 
}

I noticed that the sample is missing the closing square bracket and curly brace.
In any event, I'd like to know how to retrieve the home phone number in this example. (Everything else seems to be covered in the example)
Cheers
Craig

Anonymous · ‎2012-12-11

For this example looks ok? There is a closing square bracket (or did you fixed it already?).
In the component you can for every schema column define a query or path: refer to http://goessner.net/articles/JsonPath/

Anonymous · ‎2012-12-11

That's a great reference. Thanks for sharing!
I put my path to
"phoneNumbers.."
That's not quite right. It does give me the home phone number but of course that's assuming that its the first phone number and it gives it to me in this format:
{"number":"212 555-1234","type":"home"}
My preference is to just get the phone number.
How to set my path in this case then?

Anonymous · ‎2012-12-14

I've moved on to a real world example now. I am having troubles trying to put the appropriate JSON path to get my fields. For now I'd be happy just getting the productName.
Here are a couple of items:

,
		"scheduledTasks": null,
		"o_id": 733,
		"o_parentId": 732,
		"o_parent": null,
		"o_type": "object",
		"o_key": "Widget_corporation",
		"o_path": "\/manufacturers\/",
		"o_index": 0,
		"o_creationDate": 1348517106,
		"o_modificationDate": 1348517236,
		"o_userOwner": 2,
		"o_userModification": 2,
		"o_properties": null,
		"o_hasChilds": null,
		"o_dependencies": null,
		"o_childs": null,
		"o_locked": null,
		"o_elementAdminStyle": null,
		"____pimcore_cache_item__": "pimcore_object_733"
	}],
	"oepl_catalog_number": null,
	"oem_description": "PRESSURE CALIBRATOR 10000 PSIG",
	"oepl_upc": "095969605632",
	"is_hazmat_applicable": null,
	"vendor": ,
		"scheduledTasks": null,
		"o_id": 23305,
		"o_parentId": 23304,
		"o_parent": null,
		"o_type": "object",
		"o_key": "Widget_corporation",
		"o_path": "\/vendors\/",
		"o_index": 0,
		"o_creationDate": 1350573369,
		"o_modificationDate": 1350573599,
		"o_userOwner": 2,
		"o_userModification": 2,
		"o_properties": null,
		"o_hasChilds": null,
		"o_dependencies": null,
		"o_childs": null,
		"o_locked": null,
		"o_elementAdminStyle": null,
		"____pimcore_cache_item__": "pimcore_object_23305"
	}],
	"weight": null,
	"oepl_origin": "US",
	"oepl_harmonization": "9026204000",
	"sku": "11825",
	"family": "PTOOL",
	"oepl_products_id": "11825",
	"image": null,
	"_type": null,
	"tax_class_id": "Taxable Goods",
	"entity_id": "4872",
	"url_key": "Widget-717-10000g-pressure-calibrator-10000-psig.html",
	"oepl_short_description": "Widget 717-10000G Pressure Calibrator 10000 Psig",
	"oepl_hide_price": 0,
	"websites": "optimumgsa;optimumstores;canada",
	"category": null,
	"visibility": "Catalog, Search",
	"videos": null,
	"source": "March 2 2012",
	"last_edited": {
		
	},
	"imported_at": {
		
	},
	"assets": ,
		"dependencies": null,
		"childs": null,
		"hasChilds": null,
		"scheduledTasks": null,
		"____pimcore_cache_item__": "pimcore_asset_39"
	}],
	"currtype": "Fixed USD and CAD",
	"price_us": 1354.55,
	"price_cad": 1613,
	"cost_us": 1015.91,
	"cost_cad": 1066.71,
	"discount_us": 0.25,
	"discount_cad": 0.35,
	"gsa_1": 1110.73,
	"gsa_5": 1082.96,
	"nist_price_us": null,
	"nistl_price_us": null,
	"box_qty": null,
	"box_price_us": null,
	"box_price_cad": null,
	"associatedPromo": null,
	"parent_id": null,
	"parent_name": null,
	"configured_id": null,
	"item_no": "11825",
	"assigned_item_no": null,
	"lastImportDate": {
		
	},
	"lastImportResult": "new",
	"lazyLoadedFields": null,
	"o_published": true,
	"o_class": null,
	"o_versions": null,
	"o___loadedLazyFields": [],
	"scheduledTasks": null,
	"o_id": 46447,
	"o_parentId": 40625,
	"o_parent": null,
	"o_type": "object",
	"o_key": "717-10000g-11825",
	"o_path": "\/products\/",
	"o_index": 0,
	"o_creationDate": 1355413034,
	"o_modificationDate": 1355413263,
	"o_userOwner": 0,
	"o_userModification": 0,
	"o_properties": null,
	"o_hasChilds": null,
	"o_dependencies": null,
	"o_childs": null,
	"o_locked": null,
	"o_elementAdminStyle": null,
	"____pimcore_cache_item__": "pimcore_object_46447"
},
{
	"o_classId": 3,
	"o_className": "Product",
	"productName": "Widget 700PTPK Pneumatic Test Pump Kit  0 To 600 Psi\/40 Bar",
	"oepl_description": "Widget 700PTPK Pneumatic Test Pump Kit  0 To 600 Psi\/40 Bar",
	"productModel": "700PTPK",
	"productType_accessories": "Pressure",
	"status": 0,
	"source_from": "OEM",
	"productType": "Accessories",
	"manufacturer": ,
		"scheduledTasks": null,
		"o_id": 733,
		"o_parentId": 732,
		"o_parent": null,
		"o_type": "object",
		"o_key": "Widget_corporation",
		"o_path": "\/manufacturers\/",
		"o_index": 0,
		"o_creationDate": 1348517106,
		"o_modificationDate": 1348517236,
		"o_userOwner": 2,
		"o_userModification": 2,
		"o_properties": null,
		"o_hasChilds": null,
		"o_dependencies": null,
		"o_childs": null,
		"o_locked": null,
		"o_elementAdminStyle": null,
		"____pimcore_cache_item__": "pimcore_object_733"
	}],
	"oepl_catalog_number": null,
	"oem_description": "PNEUMATIC TEST PUMP KIT  0 TO 600 PSI\/40 BAR",
	"oepl_upc": "095969617970",
	"is_hazmat_applicable": null,
	"vendor": ,
		"scheduledTasks": null,
		"o_id": 23305,
		"o_parentId": 23304,
		"o_parent": null,
		"o_type": "object",
		"o_key": "Widget_corporation",
		"o_path": "\/vendors\/",
		"o_index": 0,
		"o_creationDate": 1350573369,
		"o_modificationDate": 1350573599,
		"o_userOwner": 2,
		"o_userModification": 2,
		"o_properties": null,
		"o_hasChilds": null,
		"o_dependencies": null,
		"o_childs": null,
		"o_locked": null,
		"o_elementAdminStyle": null,
		"____pimcore_cache_item__": "pimcore_object_23305"
	}],
	"weight": null,
	"oepl_origin": "US",
	"oepl_harmonization": "9026902000",
	"sku": "11824",
	"family": "PTOOL",
	"oepl_products_id": "11824",
	"image": null,
	"_type": null,
	"tax_class_id": "Taxable Goods",
	"entity_id": "4870",
	"url_key": "Widget-700ptpk-pneumatic-test-pump-kit-0-to-600-psi-40-bar.html",
	"oepl_short_description": "Widget 700PTPK Pneumatic Test Pump Kit  0 To 600 Psi\/40 Bar",
	"oepl_hide_price": 0,
	"websites": "optimumgsa;optimumstores;canada",
	"category": null,
	"visibility": "Catalog, Search",
	"videos": null,
	"source": "March 2 2012",
	"last_edited": {
		
	},
	"imported_at": {
		
	},
	"assets": ,
		"dependencies": null,
		"childs": null,
		"hasChilds": null,
		"scheduledTasks": null,
		"____pimcore_cache_item__": "pimcore_asset_39"
	}],
	"currtype": "Fixed USD and CAD",
	"price_us": 645,
	"price_cad": 729,
	"cost_us": 483.75,
	"cost_cad": 507.94,
	"discount_us": 0.25,
	"discount_cad": 0.3,
	"gsa_1": 528.9,
	"gsa_5": 515.68,
	"nist_price_us": null,
	"nistl_price_us": null,
	"box_qty": null,
	"box_price_us": null,
	"box_price_cad": null,
	"associatedPromo": null,
	"parent_id": null,
	"parent_name": null,
	"configured_id": null,
	"item_no": "11824",
	"assigned_item_no": null,
	"lastImportDate": {
		
	},
	"lastImportResult": "new",
	"lazyLoadedFields": null,
	"o_published": true,
	"o_class": null,
	"o_versions": null,
	"o___loadedLazyFields": [],
	"scheduledTasks": null,
	"o_id": 46446,
	"o_parentId": 40625,
	"o_parent": null,
	"o_type": "object",
	"o_key": "700ptpk-11824",
	"o_path": "\/products\/",
	"o_index": 0,
	"o_creationDate": 1355413033,
	"o_modificationDate": 1355413261,
	"o_userOwner": 0,
	"o_userModification": 0,
	"o_properties": null,
	"o_hasChilds": null,
	"o_dependencies": null,
	"o_childs": null,
	"o_locked": null,
	"o_elementAdminStyle": null,
	"____pimcore_cache_item__": "pimcore_object_46446"
},
]

Anonymous · ‎2012-12-14

Well, as typically happens...fight with it all day, post the question then figure out the answer right after posting.
My JSONPath needed to be set to "$..productName".

I would still like to know how to do the filtering but that's on the "nice to have list" for me right now.

Anonymous · ‎2012-12-15

To be onest I am not the expert in json query and sorry I didn't found a way with the built in components.
The first you need is to add an leading key to your json object.
For the purspose of extracting values from the json I prefer the use of tJsonNormalize component (you get this from Talend Exchange).
This component provides as output flow every single key-value pair within your json object.
I your case you only need the JSON_KEY and JSON_VALUE

Anonymous · ‎2013-02-28

Thank you very much crtaylor.
I was the same problem with the JSONPath, I have to define the JSONPath with the same format like you "$..name"
It works now !!

Anonymous · ‎2014-07-09

i have a json string which im trying to extract from it specific field(id).im doing it by textractjsonfield-this components first convert the jsonstring into xml in order that the it can extract fields from the json string(i got from an api),but unfortunately when my json string is not valid as xml string(contains numbers,invalid chars in xml format) and therfore im getting an exception from talend...
how can i extract fields from my json by talend without the authomatic way of talend to parse the json into xml? does tjsonnormalize can be relavent ?
thanks a lot shani.
This is a little from the json

here for example i need all the "id" fields that are after a number:"0","1"...)
{
"response": {
"code": 200,
"msg": "Success",
"data": {
"0": {
"id": "15124",
"name": " yoav (yoavshaki@yahoo.com) - 301519506662355",
"network_id": 1,
"network_type": "Facebook",
"currency": "USD",
"currency_info": {
"prefix": "$",
"postfix": "",
"name": "US Dollars"
},
"timezone": {
"id": 139,
"code": "IST",
"region": "Asia",
"locality": "Jerusalem",
"offset": 3,
"facebook_code": 70
}
}
}
}
}

JSON

Talend Data Integration

v5.x