view release on metacpan or  search on metacpan

lib/DBIO/PostgreSQL/SQLMaker.pm  view on Meta::CPAN

      ( ref $val eq 'HASH' || ref $val eq 'ARRAY' )
    ? $JSON->encode($val)
    : $val;    # already a JSON string

  return ( sprintf( '%s %s ?::jsonb', $quoted, $op ), $json );
}


sub _where_op_jsonb_path {
  my ( $self, $col, $op, $val ) = @_;

  my $quoted = $self->_quote($col);
  return ( sprintf( '%s %s ?::jsonpath', $quoted, $op ), $val );
}


sub _where_op_jsonb_exists {
  my ( $self, $col, $op, $val ) = @_;

  my $quoted = $self->_quote($col);

  if ( $op eq '?' ) {
    return ( sprintf( 'jsonb_exists(%s, ?)', $quoted ), $val );
  }

  my @keys = ref $val eq 'ARRAY' ? @$val : ($val);
  my $arr  = 'ARRAY[' . join( ', ', ('?') x scalar @keys ) . ']';

  if ( $op eq '?|' ) {
    return ( sprintf( 'jsonb_exists_any(%s, %s)', $quoted, $arr ), @keys );
  }
  else {    # ?&
    return ( sprintf( 'jsonb_exists_all(%s, %s)', $quoted, $arr ), @keys );
  }
}



1;

__END__

=pod

=encoding UTF-8

=head1 NAME

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

=head1 VERSION

version 0.900000

=head1 DESCRIPTION

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

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

=head2 Containment: C<@>> and C<<@>

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

=head2 Key existence: C<?>, C<?|>, C<?&>

Tests whether a JSONB object has a specific key (or any/all keys from a list).
These operators are rewritten as C<jsonb_exists*()> functions to avoid
conflicts with DBI's C<?> 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[?, ?])

=head2 JSONPath: C<@?> and C<@@> (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

=head2 Path extraction

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

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

=head1 METHODS

=head2 new

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

=head2 _where_op_jsonb_contains

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