Skip to main content
Solved

JSON: extract object key as attribute value

s.jager
Influencer
Forum|alt.badge.img+16

Hi all,

I am trying to read a json file, that contains a collection of objects. It is NOT an array, and the objects have no name, they have just a number. It’s that number I am interested in, but I can’t seem to find a way to get it exposed. My Json looks like this:

{
  "1": {
    "name": "msg_status",
    "data": {
      <snip>
    },
    "port": 4,
    "timestamp": 1717497996,
    "timestamp_parsed": "2024-06-04 12:46:36"
  },
  "2": {
    "name": "msg_ublox_location",
    "data": {
      <snip>
    },
    "port": 2,
    "timestamp": 1717498735,
    "timestamp_parsed": "2024-06-04 12:58:55"
  },
  "3": {
    "name": "msg_ublox_location_short",
    "data": {
      <snip>
    },
    "port": 13,
    "timestamp": 1717498741,
    "timestamp_parsed": "2024-06-04 12:59:01"
  },
  "4": {
    "name": "msg_ublox_location",
    "data": {
      <snip>
    },
    "port": 2,
    "timestamp": 1717499651,
    "timestamp_parsed": "2024-06-04 13:14:11"
  }
}

and so on, for a couple of thousand objects per file. The data{} part contains different attributes depending on the name attribute, that’s not important for this question because I can get those out without a problem. 

My problem is the numbers 1 to 4 in this short example: I can’t seem get those into an attribute per feature. If I use a JSON Reader, with json[*] as JSON Query, I get every single object nicely, with all the attributes. But I should use the number that defines the object as well, since the order of these objects is important - and I can use the numbering for that.

If I use json as JSON Query, II’ll get a single feature with thousands of attributes like this:

1.port
1.timestamp
1.timestamp_parsed
2.name
2.data.<snip>
2.port
2.timestamp
2.timestamp_parsed
etc….

In a relatively small file with only a week’s worth of data, this can go up to 4530.timestamp, and typically the data is harvested once every 3 weeks. I suppose I could use an AttributeExploder on this single feature, then process the attributename/value pairs, but that feels clumsy and a workaround. I’d much rather use a clever JSON Query, if that is possible.

I could also use a counter, but given the fact that typically I’d receive multiple files of data at a time, and the numbers are only unique per separate file, that also does not feel right. But I can’t seem to find anything about this type of Json, where the key-name is actually a value one needs.

 

Anybody have any ideas?

 

TIA,

Stefan

Best answer by debbiatsafe

Hello @s.jager 

One alternative is to use JSONiq in the XMLXQueryExploder transformer with the jn:keys function to find the key/names of the root objects in your file. Then output one feature per root JSON object along with the numbered key in a new attribute (eg.{“order”:1,“name":"msg_status","data":{<snip>},...}). Use a JSONFlattener to flatten the output JSON and expose the flattened attributes. Attached is an example workspace demonstrating this approach.

The XMLXQueryExploder transformer does support JSON only if the JSON is read in as an attribute in the JSONiq expression. So your input JSON file needs to be read into the workspace using the text line reader with the Read Whole File at Once parameter set to ‘Yes’ or an AttributeFileReader.

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

    4 replies

    s.jager
    Influencer
    Forum|alt.badge.img+16
    • s.jagerInfluencer
    • Author
    • Influencer
    • June 20, 2024

    Update: I can get it to work using an AttributeExploder, some parsing of the resulting features, then aggregating them back into the original objects, but that competely kills the performance. Using that workflow takes 15 minutes to obtain 4530 objects, using json[*] takes 3 seconds to obtain the same 4530 objects (but without the ID-number). Since ultimately this needs to become a Workspace App for a certain groupd of my end users, that is an unnaceptable loss of performance.

    So if anyone has another idea, I’m open to suggestions….

    danilo_fme
    1 person upvoted this
    • danilo_fme
      danilo_fme
    debbiatsafe
    Safer
    Forum|alt.badge.img+20
    • debbiatsafeSafer
    • Safer
    • Best Answer
    • June 20, 2024

    Hello @s.jager 

    One alternative is to use JSONiq in the XMLXQueryExploder transformer with the jn:keys function to find the key/names of the root objects in your file. Then output one feature per root JSON object along with the numbered key in a new attribute (eg.{“order”:1,“name":"msg_status","data":{<snip>},...}). Use a JSONFlattener to flatten the output JSON and expose the flattened attributes. Attached is an example workspace demonstrating this approach.

    The XMLXQueryExploder transformer does support JSON only if the JSON is read in as an attribute in the JSONiq expression. So your input JSON file needs to be read into the workspace using the text line reader with the Read Whole File at Once parameter set to ‘Yes’ or an AttributeFileReader.

    1 Attachments
    nielsgerrits
    geomancer
    danilo_fme
    5 people upvoted this
    • nielsgerrits
      nielsgerrits
    • geomancer
      geomancer
    • danilo_fme
      danilo_fme
    • s.jager
      s.jager
    • mark2atsafe
      mark2atsafe
    s.jager
    Influencer
    Forum|alt.badge.img+16
    • s.jagerInfluencer
    • Author
    • Influencer
    • June 21, 2024

    Hi Debbi,

    Thank you, that works! With pretty good performance too, so ‘ll start using that.

     

    A tip though: I’d never have thought to check an XML-transformer when dealing with Json, so that may be something to do for a future release: add this sort of functionality to Json-transformers...

    debbiatsafe
    danilo_fme
    2 people upvoted this
    • debbiatsafe
      debbiatsafe
    • danilo_fme
      danilo_fme
    debbiatsafe
    Safer
    Forum|alt.badge.img+20
    • debbiatsafeSafer
    • Safer
    • June 21, 2024
    s.jager wrote:

    Hi Debbi,

    Thank you, that works! With pretty good performance too, so ‘ll start using that.

     

    A tip though: I’d never have thought to check an XML-transformer when dealing with Json, so that may be something to do for a future release: add this sort of functionality to Json-transformers...

     

    Hi @s.jager 

    JSONiq is the JSON extension to XQuery, which is why JSON can be worked with in an XMLXQuery- transformer.

    JSONiq is also accepted in the JSONTemplater and in the JSONUpdater if the Value Type parameter is “JSON/XQuery”.

    For example, you can use the following JSONiq expression in the JSONTemplater root template, then use a JSONFragmenter to fragment into features to get the same output:

    let $d := {
  let $doc := {fme:get-json-attribute("text_line_data")}
  for $record in jn:keys($doc)
  return {|
    {"order":xs:int($record)},
    {$doc($record)}
  |}
}
return [$d]

    I chose the XMLXQueryExploder in my initial response because it allowed for a slightly simpler JSONiq expression. Also because it is an interesting use case for the XMLXQuery-transformers.

    danilo_fme
    s.jager
    2 people upvoted this
    • danilo_fme
      danilo_fme
    • s.jager
      s.jager

    Reply


    Helpful Members This Week

    Community Stats

    31,105
    Posts
    117,929
    Replies
    38,858
    Members

    Latest FME

    Terms & ConditionsCookie settings

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