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

Question

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

Answer 1

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