ADF Data Flow Parsing an array inside a row

Question

ADF Data Flow Parsing an array inside a row

Hadi Chahine 0

Hi,

I have a table rows from a postgres database. The table has the structure (id string, cat_data json, variants json)

cat_data has the structure ["a","b","c"]

variants has the structure {"624": {name: "Test"}, "631": {name: "Hello"}}

I'm using ADF data flows to parse those two strings. Here's what I tried to do for cat_data: enter image description here

This caused an error: "statusCode":400,"shortMessage":"DF-PARS-005 at Parse 'parse1'(Line 270/Col 8): Only arrays and array of structures allowed"

First request: Not sure on how to proceed on either cat_data, and variants. Ultimately I want both of those parsed.

Second request: Is there a way to parse those strings without knowing the structure (just as JSON.parse(string) would be used in js) ?

2 answers

Your answer

Answer 1

Harishga 6,000 Microsoft External Staff

Hi @Hadi Chahine ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

In this specific situation, you have the option to use the 'flatten transformation' to convert the JSON data into a tabular format, and subsequently, pass the JSON column.

For reference, kindly check this Thread:

https://learn.microsoft.com/en-us/answers/questions/545933/flatten-and-parsing-json-using-azure-data-flow

It appears that the data in cat_data is structured as an array, and this might be the root cause of the issue. Please consider passing the data in JSON format instead. If you still encounter the same error after making this change, please provide us with a sample of the data to help us further investigate the problem.

I hope this information helps you resolve the issue. Let me know if you have any further questions or concerns.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Hadi Chahine 0 Reputation points

2023-10-18T11:35:46.4233333+00:00

Hi @Harishga ,

Thanks for taking the time to reply to my question.

I declared that cat_data is an array of documents (JSON array). Is there a reason why that wouldn't work?

You're right however, I tried wrapping cat_data in a JSON object previously, and It worked. I'm trying to understand what is "array of documents" used for however.

Thanks again,

Hadi
Harishga 6,000 Reputation points Microsoft External Staff

2023-10-24T15:04:35.99+00:00

Hi Hadi Chahine
1.It seems that there are some missing records in the cat data, which is causing an error message to be thrown.
2. Arrays of documents are useful when you have a collection of related data that you want to store together. For example, if you have a collection of customer orders, you might store each order as a separate document in an array. This allows you to easily retrieve all orders for a particular customer or all orders within a specific date range.
Harishga 6,000 Reputation points Microsoft External Staff

2023-10-25T14:16:55.09+00:00

Hi Hadi Chahine,
Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Harishga 6,000 Reputation points Microsoft External Staff

2023-10-26T12:33:21.1633333+00:00

Hi Hadi Chahine,
Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Hadi Chahine 0 Reputation points

2023-10-27T07:13:12.98+00:00

Hi @Harishga ,

I doubled checked the data and it's valid. I tried something else however:

Nesting "cat_data" inside and then extracting it out. This worked perfectly. It seems to me that "Parse" can't parse an array or map, it can parse only objects. If "cat_data" isn't valid this would have raised an error too, right?

Thanks.
Hadi Chahine 0 Reputation points

2023-10-30T07:45:44.89+00:00

How would you like us to mark this answer? Write a new answer?
Harishga 6,000 Reputation points Microsoft External Staff

2023-10-30T08:51:46.6+00:00

Hi Hadi Chahine,
Thank you for your response. I have made an update to my answer.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Harishga 6,000 Microsoft External Staff

Hi Hadi Chahine,
Thank you for your response.
Yes, it seems that you are correct. Parse can only parse objects, not arrays or maps. If cat_data is not valid, Parse will raise an error. This is because Parse expects the input to be a JSON string, and JSON strings must represent objects. Arrays and maps can be represented as JSON strings, but they must be nested inside of an object.

Share via

ADF Data Flow Parsing an array inside a row

2 answers

Your answer