Use Json list as merge condition in synapse data flow

Question

Use Json list as merge condition in synapse data flow

Kumar, Arun 341

I am using a dataflow to do upsert on target delta table (source is also delta table). My source data have a json key value pair in one of the columns (transaction) and need to use that as Key Column. The data in transaction column is like below

{"ce":0.29,"cec":0.0002964954,"ceum":{"base_unit_id":"k2k0","from_base":"0.001","id":"k2l0","to_base":"1000","unit_abbreviation":"t","unit_class_id":"oonrg","unit_name":"metric tonne"},

In the $mergeCondition currently i use "id" as the key column. Since transaction column is a key:value pair as above, how can i use this as key?

User's image

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-12-21T19:20:02.84+00:00
Hello Kumar, Arun,

Are you looking to extract the value of the id key from the transaction column?

If yes, one simple approach is to extract the id using synapse/databricks and then ingest into ADF for further processing.

You can use the below spark SQL expression to extract the Id and then ingest into ADF for further processing.

SELECT get_json_object(transaction, '$.ceum.id') as id FROM YourSourceTable

I hope this helps.
Kumar, Arun 341 Reputation points

2023-12-21T19:34:07.4+00:00
@Bhargava-MSFT No. I want to use that transaction column which is a list/array as a Key Column in data flow. Can i use the same way as other Key columns which are string or integers?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-12-29T01:23:10.8433333+00:00

Hello,

If you want to use the entire "transaction" column as a Key Column in a data flow, it might not be directly possible because the Key Column expects a simple data type, such as a string or integer. Since the "transaction" column is a complex data type, you can't use it directly as a Key Column.

A potential workaround is to convert the "transaction" column into a string using the toString function within a derived Column transformation. This newly created string column can then be used as a key column in the sink settings for your upsert operation.
Kumar, Arun 341 Reputation points

2023-12-29T04:10:03.7166667+00:00

@Bhargava-MSFT Never mind. I flattened the transaction column through pyspark and did the delta merge through notebook. Thanks for your help

Answer accepted by question author

0 additional answers

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-12-21T19:20:02.84+00:00

Hello Kumar, Arun,

Are you looking to extract the value of the id key from the transaction column?

If yes, one simple approach is to extract the id using synapse/databricks and then ingest into ADF for further processing.

You can use the below spark SQL expression to extract the Id and then ingest into ADF for further processing.

SELECT get_json_object(transaction, '$.ceum.id') as id FROM YourSourceTable

I hope this helps.
Kumar, Arun 341 Reputation points

2023-12-21T19:34:07.4+00:00

@Bhargava-MSFT No. I want to use that transaction column which is a list/array as a Key Column in data flow. Can i use the same way as other Key columns which are string or integers?
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-12-29T01:23:10.8433333+00:00

Hello,

If you want to use the entire "transaction" column as a Key Column in a data flow, it might not be directly possible because the Key Column expects a simple data type, such as a string or integer. Since the "transaction" column is a complex data type, you can't use it directly as a Key Column.

A potential workaround is to convert the "transaction" column into a string using the toString function within a derived Column transformation. This newly created string column can then be used as a key column in the sink settings for your upsert operation.
Kumar, Arun 341 Reputation points

2023-12-29T04:10:03.7166667+00:00

@Bhargava-MSFT Never mind. I flattened the transaction column through pyspark and did the delta merge through notebook. Thanks for your help

Answer 1

Hello Kumar, Arun,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others", I'll repost your solution in case you'd like to "Accept" the answer.

Issue:How to use Json list as merge condition in synapse data flow

Using a dataflow to do upsert on target delta table (source is also delta table). Source data have a json key value pair in one of the columns (transaction) and need to use that as Key Column. The data in transaction column is like below

{"ce":0.29,"cec":0.0002964954,"ceum":{"base_unit_id":"k2k0","from_base":"0.001","id":"k2l0","to_base":"1000","unit_abbreviation":"t","unit_class_id":"oonrg","unit_name":"metric tonne"}

How to use that transaction column which is a list/array as a Key Column in data flow.

Solution:

One solution that resolved the issue was flattening the transaction column through PySpark and then performing a Delta merge using the notebook.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.

Share via

Use Json list as merge condition in synapse data flow

0 additional answers

Your answer