question

MayurTakle-9863 avatar image
0 Votes"
MayurTakle-9863 asked MayurTakle-9863 commented

How to store data from complex JSON in Azure DB using AZURE Synapse

Hi,

We are having a JSON structure where in 2 JSON object are nested. Please find below JSON example for your reference.
We need a way to store this data in the database.

result is a json array object which we can flattened in dataflow in different columns. But our target is to store the data in the below objects "782005" in the tables. you can see that the content in the all the objects is same. we don't want to create separate files/tables for the same. We want to store then in one table.


[
{
"count": 52,
"results": [
{
"key": "custom_fields",
"id": "782005"
},
{
"key": "custom_fields",
"id": "782015"
},
{
"key": "custom_fields",
"id": "782045"
}

 ],
 "custom_fields": {
   "782005": {
     "updated_at": "2021-02-24T11:43:19+00:00",
     "created_at": "2021-02-24T11:43:19+00:00",
     "name": "Client ID",
     "value_type": "string",
     "custom_field_set_id": "290485",
     "id": "782005"
   },
   "782015": {
     "updated_at": "2021-02-24T11:43:39+00:00",
     "created_at": "2021-02-24T11:43:39+00:00",
     "name": "ERP Status",
     "value_type": "single",
     "custom_field_set_id": "290485",
     "id": "782015"
   },
   "782045": {
     "updated_at": "2021-03-03T16:55:41+00:00",
     "created_at": "2021-02-24T11:47:00+00:00",
     "name": "Billing Office Name",
     "value_type": "single",
      "custom_field_set_id": "290485",
     "id": "782045"
   },

"meta": {
"count": 52,
"page_count": 3,
"page_number": 1,
"page_size": 20
}
}
]


Any suggestion.

Thanks
Mayur

azure-data-factoryazure-synapse-analyticsazure-maps
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Kiran-MSFT avatar image
0 Votes"
Kiran-MSFT answered MayurTakle-9863 commented

You can use the map datatype recently added to dataflows. There are several functions where you can convert the array of structures to a map value

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply Kiran.

The issue here we are facing, we need to get the values present in JSON objects not JSON array. In the above example we need to get the data inside

"782045": {
"updated_at": "2021-03-03T16:55:41+00:00",
"created_at": "2021-02-24T11:47:00+00:00",
"name": "Billing Office Name",
"value_type": "single",
"custom_field_set_id": "290485",
"id": "782045"
},

and the above json object is nested in custom_fields object
custom_fields : {

}

0 Votes 0 ·