Accessing a value within values type arrays

[elaaadani]

Hello to all ! I would like to ask you a question. I have this information in a Data object. I need to save the variable created_time and on the other hand, I need to save from field_data, the values of rut_(chile) and email.

{
   "data":[
      {
         "created_time":"2021-11-22T08:21:16+0000",
         "id":"1045758509590397",
         "field_data":[
            {
               "name":"nombre_completo",
               "values":[
                  "xxxxxxxx"
               ]
            },
            {
               "name":"servicio_que_deseas_",
               "values":[
                  "kit_filtros_+_cambio_de_aceite"
               ]
            },
            {
               "name":"sucursal",
               "values":[
                  "santiago"
               ]
            },
            {
               "name":"número_de_teléfono",
               "values":[
                  "+56912345678"
               ]
            },
            {
               "name":"rut_(chile)",
               "values":[
                  "12345678"
               ]
            },
            {
               "name":"selecciona_tu_vehículo",
               "values":[
                  "actros"
               ]
            },
            {
               "name":"correo_electrónico",
               "values":[
                  "test@gmail.com""
]
}
]
},
{
""created_time"":""2021-11-22T01":"49":44+0000",
""id"":"2741196499508739",
""field_data"":[
{
""name"":""nombre_completo"",
""values"":[
""xxxxxxxxxxx""
]
},
{
""name"":""servicio_que_deseas_"",
""values"":[
""kit_filtros_+_cambio_de_aceite""
]
},
{
""name"":""sucursal"",
""values"":[
""santiago""
]
},
{
""name"":""número_de_teléfono"",
""values"":[
"+569123456789"
]
},
{
""name"":""rut_(chile)"",
""values"":[
"123456789"
]
},
{
""name"":""selecciona_tu_vehículo"",
""values"":[
""atego""
]
},
{
""name"":""correo_electrónico"",
""values"":[
"j.escamilla3569@gmail.com"
]
}
]
}
],
""paging"":{
""cursors"":{
""before"":"QVFIUkdsakIxMXNVcXl0NVQyYUxlRlIyaHNjVlEzaHp6TVExUVQ5bHBQRnlkZAGNnTXdDdlhrc1pNdFBtVXg2WnhBeXljWE53OVF6bFVLbTF0c1FjVXMxU2Nn",
""after"":"QVFIUkxqNE84RTE3dURLREhqNUZAnRlFaOGVRclhZAWGc3RkdVSmtNQS1Md3lZATjF0TG9UcnBCUHhlU012a1R6VTFzZAW9welVCRThKWVBCNy1hblZAaaU9Sd1RB"\"
}
}
}"

For the field data, I have saved the Data information in an Array and for each record, it is saved in an Array. But I don't know how I can access the values I need to save them in a variable. Thanks in advance !

[mahoneypat · Accepted solution]

Is this your desired output (see Outputs section)?

If so, I got an invalid JSON with what you posted, so I converted it to this in a compose step

{
"data": [
{
"created_time": "2021-11-22T08:21:16+0000",
"id": "1045758509590397",
"field_data": [
{
"name": "nombre_completo",
"values": [
"xxxxxxxx"
]
},
{
"name": "servicio_que_deseas_",
"values": [
"kit_filtros_+_cambio_de_aceite"
]
},
{
"name": "sucursal",
"values": [
"santiago"
]
},
{
"name": "número_de_teléfono",
"values": [
"+56912345678"
]
},
{
"name": "rut_(chile)",
"values": [
"12345678"
]
},
{
"name": "selecciona_tu_vehículo",
"values": [
"actros"
]
},
{
"name": "correo_electrónico",
"values": [
"test@gmail.com"
]
}
]
},
{
"created_time": "2021-11-22T01:49:44+0000",
"id": "2741196499508739",
"field_data": [
{
"name": "nombre_completo",
"values": [
"xxxxxxxxxxx"
]
},
{
"name": "servicio_que_deseas_",
"values": [
"kit_filtros_+_cambio_de_aceite"
]
},
{
"name": "sucursal",
"values": [
"santiago"
]
},
{
"name": "número_de_teléfono",
"values": [
"+569123456789"
]
},
{
"name": "rut_(chile)",
"values": [
"123456789"
]
},
{
"name": "selecciona_tu_vehículo",
"values": [
"atego"
]
},
{
"name": "correo_electrónico",
"values": [
"j.escamilla3569@gmail.com"
]
}
]
}
],
"paging": {
"cursors": {
"before": "QVFIUkdsakIxMXNVcXl0NVQyYUxlRlIyaHNjVlEzaHp6TVExUVQ5bHBQRnlkZAGNnTXdDdlhrc1pNdFBtVXg2WnhBeXljWE53OVF6bFVLbTF0c1FjVXMxU2Nn",
"after": "QVFIUkxqNE84RTE3dURLREhqNUZAnRlFaOGVRclhZAWGc3RkdVSmtNQS1Md3lZATjF0TG9UcnBCUHhlU012a1R6VTFzZAW9welVCRThKWVBCNy1hblZAaaU9Sd1RB"
}
}
}

I then added a Select step with these expressions

item()?['created_time']

join(item()?['field_data']?[6]?['values'], ', ')

join(item()?['field_data']?[4]?['values'], ', ')

Counting starts at 0 so the above join expressions access the 5th and 7th values arrays.  If you data always has the same structure, this will work.

And then added a Create HTML table step with the output of the select.

Pat

[elaaadani]

@mahoneypat Thank you very much for your help ! Can I put that HTML table as columns and rows in a table in excel ? Or should I save the information in variables to be able to do those?

[mahoneypat]

FYI that this all might be easier with Power BI depending on what you plan to do with the data.  In any case, you can use the Add a row to an Excel table action, but you'll need to have an Excel file already created with a table already created.  You don't need to go through the HTML table step and just do an Apply to each on the output of the Select action (with the add a row to Excel in there).

Pat

[elaaadani]

@mahoneypat  thank you. 

My last question. Is there a way to do this without having to use the Join function?

I am trying to do it in a simpler way. I get the name and the value.

I want to leave each name as a fixed column in excel. Then it depends on the name, which column the value goes in, but I am stuck on this part. I don't know how to proceed to make it work correctly with what I need.
Thanks for your help !

[mahoneypat]

Not sure what you mean, but, since the values are in an array and you want a text output, the best way is to use the join function.

Pat