Search Values of Array in "One Go"

[Mark Waddingham]

On 2017-08-23 17:24, Sannyasin Brahmanathaswami via use-livecode wrote:
> We use these arrays with media metadata that is extracted from our
> database of media metadata. The dbase was originally designed for lots
> of columns so that "there is nothing we cannot know about a media
> item" incorporating DCIM columns and also W3C media metadata
> initiative's recommended properties.  In actually usage for any give
> media item only a small subset of columns containe any data. but we do
> use almost all the columns at one time or another.  There are no
> blobs, so this is very light weight data.
> 
> Anyway… this results in arrays which contain 1 top level key per
> record and that element contains another 40 or so keys for the columns
> of the database. most of which are empty/unused. So these arrays are
> never "heavy" in terms of bytes.

I think you may have already found a solution to what you want to do... 
However, you'll find my version below.

The arrayFindElementsContainingString command is a generic handler which 
will search all elements of an array recursively for containing pNeedle.

It returns a sequence (numerically indexed array) of array paths 
containing the needle.

The handler exploits the 'dynamic path' lookup feature of arrays. If you 
construct a sequence (numerically keyed array starting at 1) array of 
strings, you can use that sequence in [ ... ], and the engine will treat 
it as a sequence of keys. e.g.

   put "foo" into tPath[1]
   put "baz" into tPath[2]
   put tArrayA[tPath] into field "Result"

Here the last line is equivalent to tArrayA["foo"]["baz"].

(Note this all works on arrays and strings, the test handler uses Json 
as a convenient way to represent an array in a field!)

-------- TEST HANDLER
-- Requires a field "Results"
-- Requires a field "Json" containing the JSON form of the array to be 
searched
-- Requires a field "Needle" containing the string to search for
-- Requires a field "Results"

/* Test the array finding */
command testArrayFind pNeedle
    /* Clear the results field */
    put empty into field "Results"

    /* Get an array which is JSON encoded in field "Json" as an array */
    local tArray
    put JSONImport(field "Json") into tArray

    /* Search the array for the needle, fetched from field "Needle" */
    arrayFindElementsContainingString tArray, field "Needle"

    /* Iterate over each found path */
    repeat for each element tFoundPath in it
       /* Each path is a numerically keyed array (sequence), these can be 
used
       * directly as array indicies, the engine will iterate through the 
elements of
       * the sequence to get the keys to use */
       local tValue
       put tArray[tFoundPath] into tValue

       /* Create a slash delimited list of keys (note - this won't work 
if any of the
       * keys in the path contain '/'!). */
       combine tFoundPath with "/"

       /* Show the list of results in field "Results" in the form:
       * slash delimited path : value
       */
       put tFoundPath & ":" & tValue & return after field "Results"
    end repeat
end testArrayFind

-------- LIBRARY FUNCTIONALITY

/* Search all elements of pArray for containment of pNeedle. The
  * command returns a numerically keyed array in 'it', each element of
  * which is an array path to an element containing pNeedle. */
command arrayFindElementsContainingString pArray, pNeedle
    /* We expect an array for pArray */
    if pArray is not an array and pArray is not empty then
       throw "pArray must be an array"
    end if

    /* We expect a string for pNeedle */
    if pNeedle is an array then
       throw "pNeedle must be a string"
    end if

    /* As arrays are recursive, we pass through the current base path
     * for the sub-array being searched to an auxillary private command 
*/
    local tBasePath
    put empty into tBasePath

    /* The auxillary private command accumulates complete paths in
     * tPaths, which is a numerically keyed array (sequence). */
    local tPaths
    put empty into tPaths
    _arrayFindElementsContainingString pArray, pNeedle, tBasePath, tPaths

    /* Using 'return for value' returns the value in 'it' in the caller. 
*/
    return tPaths for value
end arrayFindElementsContainingString

private command _arrayFindElementsContainingString pArray, pNeedle, 
pBasePath, @xFoundPaths
    repeat for each key tKey in pArray
       /* Fetch the value of the key */
       local tElement
       put pArray[tKey] into tElement

       /* Create a new path from the base path by appending the current 
key */
       local tPath
       put pBasePath into tPath
       put tKey into tPath[the number of elements in tPath + 1]

       /* What we do depends on the content of the element */
       if tElement is an array then
          /* If the element is an array, then we recurse passing through 
the path to this key
           * as the base path */
          _arrayFindElementsContainingString tElement, pNeedle, tPath, 
xFoundPaths
       else if tElement contains pNeedle then
          /* If the element is not an array, it must be a string-like 
thing so we can check for
           * for containment of pNeedle, and if it does contain the 
needle we append the
           * path to the key to the xPaths sequence */
          put tPath into xFoundPaths[the number of elements in 
xFoundPaths + 1]
       end if
    end repeat
end _arrayFindElementsContainingString

--------

Warmest Regards,

Mark.

-- 
Mark Waddingham ~ mark at livecode.com ~ http://www.livecode.com/
LiveCode: Everyone can create apps