Skip to main content
Solved

JSON Extraction help


louielou1234
Contributor
Forum|alt.badge.img+5

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

}

]

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

0684Q00000ArK60QAF.png

View original
Did this help you find an answer to your question?

12 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • Contributor
  • May 12, 2020

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
Influencer
  • May 12, 2020

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.


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 12, 2020
takashi wrote:

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
Influencer
  • May 12, 2020

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
Influencer
Forum|alt.badge.img+33
  • Influencer
  • Best Answer
  • May 12, 2020

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


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 12, 2020
takashi wrote:

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. 


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 12, 2020
ebygomm wrote:

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
Influencer
  • May 13, 2020
louielou1234 wrote:

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.


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 13, 2020
ebygomm wrote:

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.


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 13, 2020
takashi wrote:

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

Thank you!!


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • May 13, 2020

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.

 


louielou1234
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 14, 2020
itay wrote:

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings