Extraction of Fields from an Array and Creation of Columns While Maintaining the Structure, Replacing Missing Fields with Nul

[carlos_carreno]

Good afternoon, I need your help. The objective is the following process with different arrays from Bizevent:

1. Verify in each position if the field exists
2. If it exists, create the column with the information from that position
3. If it doesn't exist, create the column but assign it "null"

I've tried it this way:

data record(a = array("estado:bueno","aplica:si","salud:buena","animal:perro")),
record(a=array("estado:bueno","aplica:si","salud:buena","animal:perro","lugar:francia")),
record(a=array("estado:bueno","aplica:si"))
| fieldsAdd if(iAny(a[] == "animal:perro"),"kk")

I have tried to interact with the if statement so that if the condition is met, it takes the value from the position and creates the column. It would be something like this:

[krzysztof_hoja]

It looks like you are looking for "transpose" function which is not available. Currently the only way to achieve it is via workaround using DPL parsing.

To achieve view like your screenshot:

data record(a = array("estado:bueno","aplica:si","salud:buena","animal:perro")),
record(a=array("estado:bueno","aplica:si","salud:buena","animal:perro","lugar:francia")),
record(a=array("estado:bueno","aplica:si"))
| fieldsAdd a = toString(a)
| parse a, "'[' KVP{ '\"' LD:key ':' LD:value '\"' ', '? }:a ']'"
| fieldsFlatten a 
| fieldsKeep "a.*"

 Result look like:

[carlos_carreno]

What recommendation would you give when, in the field, I find that there is a data object that does not meet the key-value structure?

["Radicado: 13-02-20250213004567 ", 
 "Expediente Cliente: 9876543210 ", 
 "Johnathan Smith ", 
 "Zona Motor: NA ", 
 "Zona: Zona New York ", 
 "Agencia que radicó: Manhattan ", 
 "Usuario que radicó: jdoe ", 
 "Agencia origen cliente: Manhattan ", 
 "Asesor cliente: jdoe ", 
 "Radicado Visual: 1456789 ", 
 "Modalidad de crédito: Personal ", 
 "Línea de crédito: 410 - Personal Loan/International Clients ", 
 "Monto: $75,000.00 "]

As can be seen in the name, it does not meet the key-value condition.

thanks 

[krzysztof_hoja]

Do you have in mind

 "Johnathan Smith ", 

element?

How many such cases can you have in array?

How do you distinguish them from each other? Index?

And what would you like to see as a filed name / header in the table in such case?

[carlos_carreno]

How many of those cases can you have in the array?
Analyzing the data, they all come in that form.

How do you distinguish them from each other? Index?
I know they are names because I know the data, but the key should be the name.

And what would you like to see as a field name/header in the table in such case?
Name.

thanks

[krzysztof_hoja · Accepted solution]

What I had in mind was adjusting original content to format conforming with KVP.

Like in this example I append "Name: " in case ':' is missing

data json:"""[{"a": ["Radicado: 13-02-20250213004567 ", 
 "Expediente Cliente: 9876543210 ", 
 "Johnathan Smith ", 
 "Zona Motor: NA ", 
 "Zona: Zona New York ", 
 "Agencia que radicó: Manhattan ", 
 "Usuario que radicó: jdoe ", 
 "Agencia origen cliente: Manhattan ", 
 "Asesor cliente: jdoe ", 
 "Radicado Visual: 1456789 ", 
 "Modalidad de crédito: Personal ", 
 "Línea de crédito: 410 - Personal Loan/International Clients ", 
 "Monto: $75,000.00 "]
 }]"""
| fieldsAdd a = if(contains(a[],":"), a[], else: concat("Name: ", a[]))
| fieldsAdd a = toString(a)
| parse a, "'[' KVP{ '\"' LD:key ':' LD:value '\"' ', '? }:a ']'"
| fieldsFlatten a 
| fieldsKeep "a.*"

[carlos_carreno]

Thank you very much, you made it seem easy. 😁