Skip to main content
  • EN

Hello,

I have a json response body that I am trying to get into a table. Using the JSONFragmenter I have gotten each record to populate as a different fragment. I have a long string with all the key value pairs. See below,

 

{"ActualFinishDate":"2023-06-01T18:00:00Z","DateSubmitTo":"2022-05-02T13:37:46Z","Description":"FLT - Service Requirement","InitiateDate":"2022-05-02T13:37:46Z","StatusDescription":"Completed","WOCost":0,"WorkOrderId":"3006845","Comments":null,"Instructions":null}

 

Now what I would like to do is create a table that would have each of the value keys as an attribute column and to have that tabled filled out with the corresponding data.

 

What would my next steps be?

 

 

 

 

 

What I chose to do is add a JSONExtractor. Then under the Extract Queries configuration box I created each of the attributes that I wanted to see in the Target Attribute column. Then in the JSON Query the JSON Query is simply json["Key"]. example

Hello @abysmal-ukulele​ 

The JSONFragmenter & JSONExtractor combination is definitely one option.

FYI the JSONFragmenter also has options to flatten query results into attributes within the transformer itself. Once this parameter enabled, the transformer will automatically flatten any key value pairs into attribute names and values. Note that you do have to manually enter the key names to expose them either within the Attributes to Expose parameter of the JSONFragmenter or use an AttributeExposer.

Options to flatten results of JSON query within JSONFragmenter

Hello @abysmal-ukulele​ 

The JSONFragmenter & JSONExtractor combination is definitely one option.

FYI the JSONFragmenter also has options to flatten query results into attributes within the transformer itself. Once this parameter enabled, the transformer will automatically flatten any key value pairs into attribute names and values. Note that you do have to manually enter the key names to expose them either within the Attributes to Expose parameter of the JSONFragmenter or use an AttributeExposer.

Options to flatten results of JSON query within JSONFragmenter

Hi @debbiatsafe​ Thanks for that. I swear I had tried to do the very thing that you're picturing but it didn't work. I couldn't figure out where I had gone wrong. I tried different syntax with no success. Of course, once I read your post the transformer behaved.

 

If anyone else is struggling with the JSONFragmenter it is worth mentioning that each entry within the 'Attributes to Expose' fields do not require quotations. The entries need to reflect the Key Value exactly. now 

Thanks for steering me in the right direction.

Hello @abysmal-ukulele​ 

The JSONFragmenter & JSONExtractor combination is definitely one option.

FYI the JSONFragmenter also has options to flatten query results into attributes within the transformer itself. Once this parameter enabled, the transformer will automatically flatten any key value pairs into attribute names and values. Note that you do have to manually enter the key names to expose them either within the Attributes to Expose parameter of the JSONFragmenter or use an AttributeExposer.

Options to flatten results of JSON query within JSONFragmenter

So here is an instance of the JSONFragmenter not working as expected.

First see how I configured the transformer in picture 1. pic1 

You can see the JSON from the _http_response_body in picture 2.pic2 

In picture 3 you can see the very last column "comments" has not been populated.

pic3 

What did I do wrong? @debbiatsafe​ Maybe you'll have some insight?

Hello @abysmal-ukulele​ 

The JSONFragmenter & JSONExtractor combination is definitely one option.

FYI the JSONFragmenter also has options to flatten query results into attributes within the transformer itself. Once this parameter enabled, the transformer will automatically flatten any key value pairs into attribute names and values. Note that you do have to manually enter the key names to expose them either within the Attributes to Expose parameter of the JSONFragmenter or use an AttributeExposer.

Options to flatten results of JSON query within JSONFragmenter

Okay @debbiatsafe​ last message about this I swear...

So I understand where I went wrong. If I had to categorize it I would say that I didn't quite understand when or what exactly the JSONFragmenter was flattening. I thought the JSONFragmenter was flattening the input JSON text. In my post above, I have shown the text arriving into the jsonFragmenter (pic 2).

 

The JSONFragmenter is flattenting the content after it has been processed and is found in the _response_body attribute. The JSON Query that I created in my previous post: jsont"Comments"]

  • o"Comments"] removed the key value pair and left only the content associated with a comment. Without the key value the flattener did not work. So when creating a JSON query to be flattened less is more, in a sense. The json query one creates should maintain the key value pairs. So the json query: jsony"Comments"]
  • , is a query that works since it maintains key value pairs.

     

    Hopefully this explanation will help someone else. thatswhyUnderstanding what is being flattened and at what stage of processing is helpful when using the transformer.

    • Reply


    Community Stats

    30,162
    Posts
    114,483
    Replies
    37,809
    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.