topic Re: Extraction of Fields from an Array and Creation of Columns While Maintaining the Structure, Replacing Missing Fields with Nul in DQL

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

carlos_carreno — Wed, 05 Feb 2025 19:09:04 GMT

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:

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

krzysztof_hoja — Thu, 13 Feb 2025 08:14:33 GMT

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:

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

carlos_carreno — Thu, 13 Feb 2025 10:39:08 GMT

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

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

krzysztof_hoja — Thu, 13 Feb 2025 10:50:37 GMT

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?

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

carlos_carreno — Thu, 13 Feb 2025 11:24:44 GMT

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

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

krzysztof_hoja — Thu, 13 Feb 2025 11:34:41 GMT

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.*"

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

carlos_carreno — Thu, 13 Feb 2025 11:42:41 GMT

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