ADF to search the elements in multiple json files

Question

ADF to search the elements in multiple json files

David Vinh 0

I have multiple json files and want to search elements in Json files. If found a match, then copy metadata{...} to another storage as json format. I am not able to transform into a sink.

The filter search for record.metada.markings.document.code == 'B' && metadata.publicationDate.date >= '2020-01-01'

{

"record": {

    "ns": "mra",
        "type": "REC",
        "record": {
            "ns": "rec",
            "id": "sample03.json",
            "markings": {
                "document": {
                    "ns": "rec",
                    "code": "B"
                }
            }
		},	
        "metadata": {
			"an": "0002641",
			"markings": {
				"document": {
                "distribution": {
					"code": "B"
					}
				}

				
			},
			"publicationDate": {
				"date": "2024-01-01"
			}
		}

		

		
	}

   
}

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-21T05:11:14.5066667+00:00
@David Vinh

Thanks for reaching out to Microsoft Q&A.

To search through multiple JSON files in Azure Data Factory (ADF) and copy those that match specific criteria, you can follow these steps:

Steps to Implement the Solution

Ensure your JSON files are accessible, either in Azure Blob Storage or another supported data source.

In ADF, create a new Data Flow. This will allow you to perform transformations and filtering on your data.

Add a source transformation to read your JSON files. Configure it to point to the location of your JSON files.

Add a Filter transformation to apply your conditions:

For the condition record.record.markings.document == 'B', you can use:
record.record.markings.document == 'B'

For the publication date condition, use:
toDate(metadata['publication date:'].date) >= toDate('2020-01-01')

Combine these conditions with an && operator.

After filtering, add a Sink transformation to specify where to copy the matching JSON files.

Choose the appropriate sink type (e.g., Azure Blob Storage) and configure it to write the entire JSON file.:

Use the debug mode in ADF to test your Data Flow. Ensure that it correctly filters and copies the files as expected

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-22T07:06:15.4166667+00:00

@David Vinh We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
LearningAzure 0 Reputation points

2024-10-23T13:52:03.15+00:00

Phemanth, Sorry I put the comment in the answer section. If Json files found then copy record{metadata{....}} to another storage.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-24T06:39:49.2966667+00:00

@David Vinh You can use a combination of the Set variable and Lookup activities to filter out the metadata section from the JSON file and then copy it to the sink.

Here's how you can do it:

Set variable: In the Set variable activity, use the New item option to create a new variable and name it metadata.

Lookup: In the Lookup activity, use the in operator to specify the path to the metadata section within the JSON file. This will extract the metadata section as a separate object.

Assign variable: Use the Assign activity to assign the output of the Lookup activity to the metadata variable.

Copy to sink: In the Copy data activity, set the metadata variable as the source and the sink to the desired destination.

1 answer

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-21T05:11:14.5066667+00:00

@David Vinh

Thanks for reaching out to Microsoft Q&A.

To search through multiple JSON files in Azure Data Factory (ADF) and copy those that match specific criteria, you can follow these steps:

Steps to Implement the Solution

Ensure your JSON files are accessible, either in Azure Blob Storage or another supported data source.

In ADF, create a new Data Flow. This will allow you to perform transformations and filtering on your data.

Add a source transformation to read your JSON files. Configure it to point to the location of your JSON files.

Add a Filter transformation to apply your conditions:

For the condition record.record.markings.document == 'B', you can use:
record.record.markings.document == 'B'

For the publication date condition, use:
toDate(metadata['publication date:'].date) >= toDate('2020-01-01')

Combine these conditions with an && operator.

After filtering, add a Sink transformation to specify where to copy the matching JSON files.

Choose the appropriate sink type (e.g., Azure Blob Storage) and configure it to write the entire JSON file.:

Use the debug mode in ADF to test your Data Flow. Ensure that it correctly filters and copies the files as expected

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-22T07:06:15.4166667+00:00

@David Vinh We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
LearningAzure 0 Reputation points

2024-10-23T13:52:03.15+00:00

Phemanth, Sorry I put the comment in the answer section. If Json files found then copy record{metadata{....}} to another storage.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-24T06:39:49.2966667+00:00

@David Vinh You can use a combination of the Set variable and Lookup activities to filter out the metadata section from the JSON file and then copy it to the sink.

Here's how you can do it:

Set variable: In the Set variable activity, use the New item option to create a new variable and name it metadata.

Lookup: In the Lookup activity, use the in operator to specify the path to the metadata section within the JSON file. This will extract the metadata section as a separate object.

Assign variable: Use the Assign activity to assign the output of the Lookup activity to the metadata variable.

Copy to sink: In the Copy data activity, set the metadata variable as the source and the sink to the desired destination.

Answer 1

David Vinh 0

dia1

The source dataset has one of the Json files:

dia2

Match the condition requirement in the If Condition activity. then copy the section metadata{...} to sink. It works but it copied the entire JSON file to the sink. I don't know how to copy metadata parent, including children nodes to sink. I want the result json file only have record{ metadata } only in Sink see image:

dia3

Share via

ADF to search the elements in multiple json files

1 answer

Your answer