Question

Is it possible to create a list attribute for specific content of a JSON (in a single transformer)?

  • 5 August 2021
  • 2 replies
  • 26 views

Badge +3

Suppose you have some kind of JSON as follows:

{
   "students" : [
      {
         "id" : "01",
         "name" : "Tom",
         "lastname" : "Price",
         "hobbys" : [ "football", "hockey" ]
      },
      {
         "id" : "02",
         "name" : "Nick",
         "lastname" : "Thameson",
         "hobbys" : [ "reading", "gardening", "painting" ]
      }
   ]
}

and I would like to create a list attribute for only some specific content of the JSON. For example, lets say your only interested in only the name and lastname of the students. Then I would like to achieve a result like:

students{0}.name = Tom
students{0}.lastname = Price
students{1}.name = Nick
students{1}.lastname = Thameson

Is there some easy way to achieve that?

I am aware of the following transformers that can extract JSON data:

  1. JSONExtractor
  2. JSONFlattener
  3. JSONFragmenter

 

However, for the JSONExtractor I'm only familiar with the possibility to create attributes for (single) JSON objects or a specific JSON array element. Is there maybe some option in the JSONExtractor that I'm unaware of in which you can set list attributes for (specific elements of) a JSON array?

 

Using the JSONFlattener I find myself that the entire JSON is being flattened, so then I always find myself first using a JSONExtractor to extract specific content to flatten, exposing the (expected) flattened attributes, and removing all other undesired attributes using AttributeKeepers, -Removers or -Managers.

 

Somewhat similarly using an AttributeFragmenter I find myself fragmenting the JSON array of interest into individual features for each element of the JSON array, extracting the desired JSON attributes, and then building the desired list.

 

I guess there are probably some options to achieve my goal using JSONiq (a kind of xQuery for JSON instead of XML) in an XMLXQueryExtractor transformer, but unfortunately I only vaguely know about JSONiq (basically that it exists and looks like xQuery), and have only limited experience with xQuery for XML. 

Although I would be really interested to learn the possibilities of JSONiq for this purpose (maybe someone can share an example), where possible I would prefer to use 'non scripting' transformers to achieve my task. 

But then again I'm afraid that using 'conventional transformers' would require multiple steps, and in that case I would still prefer a direct solution in JSONiq.

 

Curious to hear if others have some experience/ideas on this front.

 

Kind regards,

 

Thijs


2 replies

Userlevel 1
Badge +11

Hi @thijsknapen​,

Thanks for your question! I got some suggestions from the team and it sounds like this isn't possible to do in just a single transformer. One of the best ways to go about it is as you've already mentioned using a JSONFlattener to get attributes, and then removing the unwanted attributes.

 

Some other suggestions from the team were to:

  • look into using the JSONFragmenter if what you need to flatten needs to generate multiple features. It gives you more control with JSON Query and fragmentation. This also gives you a separate feature per student object (creates a new feature per student object and the object into attributes)
  • OR use the JSONUpdater to remove the unwanted keys before using a JSONFlattener
  • when you've chosen your workflow, you could then wrap up the process as a custom transformer and reuse it as a single transformer

 

Hopefully that helps provide some more ideas!

Badge +3

Hi @jovitaatsafe​ ,

 

Thanks for the reply. I had another look at it myself, and I managed to make it work in a single transformer using JSONiq inside of an XMLXQueryExtractor transformer, using the following expression:

let $json_doc := fme:get-json-attribute("json_example")
let $students := jn:members($json_doc("students"))
return (
    fme:set-list-attribute("students{}.name", $students("name")),
    fme:set-list-attribute("students{}.lastname", $students("lastname"))
)

store_json_array_in_list_attributeThis solution was inspired on the reaction of takashi at this question (especially the count distinct elements expression): https://community.safe.com/s/question/0D54Q000080hL8C/count-elements-in-json-array.

Furthermore I found some helpful documentation in the following links:

http://cf.zorba-xquery.com.s3.amazonaws.com/doc/zorba-2.7.0/zorba/html/JSONforXQuery.html

https://nbviewer.jupyter.org/github/ghislainfourny/jsoniq-tutorial/blob/master/JSONiq-tutorial.ipynb

Reply