code to unpack query array to tab-delimited data?

[sltfn]

Hello. I am very new to and still much in the dark about arrays. I am looking for efficient code to unpack the output from a query to lines of tab-delimited data (one record per line). Here is the code I've come up with so far. I'd guess there MUST be a more efficient approach.

on mouseup
## query constructed above
 put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
 put flattenArray( tOutputA) into flattened -- see flattenArray function below
end mouseup

function flattenArray A , d1, d2, T,  X
   if T is empty then
      if d1 is empty then put cr into d1
      if d2 is empty then put tab into d2
   end if
   if A is not an array then return T  & X & A & d1
   repeat for each key K in A
      put flattenArray( A[K],  d1, d2, T,  X & K & d2 )  into T
   end repeat
end flattenArray

This yields the queried records in this format (just one record of many shown here).

[CDB Table ID]    1    [CDB Record ID]    ATTRIBUTES    fopn
[CDB Table ID]    1    [CDB Record ID]    LAST_NAME    Jordan
[CDB Table ID]    1    [CDB Record ID]    STATUS    linnm
[CDB Table ID]    1    [CDB Record ID]    RANK    2
[CDB Table ID]    1    [CDB Record ID]    CITY    San Francisco
[CDB Table ID]    1    [CDB Record ID]    YEAR    2019
[CDB Table ID]    1    [CDB Record ID]    ROLE    5
[CDB Table ID]    1    [CDB Record ID]    NAME    Sam Jordan
[CDB Table ID]    1    [CDB Record ID]    DATE    2019-02-17
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbDateModified    1562677087
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbDateCreated    1562677087
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbRecordID    [CDB Record ID]
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbTableID    [CDB Table ID]
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbRecordVersion    1
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbTableName    stats
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbCloudSyncVersion    1

I then loop through the lines and remove the first two columns, then I filter out the lines with "cdb" keys not needed in my app as long as I preserve the record ID, and get a list of records like this…

[CDB Record ID]    ATTRIBUTES    fopn
[CDB Record ID]    LAST_NAME    Jordan
[CDB Record ID]    STATUS    linnm
[CDB Record ID]    RANK    2
[CDB Record ID]    CITY    San Francisco
[CDB Record ID]    YEAR    2019
[CDB Record ID]    ROLE    5
[CDB Record ID]    NAME    Sam Jordan
[CDB Record ID]    DATE    2019-02-17

Finally, I loop through the lines again and put the data for each record into the tab-delimited format I need, which is something roughly like this…

fopn    Jordan    linnm    2    San Francisco    2019    Sam Jordan    2019-02-17    [CDB Record ID]
ucpn    Smith    linnm    1    Los Angeles    2019    Susan Smith    2019-06-23    [CDB Record ID]

efrain.c and mark_talluto's suggestions for batch record creation and updates were VERY helpful. Thank you.

Does anybody have a simpler solution for this problem? Pretty sure there must be one (many). Thanks in advance.

[efrain.c]

Hi sltfn,

Here is one way to format the results of a batchQuery into a line delimited list of tab delimited records. I tried to make it as generic as possible so other users can use it if they'd like.

Code:

function flattenArray pBatchQueryA, pKeys, pDelimiter, pIncludeRecordID
    local tResults, tRecord
   
    repeat for each key xTableID in pBatchQueryA
         repeat for each key xIndex in pBatchQueryA[xTableID]
              repeat for each key xRecordID in pBatchQueryA[xTableID][xIndex]
                   repeat for each item xKey in pKeys
                        put pBatchQueryA[xTableID][xIndex][xRecordID][xKey] & pDelimiter after tRecord
                   end repeat
                   
                   if pIncludeRecordID then put xRecordID & pDelimiter after tRecord
                   
                   delete char -1 of tRecord --remove trailing delimiter
                   put tRecord & lf after tResults
                   put empty into tRecord
              end repeat
         end repeat
    end repeat
    delete char -1 of tResults --remove trailing lf
   
    return tResults
end flattenArray

A couple things to note:

- put flattenArray(tOutputA) into flattened will have to be changed to put flattenArray(tOutputA, "attributes,last_name,status,rank,city,year,name,date", "tab", true) into flattened.

- the code assumes that the batchQuery was only performed on one table. If this is not the case, you can pass another parameter after pBatchQueryA (pTableID) that will specify the table you are interested in. The outer repeat that uses xTableID will need to be removed and all references of xTableID will need to be changed to pTableID.

Hope this helps! Let me know if you have any questions.

[sltfn]

Thanks, efrain! This cut processing time from my inefficient example above by 60%. In the interim I had devised a method that cut processing time by 30+% but this is much better and also taught me some things.

[efrain.c]

Awesome! I'm glad to hear you're getting a hang of arrays. That's a great improvement. If you don't mind sharing, I'm interested to see how my suggestion compares in processing time.

[sltfn]

Oops. Hadn't seen your reply above so edited my reply. Your suggestion cut processing time by 60% from my stumbling initial approach; processes in 40% of the time. Thank you.