Share via

Iam receiving json data from restapi link, with multiple arrays how to pull data into SQL db

Venki Bimma 0 Reputation points
2023-03-24T12:22:56.7433333+00:00

@ShaikMaheer-MSFT data structure like this { } Json.

[ ] Links

{ } 0

{ } 1

[ ] Results
{} Entity - under again [] links
Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,565 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee
    2023-03-27T08:48:11.1966667+00:00

    Hi Venki Bimma,

    Thank you for posting query in Microsoft Q&A Platform.

    Kindly share your json correctly with some sample data to understand your requirement better.

    But, if I am not wrong, you want to take json data and load it into SQL table. Please correct me if I am wrong.

    You need to navigate to mapping tab of copy activity and map your columns from REST API json to SQL table columns as per your requirement.

    Please check below video, to understand it better.

    How to load Tabular and Hierarchical JSON data from Rest API to SQL Table | Azure Synapse | ADF

    Hope this helps. Please let me know how it goes or if any further queries.

    Please consider hitting Accept Answer button. Accepted answers help community as well.

    0 comments
  2. Venki Bimma 0 Reputation points
    2023-03-27T11:00:40.6866667+00:00

    This my json data @ShaikMaheer-MSFT {

    "links": [{

	"href": "https://api.sitescout.com/advertisers/114423/campaigns/2458195/stats/inventoryUnits?dateFrom=20230314&dateTo=20230314&pageSize=1000",

	"rel": "self"

}, {

	"href": "https://api.sitescout.com/advertisers/114423/campaigns/2458195/stats/inventoryUnits?dateTo=20230314&pageSize=1000&page=2&dateFrom=20230314",

	"rel": "next"

}],

"totalCount": 1039,

"pagination": {

	"page": 1,

	"pageSize": 1000

},

"results": [{

			"entity": {

				"links": [],

				"identifier": "richouses.com",

				"name": "richouses.com",

				"type": "domain",

				"impliedStatus": "online",

				"impliedBid": "0.56",

				"hasChildren": false

			},

			"stats": {

				"impressionsBid": 37,

				"impressionsWon": 32,

				"dataCost": 0.0208,

				"auctionsSpend": 0.017781400000000003,

				"totalSpend": 0.0385814,

				"nonBillableSpend": 0.0,

				"grossTotalSpend": 0.0385814,

				"clicks": 0,

				"offerClicks": 0,

				"clickThroughConversions": 0,

				"ctcRevenue": 0.0,

				"viewThroughConversions": 0,

				"vtcRevenue": 0.0,

				"totalConversions": 0,

				"revenue": 0.0,

				"videoStarted": 0,

				"videoFirstQuartileReached": 0,

				"videoMidpointReached": 0,

				"videoThirdQuartileReached": 0,

				"videoCompleted": 0,

				"videoSkipped": 0,

				"eligibleImpressions": 32,

				"viewableImpressions": 20,

				"measuredImpressions": 26,

				"viewabilitySpend": 0.038581400000000009,

				"advertiserSpend": 0.089724186046511625,

				"effectiveCPAOnAdvertiserSpend": 0.0,

				"effectiveCPMOnAdvertiserSpend": 2.8038808139534881,

				"effectiveCPCOnAdvertiserSpend": 0.0,

				"effectiveCPCVOnAdvertiserSpend": 0.0,

				"companionImpressions": 0,

				"companionClicks": 0,

				"companionConversions": 0,

				"companionOfferRevenue": 0.0,

				"winRate": 0.86486486486486491,

				"clickThroughRate": 0.0,

				"viewCVR": 0.0,

				"returnOnAdSpend": 0.0,

				"dataEffectiveCPM": 0.65,

				"mediaEffectiveCPM": 0.55566875,

				"revenuePerMille": 0.0,

				"clickRPM": 0.0,

				"grossTotalEffectiveCPM": 1.20566875,

				"measuredRate": 0.8125,

				"viewableRate": 0.76923076923076927,

				"viewableCPM": 1.9290700000000005,

				"totalCVRM": 0.0,

				"totalEffectiveCPM": 1.20566875,

				"conversions": 0,

				"viewthruConversions": 0,

				"clickthruRate": 0.0,

				"dataSpend": 0.0208,

				"auctionsWon": 32,

				"effectiveCPM": 0.55566875

			},

			"links": []

		}, {

			"entity": {

				"links": [],

				"identifier": "klif.com",

				"name": "klif.com",

				"type": "domain",

				"impliedStatus": "online",

				"impliedBid": "0.56",

				"hasChildren": false

			},

			"stats": {

				"impressionsBid": 1,

				"impressionsWon": 1,

				"dataCost": 0.00065,

				"auctionsSpend": 0.00055976000000000008,

				"totalSpend": 0.00120976,

				"nonBillableSpend": 0.0,

				"grossTotalSpend": 0.00120976,

				"clicks": 0,

				"offerClicks": 0,

				"clickThroughConversions": 0,

				"ctcRevenue": 0.0,

				"viewThroughConversions": 0,

				"vtcRevenue": 0.0,

				"totalConversions": 0,

				"revenue": 0.0,

				"videoStarted": 0,

				"videoFirstQuartileReached": 0,

				"videoMidpointReached": 0,

				"videoThirdQuartileReached": 0,

				"videoCompleted": 0,

				"videoSkipped": 0,

				"eligibleImpressions": 1,

				"viewableImpressions": 0,

				"measuredImpressions": 1,

				"viewabilitySpend": 0.00120976,

				"advertiserSpend": 0.0028133953488372089,

				"effectiveCPAOnAdvertiserSpend": 0.0,

				"effectiveCPMOnAdvertiserSpend": 2.8133953488372088,

				"effectiveCPCOnAdvertiserSpend": 0.0,

				"effectiveCPCVOnAdvertiserSpend": 0.0,

				"companionImpressions": 0,

				"companionClicks": 0,

				"companionConversions": 0,

				"companionOfferRevenue": 0.0,

				"winRate": 1.0,

				"clickThroughRate": 0.0,

				"viewCVR": 0.0,

				"returnOnAdSpend": 0.0,

				"dataEffectiveCPM": 0.65,

				"mediaEffectiveCPM": 0.55976,

				"revenuePerMille": 0.0,

				"clickRPM": 0.0,

				"grossTotalEffectiveCPM": 1.20976,

				"measuredRate": 1.0,

				"viewableRate": 0.0,

				"totalCVRM": 0.0,

				"totalEffectiveCPM": 1.20976,

				"conversions": 0,

				"viewthruConversions": 0,

				"clickthruRate": 0.0,

				"dataSpend": 0.00065,

				"auctionsWon": 1,

				"effectiveCPM": 0.55976

			},

			"links": []

		}, {

			"entity": {

				"links": [],

				"identifier": "sportingnews.com",

				"name": "sportingnews.com",

				"type": "domain",

				"impliedStatus": "online",

				"impliedBid": "0.56",

				"hasChildren": false

			},

			"stats": {

				"impressionsBid": 6,

				"impressionsWon": 1,

				"dataCost": 0.00065,

				"auctionsSpend": 0.00056000000000000006,

				"totalSpend": 0.00121,

				"nonBillableSpend": 0.0,

				"grossTotalSpend": 0.00121,

				"clicks": 0,

				"offerClicks": 0,

				"clickThroughConversions": 0,

				"ctcRevenue": 0.0,

				"viewThroughConversions": 0,

				"vtcRevenue": 0.0,

				"totalConversions": 0,

				"revenue": 0.0,

				"videoStarted": 0,

				"videoFirstQuartileReached": 0,

				"videoMidpointReached": 0,

				"videoThirdQuartileReached": 0,

				"videoCompleted": 0,

				"videoSkipped": 0,

				"eligibleImpressions": 1,

				"viewableImpressions": 1,

				"measuredImpressions": 1,

				"viewabilitySpend": 0.00121,

				"advertiserSpend": 0.0028139534883720924,

				"effectiveCPAOnAdvertiserSpend": 0.0,

				"effectiveCPMOnAdvertiserSpend": 2.8139534883720922,

				"effectiveCPCOnAdvertiserSpend": 0.0,

				"effectiveCPCVOnAdvertiserSpend": 0.0,

				"companionImpressions": 0,

				"companionClicks": 0,

				"companionConversions": 0,

				"companionOfferRevenue": 0.0,

				"winRate": 0.16666666666666666,

				"clickThroughRate": 0.0,

				"viewCVR": 0.0,

				"returnOnAdSpend": 0.0,

				"dataEffectiveCPM": 0.65,

				"mediaEffectiveCPM": 0.56,

				"revenuePerMille": 0.0,

				"clickRPM": 0.0,

				"grossTotalEffectiveCPM": 1.21,

				"measuredRate": 1.0,

				"viewableRate": 1.0,

				"viewableCPM": 1.21,

				"totalCVRM": 0.0,

				"totalEffectiveCPM": 1.2100000000000002,

				"conversions": 0,

				"viewthruConversions": 0,

				"clickthruRate": 0.0,

				"dataSpend": 0.00065,

				"auctionsWon": 1,

				"effectiveCPM": 0.56

			},

			"links": []

		}, {

			"entity": {

				"links": [],

				"identifier": "grizly.com",

				"name": "grizly.com",

				"type": "domain",

				"impliedStatus": "online",

				"impliedBid": "0.56",

				"hasChildren": false

			},

			"stats": {

				"impressionsBid": 131,

				"impressionsWon": 49,

				"dataCost": 0.031849999999999989,

				"auctionsSpend": 0.02742444,

				"totalSpend": 0.05927444,

				"nonBillableSpend": 0.0,

				"grossTotalSpend": 0.05927444,

				"clicks": 0,

				"offerClicks": 0,

				"clickThroughConversions": 0,

				"ctcRevenue": 0.0,

				"viewThroughConversions": 0,

				"vtcRevenue": 0.0,

				"totalConversions": 0,

				"revenue": 0.0,

				"videoStarted": 0,

				"videoFirstQuartileReached": 0,

				"videoMidpointReached": 0,

				"videoThirdQuartileReached": 0,

				"videoCompleted": 0,

				"videoSkipped": 0,

				"eligibleImpressions": 49,

				"viewableImpressions": 27,

				"measuredImpressions": 46,

				"viewabilitySpend": 0.059274439999999991,

				"advertiserSpend": 0.1378475348837209,

				"effectiveCPAOnAdvertiserSpend": 0.0,

				"effectiveCPMOnAdvertiserSpend": 2.8132149976269574,

				"effectiveCPCOnAdvertiserSpend": 0.0,

				"effectiveCPCVOnAdvertiserSpend": 0.0,

				"companionImpressions": 0,

				"companionClicks": 0,

				"companionConversions": 0,

				"companionOfferRevenue": 0.0,

				"winRate": 0.37404580152671757,

				"clickThroughRate": 0.0,

				"viewCVR": 0.0,

				"returnOnAdSpend": 0.0,

				"dataEffectiveCPM": 0.6499999999999998,

				"mediaEffectiveCPM": 0.55968244897959185,

				"revenuePerMille": 0.0,

				"clickRPM": 0.0,

				"grossTotalEffectiveCPM": 1.2096824489795919,

				"measuredRate": 0.93877551020408168,

				"viewableRate": 0.58695652173913049,

				"viewableCPM": 2.1953496296296291,

				"totalCVRM": 0.0,

				"totalEffectiveCPM": 1.2096824489795917,

				"conversions": 0,

				"viewthruConversions": 0,

				"clickthruRate": 0.0,

				"dataSpend": 0.031849999999999989,

				"auctionsWon": 49,

				"effectiveCPM": 0.55968244897959185

			}

		]
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.