Parsing JSON into separate columns

1

I am trying to get this data into 2 columns and can’t figure it out. It’s probably simple, but I am new and trying to see if EM can replace our ETL scripts.

One of the files we have to ingest looks like this, but with several thousand more. It’s poorly formed JSON in my opinion, but it’s what we have. I want to place the 2 numbers in each array in their own columns, machine_id and revision. When I parse, it puts it all in one.
That’s just for starters. Then I will need to query our db to see if the revision number is changed, if it has, I need to take the machine_id and do another call to get the specific data, then need to update our data. One thing at a time though. :slight_smile: Thanks.

{
	"response": {
		"id": "f6215f80-c0f0-013a-96ee-005056870204",
		"duration": "1.737s",
		"timestamp": "2022-05-28 15:16:20 -0500",
		"status": "ok",
		"messages": [],
		"api_version": 1
	},
	"machines": [
		[
			2500697,
			73
		],
		[
			2560764,
			43
		],
		[
			2649480,
			87
		],
		[
			4039882,
			34
		],
		[
			4186476,
			154
		]
	]
}
2

Hi @dbinott and welcome to the Community!

It’s not possible to parse this structure with just a single"Parse JSON" action. It needs some additional wrangling. You need to mark each row in the result of “Parse JSON” as #1 and #2 and then pivot them into two columns. See the example below.

parse-jagged-array.morph (4.5 KB)
json.txt (439 Bytes)

image

1 Like
3

Thanks for the quick reply.

wow, lots of work involved there. Now if the array was like
[
“machine”: 3445,
“rev”: 23
]
would it still be the same work? Or is it smart enough to know?

I have another issue I am trying to resolve in an xml feed. Can I post it as well? Here or new topic?

4

That's not a valid JSON. The JSON below would be parsed into two columns by the "Parse JSON" action alone.

[
  {
    "machine": 3445,
    "rev": 23
  }
]

Please post it in a new topic so that it would be easier to find for other people.

5

oops, right, forgot. has to be an new object.