Skip to main content
  • EN

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

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

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

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

    Reply


    Community Stats

    30,351
    Posts
    115,336
    Replies
    38,078
    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.