/ 
DBIO-PostgreSQL-0.900000
/ DBIO::PostgreSQL::SQLMaker

NAME

DBIO::PostgreSQL::SQLMaker - PostgreSQL-specific SQL generation for DBIO

VERSION

version 0.900000

DESCRIPTION

DBIO::SQLMaker subclass for PostgreSQL. Extends standard SQL generation with native support for PostgreSQL JSONB operators.

Used automatically by DBIO::PostgreSQL::Storage — the sql_maker_class is set on the storage and this class is instantiated transparently whenever a PostgreSQL connection is opened.

Containment: @> and <@

Tests whether a JSONB value contains (or is contained by) another JSONB value. The RHS hashref or arrayref is JSON-encoded automatically.

$rs->search({ 'me.data' => { '@>' => { status => 'active' } } });
# WHERE "me"."data" @> '{"status":"active"}'::jsonb

$rs->search({ 'me.tags' => { '<@' => ['a', 'b'] } });
# WHERE "me"."tags" <@ '["a","b"]'::jsonb

Key existence: ?, ?|, ?&

Tests whether a JSONB object has a specific key (or any/all keys from a list). These operators are rewritten as jsonb_exists*() functions to avoid conflicts with DBI's ? placeholder syntax.

$rs->search({ 'me.data' => { '?'  => 'email' } });
# WHERE jsonb_exists("me"."data", ?)

$rs->search({ 'me.data' => { '?|' => [qw(email phone)] } });
# WHERE jsonb_exists_any("me"."data", ARRAY[?, ?])

$rs->search({ 'me.data' => { '?&' => [qw(name email)] } });
# WHERE jsonb_exists_all("me"."data", ARRAY[?, ?])

JSONPath: @? and @@ (PostgreSQL 12+)

Evaluates a JSONPath expression against a JSONB value.

$rs->search({ 'me.data' => { '@?' => '$.status == "active"' } });
# WHERE "me"."data" @? '$.status == "active"'::jsonpath

$rs->search({ 'me.data' => { '@@' => '$.score > 10' } });
# WHERE "me"."data" @@ '$.score > 10'::jsonpath

Path extraction

For comparing individual fields within a JSONB value, see DBIO::PostgreSQL::JSONB which provides the jsonb() path expression helper:

use DBIO::PostgreSQL::JSONB qw(jsonb);
$rs->search( jsonb('me.data', 'status')->eq('active') );
# WHERE (me.data->>'status') = ?

METHODS

new

Extends the base constructor to register JSONB special_ops. Called automatically by DBIO::PostgreSQL::Storage — no need to instantiate this class directly.

_where_op_jsonb_contains

Handles the @> (contains) and <@ (contained by) operators.

The RHS may be:

  • hashref or arrayref — JSON-encoded automatically and bound with a ::jsonb cast:

    { 'me.data' => { '@>' => { status => 'active' } } }
# WHERE "me"."data" @> '{"status":"active"}'::jsonb

{ 'me.tags' => { '@>' => ['admin', 'user'] } }
# WHERE "me"."tags" @> '["admin","user"]'::jsonb

{ 'me.data' => { '<@' => { role => 'guest' } } }
# WHERE "me"."data" <@ '{"role":"guest"}'::jsonb

  • plain string — treated as a pre-encoded JSON string, bound as-is:

    { 'me.data' => { '@>' => '{"status":"active"}' } }
# WHERE "me"."data" @> '{"status":"active"}'::jsonb

  • scalar ref — embedded as literal SQL without binding (use for sub-selects or other column references):

    { 'me.data' => { '@>' => \'other_col' } }
# WHERE "me"."data" @> other_col

_where_op_jsonb_path

Handles @? (jsonpath predicate) and @@ (jsonpath match) operators (PostgreSQL 12+). The RHS is bound as a ::jsonpath cast.

{ 'me.data' => { '@?' => '$.status == "active"' } }
# WHERE "me"."data" @? '$.status == "active"'::jsonpath

{ 'me.data' => { '@@' => '$.score > 10' } }
# WHERE "me"."data" @@ '$.score > 10'::jsonpath

_where_op_jsonb_exists

Handles the ? (key exists), ?| (any key exists), and ?& (all keys exist) operators. Because ? would conflict with DBI's placeholder syntax, these operators are rewritten as PostgreSQL functions.

{ 'me.data' => { '?'  => 'email' } }
# WHERE jsonb_exists("me"."data", ?)

{ 'me.data' => { '?|' => [qw(email phone)] } }
# WHERE jsonb_exists_any("me"."data", ARRAY[?, ?])

{ 'me.data' => { '?&' => [qw(name email)] } }
# WHERE jsonb_exists_all("me"."data", ARRAY[?, ?])

A single string is accepted for ?| and ?& as a convenience (treated as a one-element list):

{ 'me.data' => { '?|' => 'email' } }
# WHERE jsonb_exists_any("me"."data", ARRAY[?])

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.