Solved

JSON Extraction help

L
Badge

Hi, 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."

}

]

icon

Best answer by ebygomm 12 May 2020, 16:17

View original

12 replies

erik_jan
Rank
Userlevel 2
Badge +16

This 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.

takashi
Rank
Userlevel 2
Badge +17

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.

L
Badge

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.

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!

takashi
Rank
Userlevel 2
Badge +17

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"
   }
]
ebygomm
Rank
Userlevel 1
Badge +21

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

0684Q00000ArK60QAF.png

L
Badge

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"
   }
]

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. 

L
Badge

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

0684Q00000ArK60QAF.png

Hmm--I will try looking at your alternate path (sorry newbie here to FME).  THANK YOU!

takashi
Rank
Userlevel 2
Badge +17

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.

L
Badge

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

0684Q00000ArK60QAF.png

OK Got it! --thank you and the others for your help, really appreciate it.

L
Badge

OK. @ebygomm has provided an excellent answer. Please follow that.

Thank you!!

itay
Rank
Badge +16

The 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.

 

L
Badge

The 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!

Reply


Community Stats

28,681
Posts
109,411
Replies
36,542
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.