Extracting Array of fields but the field keys are in the JSON values of the key-value pair

[Andrew_L]

I'm attempting to parse a JSON response that has some of its fields hidden away in a nested array. What makes this confusing is the field name is a value in the key-value pair.

See below and note the 'fields' array. 

{
  "data": [
    {
      "id": "1",
      "name": "Fact Sheet Alpha",
      "fields": [
        {
          "name": "Fact Sheet Alias",
          "data": {
            "value": "Different Name",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "SyncPolicy",
          "data": {
            "value": "Outbound",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "Product Score",
          "data": {
            "value": "Moderate",
            "type": "StringValue"
          },
          "dataType": null
        }
      ],
      "createdAt": "2022-04-19T00:23:50.287331Z",
      "updatedAt": "2022-05-26T20:17:16.170390Z",
      "status": "ACTIVE",
      "qualitySealStatus": "BROKEN",
      "rev": 7
    },
    {
      "id": "2",
      "name": "Fact Sheet Bravo",
      "fields": [
        {
          "name": "Fact Sheet Alias",
          "data": {
            "value": "Yet another name",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "SyncPolicy",
          "data": {
            "value": "Inbound",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "Product Score",
          "data": {
            "value": "High",
            "type": "StringValue"
          },
          "dataType": null
        }
      ],
      "createdAt": "2022-04-19T00:23:50.287331Z",
      "updatedAt": "2022-05-26T20:17:16.170390Z",
      "status": "ACTIVE",
      "qualitySealStatus": "BROKEN",
      "rev": 7
    },
    {
      "id": "3",
      "name": "Fact Sheet Charlie",
      "fields": [
        {
          "name": "Fact Sheet Alias",
          "data": {
            "value": "Bob",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "SyncPolicy",
          "data": {
            "value": "Bidirectional",
            "type": "StringValue"
          },
          "dataType": null
        },
        {
          "name": "Product Score",
          "data": {
            "value": "Low",
            "type": "StringValue"
          },
          "dataType": null
        }
      ],
      "createdAt": "2022-04-19T00:23:50.287331Z",
      "updatedAt": "2022-05-26T20:17:16.170390Z",
      "status": "ACTIVE",
      "qualitySealStatus": "APPROVED",
      "rev": 3
    }
  ]
}

Conceptually, the data is 

• id
• name
• fields
  • Fact Sheet Alias
  • Sync Policy
  • Product Score
• etc...

But the JSON above shows that it's not so simple. 

I would like to simplify this into a flat schema to

• id
• name
• Fact Sheet Alias
• Sync Policy
• Product Score
• etc...

What I've Tried

The only way I can think to do this is by doing Apply to Each, filtering the array of 'fields' each time for each type of field name, and then consolidating back into a select. However, this is slow and taxing. 

The field names will remain consistent, but the order in which they appear in the 'fields' array can easily change (therefore capturing by array index is not feasible). 

Ideally I would like to do a Select that has the nested fields defined by expression, but since the nested field names are in the value, not the key, I do not know how to identify them. 

Is there a way to accomplish this without doing 'apply to each'? 

[lbendlin]

only if you hard code all pointers which is likely not what you want.

By the way, I don't see any data.  That JSON is "just" metadata.

[Andrew_L]

Bummer, but understandable. Thanks!

Also, much of the data in the example appears to be metadata, but it contains the data I need to sync two environments (e.g., name, id, sync policy, etc.)

[grantjenkins · Accepted solution]

Might want to change the solution 😉

See example below that gets what you want.

Steps:

1. Compose your raw data (just for this example)
2. Compose to get just the array of objects from your raw data
3. Select to get each of the properties/values in a flat structure

The overall flow is below. I'll go into detail on each step after.

Compose Array Data

This is just a copy of your source data to use in the example.

Compose Data

This gets just the data array that contains all your objects (removes the object wrapper).

outputs('Compose_Array_Data')?['data']

Select

This extracts out all the properties and values to build up the flat structure you were after. Note that the last three property names are extracted dynamically using the field array indexes 0, 1, 2, and corresponding value for each property, which gets around the ordering issue.

The first 7 properties use static names as they are already in a flat structure in the original JSON, and just use the following expression to get the value.

item()

The last 3 get the name dynamically using the following expressions.

item()?['fields'][0]?['name']
item()?['fields'][1]?['name']
item()?['fields'][2]?['name']

And corresponding values using the following expressions respectively.

item()?['fields'][0]?['data/value']
item()?['fields'][1]?['data/value']
item()?['fields'][2]?['data/value']

That way it doesn't matter what order they appear in the original JSON.

Sample output of the flat data based on your example data.

[
  {
    "Id": "1",
    "Name": "Fact Sheet Alpha",
    "Created At": "2022-04-19T00:23:50.287331Z",
    "Updated At": "2022-05-26T20:17:16.170390Z",
    "Status": "ACTIVE",
    "Quality Seal Status": "BROKEN",
    "Rev": 7,
    "Fact Sheet Alias": "Different Name",
    "SyncPolicy": "Outbound",
    "Product Score": "Moderate"
  },
  {
    "Id": "2",
    "Name": "Fact Sheet Bravo",
    "Created At": "2022-04-19T00:23:50.287331Z",
    "Updated At": "2022-05-26T20:17:16.170390Z",
    "Status": "ACTIVE",
    "Quality Seal Status": "BROKEN",
    "Rev": 7,
    "Fact Sheet Alias": "Yet another name",
    "SyncPolicy": "Inbound",
    "Product Score": "High"
  },
  {
    "Id": "3",
    "Name": "Fact Sheet Charlie",
    "Created At": "2022-04-19T00:23:50.287331Z",
    "Updated At": "2022-05-26T20:17:16.170390Z",
    "Status": "ACTIVE",
    "Quality Seal Status": "APPROVED",
    "Rev": 3,
    "Fact Sheet Alias": "Bob",
    "SyncPolicy": "Bidirectional",
    "Product Score": "Low"
  }
]

Optionally, if you wanted to use property names you can then use Parse JSON for each of the objects using the following flat schema.

{
    "type": "object",
    "properties": {
        "Id": {
            "type": "string"
        },
        "Name": {
            "type": "string"
        },
        "Fact Sheet Alias": {
            "type": "string"
        },
        "SyncPolicy": {
            "type": "string"
        },
        "Product Score": {
            "type": "string"
        },
        "Created At": {
            "type": "string"
        },
        "Updated At": {
            "type": "string"
        },
        "Status": {
            "type": "string"
        },
        "Quality Seal Status": {
            "type": "string"
        },
        "Rev": {
            "type": "integer"
        }
    }
}

[Andrew_L]

Genius and exactly the type of solution I was hoping for. Thank you!!