Platform-Independent JSON Object & Array Aggregation Helpers for Laminas\Db

[eguvenc]

Feature Request

Q	A
New Feature	yes
RFC	yes
BC Break	no

Summary

Provide platform-independent helpers for creating JSON objects, JSON array aggregations, and string case transformations in Laminas\Db.
The feature should support both MySQL and PostgreSQL, including nested JSON structures, e.g., JSON_ARRAYAGG(JSON_OBJECT(...)).

---

Current behavior

Currently, Laminas\Db allows raw SQL expressions via Expression, but there is no built-in support for:

• JSON object creation (JSON_OBJECT / json_build_object)
• JSON array aggregation (JSON_ARRAYAGG / json_agg)
• String case transformations (upper, lower, ucfirst)
• Nested JSON structures

Developers must manually handle platform differences, leading to duplicate code and potential errors.

---

Proposed behavior

Introduce a helper class (e.g., JsonExpressionHelper) that can generate platform-independent SQL expressions:

• JSON Object:

  • MySQL: JSON_OBJECT('key', value, ...)
  • PostgreSQL: json_build_object('key', value, ...)

• JSON Array Aggregation:

  • MySQL: JSON_ARRAYAGG(expression)
  • PostgreSQL: json_agg(expression)

• String Case Transformations:

  • upper(column) → UPPER(column)
  • lower(column) → LOWER(column)
  • ucfirst(column) → MySQL: CONCAT(UPPER(SUBSTRING(...)), LOWER(SUBSTRING(...)))
    PostgreSQL: UPPER(SUBSTRING(...)) || LOWER(SUBSTRING(...))

• Nested JSON Support:

  • Expressions like JSON_ARRAYAGG(JSON_OBJECT(...)) should work seamlessly.

---

Example Usage

Object

$platform = strtolower($adapter->getPlatform()->getName());
$jsonHelper = new \Laminase\Db\Expression\JsonExpression($platform);

$select->columns([
    'id',
    'action' => $jsonHelper->jsonObject([
        'id' => 'p.action',
        'name' => $jsonHelper->ucfirst('p.action')
    ]),
    'method' => $jsonHelper->jsonObject([
        'id' => 'p.method',
        'name' => $jsonHelper->upper('p.method')
    ]),
]);

Array Object

$select->columns([
    'id',
    'actions' => $jsonHelper->jsonArrayAgg(
        $jsonHelper->jsonObject([
            'id'   => 'p.action',
            'name' => $jsonHelper->ucfirst('p.action')
        ])
    ),
    'methods' => $jsonHelper->jsonArrayAgg(
        $jsonHelper->jsonObject([
            'id'   => 'p.method',
            'name' => $jsonHelper->upper('p.method')
        ])
    ),
    'module',
    'name',
    'route',
]);

[froschdesign]

@eguvenc

Caution

This package is considered feature-complete, and is now in security-only maintenance mode, following a decision by the Technical Steering Committee.
If you have a security issue, please follow our security reporting guidelines.
If you wish to take on the role of maintainer, please nominate yourself

See also: https://getlaminas.org/packages-maintenance-status/?status%5B%5D=security-only

---

You should follow: https://github.com/php-db