Searching properties which is array

[wingfranky]

Hi~ I am new to SleekDB. I love SleekDB without separate installation and can store data as JSON files. I am looking for NoSQL alternatives that are similar to SQLite. I can use it to build MVP or cache external API responses.
While I am trying it, it seems that it cannot search document properties which is an array?

For example:

$person = [
"name"=>"Peter Chan",
"variantnames"=>[
"Peter C.",
"P.C."
],
......
];

$article = [
"title"=>"test title,
"keywords"=>[
"machine learning",
"computer science",
.....
],
......
];

In the $criteria section of the document, array properties there are "contains" which can search on "exact" match. However, if I would like to do a partial match with "LIKE" or other operators, e.g. <>, >, <, the PHP will throw warnings:

PHP Warning: preg_match() expects parameter 2 to be string, array given in /vendor/rakibtg/sleekdb/src/Classes/ConditionsHandler.php on line 80
Warning: preg_match() expects parameter 2 to be string, array given in /vendor/rakibtg/sleekdb/src/Classes/ConditionsHandler.php on line 80

I want to check if SleekDB can do this? It would help find a person or an article by variant names or keywords.

Thanks.

[Timu57]

Hi @wingfranky
Welcome to our SleekDB community.

Feel free to join our discord server and ask for help there.

We are planning on providing a build in solution for that use case with the next major release.

Until then the only way to search through an array is by using a custom function.

In your case that would look something like this:

$personStore->findBy([function($person){
  // here you have access to the document and can use the whole power of PHP
  // you need to return a boolean value
  // example:
  foreach($person['variantnames'] as $variantName){
    if($variantName === 'Peter C.'){
      return true;
    }
  }
  return false;
}]);

Please have a look at Example 4 on the $criteria page https://sleekdb.github.io/#/criteria#examples
There you can see a more detailed example.

[wingfranky]

Thanks! I write something like this to solve the problem. It will be good enough for building MVP and prototype. :)

/**
 * Get value from doc with nested path "a.b"
 * @param array $doc
 * @param string $field
 * @return mixed|null
 */
function getNestedValue(array $doc, string $field)
{
    $canReachTarget = true;
    $nestedPathList = explode(".", $field);
    $result = $doc;
    foreach($nestedPathList as $y){
        if(is_array($result) && array_key_exists($y, $result)) {
            $result = $result[$y];
        }else{
            $canReachTarget = false;
        }
    }
    if($canReachTarget) {
        return $result;
    }
    return null;
}

/**
 * Search field with is array type for LIKE search.
 * @param string $pattern
 * @param string $field
 * @return Closure
 */
function searchArrayFieldByLike(string $pattern, string $field): Closure
{
    return static function ($doc) use ($pattern, $field) {
        if(strpos($field, ".") !== false){
            $targetFieldValue = getNestedValue($doc, $field);
        }else{
            $targetFieldValue = $doc[$field] ?? null;
        }
        if($targetFieldValue !== null) {
            $regex = str_replace("%", ".*", $pattern);
            foreach ($targetFieldValue as $item) {
                if (preg_match("/^" . $regex . "$/i", $item) === 1) {
                    return true;
                }
            }
        }
        return false;
    };
}

// simple path with arrays
$pattern = "appl%";
$field = "keywords";
$result = $userStore->findBy([searchArrayFieldByLike($pattern, $field)]);

// nested path with arrays
$pattern = "%sample street%";
$field = "address.street";
$result = $userStore->findBy([searchArrayFieldByLike($pattern, $field)]);

[Timu57]

Your solution looks good 😊

I am closing this issue for now. Feel free to reopen it if needed.