+5Hi, sorry for this newbie JSON extractor question but I am using the JASONExtractor transformer and I am trying to populate a column called 'Temperature' and populate it with the value, such as '50' shown below. My query is this: json["beds"].["Weather"].["Temperature"] but this and many, many other variations also do not work. Can someone please, please help me?
[
{
"id": 3009,
"beds": {
"Time": "Morning",
"Weather": {
"Temperature": 50
},
{
"id": 2969,
"code": "STPAL",
"name": "Acme Co."
}
]
Best answer by ebygomm
Not 100% sure on what you are trying to get as your final output, but I'd probably use a JSONFragmenter with a JSON Query as follows, and choose to Flatten Query Result into Attributes
json[*]["name"]+json[*]["STATION"][*]That will get you this far
+22This should be possible, using the JSONExtractor, but I prefer the JSONFlattener transformer.
That transformer converts the JSON into attributes and then they are easier to use and manipulate.
Hope this helps.
JSON query shouldn't be delimited with dot.
Since the sample data you have posted is not a valid JSON document, it's hard to provide an appropriate JSON query. Please post a valid JSON document sample.
+5JSON query shouldn't be delimited with dot.
Since the sample data you have posted is not a valid JSON document, it's hard to provide an appropriate JSON query. Please post a valid JSON document sample.
Hi again, I am reading JSON over a web API and have copied what it looks like into the attached text file. Basically, I am trying to extract the number of hospital bed care types ,which then get their own column. Thanks very much for any guidance here!
This is the full text of the document. Which object do you need to extract?
[
{
"id" : 30,
"code" : "AUR",
"name" : "Hospital 1",
"STATION" : {
"Time" : "2020-04-11T08:37:05.353",
"burnunit" : {
"current" : 13
},
"obgyn" : {
"current" : 3
},
"ventilators" : {
"current" : 29
},
"isolate" : true
}
},
{
"id" : 63,
"aid" : "551",
"license" : "0022",
"code" : "PRC",
"name" : "Hospital 2",
"STATION" : {
"Time" : "2020-05-12T04:06:42.66",
"burnunit" : {
"current" : 11
},
"obgyn" : {
"current" : 15
},
"vascular" : {
"current" : 18
},
"ventilators" : {
"current" : 22
},
"isolate" : false
}
},
{
"id" : 29,
"aid" : "3302",
"license" : "0066",
"code" : "TPA",
"name" : "Hospital 3"
}
]
+44Not 100% sure on what you are trying to get as your final output, but I'd probably use a JSONFragmenter with a JSON Query as follows, and choose to Flatten Query Result into Attributes
json[*]["name"]+json[*]["STATION"][*]That will get you this far
+5This is the full text of the document. Which object do you need to extract?
[
{
"id" : 30,
"code" : "AUR",
"name" : "Hospital 1",
"STATION" : {
"Time" : "2020-04-11T08:37:05.353",
"burnunit" : {
"current" : 13
},
"obgyn" : {
"current" : 3
},
"ventilators" : {
"current" : 29
},
"isolate" : true
}
},
{
"id" : 63,
"aid" : "551",
"license" : "0022",
"code" : "PRC",
"name" : "Hospital 2",
"STATION" : {
"Time" : "2020-05-12T04:06:42.66",
"burnunit" : {
"current" : 11
},
"obgyn" : {
"current" : 15
},
"vascular" : {
"current" : 18
},
"ventilators" : {
"current" : 22
},
"isolate" : false
}
},
{
"id" : 29,
"aid" : "3302",
"license" : "0066",
"code" : "TPA",
"name" : "Hospital 3"
}
]Hi Sorry--so I am trying to create columns that show the different types of hospital beds and the number count for them. So I am trying to get any of the objects in the tier for 'vascular' , ventilators, etc. Thanks so much again for your time.
+5Not 100% sure on what you are trying to get as your final output, but I'd probably use a JSONFragmenter with a JSON Query as follows, and choose to Flatten Query Result into Attributes
json[*]["name"]+json[*]["STATION"][*]That will get you this far
Hmm--I will try looking at your alternate path (sorry newbie here to FME). THANK YOU!
Hi Sorry--so I am trying to create columns that show the different types of hospital beds and the number count for them. So I am trying to get any of the objects in the tier for 'vascular' , ventilators, etc. Thanks so much again for your time.
OK. @ebygomm has provided an excellent answer. Please follow that.
+5Not 100% sure on what you are trying to get as your final output, but I'd probably use a JSONFragmenter with a JSON Query as follows, and choose to Flatten Query Result into Attributes
json[*]["name"]+json[*]["STATION"][*]That will get you this far
OK Got it! --thank you and the others for your help, really appreciate it.
+5OK. @ebygomm has provided an excellent answer. Please follow that.
Thank you!!
+18The JSONExtractor can be very useful in cases of a static schema.
To make sure you have the right syntax for the query, you can save the JSON from the Data Inspector into a file and temporarily add a JSON reader.
In the reader properties specify a query for your attribute of choice and grab/copy the JSON query the FME so kindly built for you.
Cancel adding the JSON reader and paste the JSON query syntax into the JSONExtractor.
+5The JSONExtractor can be very useful in cases of a static schema.
To make sure you have the right syntax for the query, you can save the JSON from the Data Inspector into a file and temporarily add a JSON reader.
In the reader properties specify a query for your attribute of choice and grab/copy the JSON query the FME so kindly built for you.
Cancel adding the JSON reader and paste the JSON query syntax into the JSONExtractor.
WOW -- what an excellent approach. We, the JSON newbies, thank you for this quick cheat to getting down to the node we need. Thank you!
