Is there a better way how to search value in postgres jsonb?

[mabar]

I created a function which is supposed to create postgres jsonb "Do any of the strings in the text array exist as top-level keys or array elements?" syntax ?|. It works well, but it feels like a possibly unsafe workaround, because I have to insert the array[] part with %raw and remove " from beginning and end of each passed value and wrap it in ' instead. Is there a better way? Thank you

Generated code:

?| array ['*', 'app', 'app.client', 'app.client.entry']

Complete query:

SELECT "users".*
FROM "ori"."users" AS "users"
         LEFT JOIN "ori"."user_roles" AS "user_roles" ON ("users"."id" = "user_roles"."user_id")
         LEFT JOIN "ori"."roles" AS "roles" ON ("user_roles"."role_id" = "roles"."id")
WHERE "roles"."privileges" ?| array ['*', 'app', 'app.client', 'app.client.entry']
GROUP BY "users"."id";

Function:

use Nextras\Orm\Collection\Functions\BaseCompareFunction;
use Nextras\Orm\Collection\Helpers\DbalExpressionResult;

final class JsonAnyKeyOrValueExistsFunction extends BaseCompareFunction
{

	protected function evaluateInPhp($sourceValue, $targetValue): bool
	{
		// TODO: Implement evaluateInPhp() method.
	}

	protected function evaluateInDb(DbalExpressionResult $expression, $value): DbalExpressionResult
	{
		assert(is_array($value));

		$inlineValue = '';
		$last = array_key_last($value);
		foreach ($value as $key => $item) {
			$item = substr($item, 1, -1);
			$inlineValue .= "'{$item}'";
			if ($key !== $last) {
				$inlineValue .= ', ';
			}
		}
		$inlineValue = "array[{$inlineValue}]";

		return $expression->append('?| %raw', $inlineValue);
	}

}

[mabar]

So far I found value is wrapped in " due to DbalExpressionResult->$valueNormalizer which internally calls wrapper used in property (json) so I probably shouldn't use BaseCompareFunction?

And the array[] syntax is possibly a feature not supported by dbal

[hrach]

This is rather a Dbal question. There are two possible solution:

1. Register own modifier and implement its parsing.

• https://github.com/nextras/dbal/blob/94d2c756d190a18f09da5bf20e6be032ca658841/src/SqlProcessor.php#L78
• you may register own SqlParser implementation in DI - use sqlProcessorFactory config option and pass factory implementation.

2. Manually prepare placeholders for the value:

	protected function evaluateInDb(DbalExpressionResult $expression, $value): DbalExpressionResult
	{
		assert(is_array($value));

		$size = count($value);
		$placeholders = implode(', ', array_fill(0, $size, '%s'));
		$arrayExpression = "array[$placeholders]";
		return $expression->append("?| %arrayExpression", array_values($value));
	}

[hrach]

The thing with quotes - yes, do not inherit from BaseCompareFunction. Directly implement the inferfaces. Should be pretty straightforward.

[mabar]

Thanks! Custom modifiers are really helpful. I just had to modify api a bit to be able compose modifiers from multiple sources and have the Connection available (for Driver-specific escaping)

[hrach]

Thanks a lot - will try enhance the API: nextras/dbal#140

[hrach]

Also direct support for array will be considered: nextras/dbal#141